1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel VBA Check if cellvalue = formula results

Discussion in 'Business Applications' started by mariaa33, Nov 2, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I want to check column A rows 5-18 against a table in vba and if matches, insert DS into the cell. I have a list of names in A5-A18 and the days (1/1/12-6/3-/12) across the top in row 4. My IF is trying to check if colmun A name is equal to the Vlookup table for the matching date and Name2 (Date Day Name1 Name2). Any advise would be appreciated.

    Code:
     
     fRow = 4
     fCol = 1
      For iCol = 2 To 185
      For iRow = 5 To 18
         If Cells(iRow, fCol).Value = "VLOOKUP(cells(fRow, iCol),R1C201:R399C204,4)" Then
            Cells(iRow, iCol) = "DS"
         End If
      Next iRow
    Next iCol
    
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    Hi, a sample file will make the picture easier to understand, and don't forget to mention the Excel version you're using.
     
  3. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I have excel 2007 and I have hidden the middle columns between U and GS in the attached picture and manually added the DS in the first 3 weeks to show what I am trying to get as an end results. I just want the vba to cycle through the cells and check the right side table under Name2 column and if the date and name match, then put DS into the cell.
     

    Attached Files:

  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,856
    First Name:
    Wayne
    what happens if there are two managers on or are you just looking for name 1

    a vlookup and then use the IF statement to return a DS when tru and using the date if its in the row 4 and formatted just to show the day would work - I have used this for a project gnatt chart layout
    based on start and stop dates

    so something like
    =vlookup( date cell (use $) , $GS$2:$GU$2, 3, false)
    then IF ( vlookup result = A5, "DS", "")

    = IF ( ( vlookup( date cell -use $'s , $GS$2:$GU$2, 3, false) = A5 ) , "DS" , "" )


    a dummy data spreadsheet loaded would help
     
  5. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I want to use vba and I am not actually putting the formula in any cell so the $GS$2:$GU$2 will not work because it says invalid character.
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,856
    First Name:
    Wayne
    ok, not a strength of mine now - last time i used in anger was 1998-2002 - so very rusty

    so need to wait for a vba member to answer

    any reason for using vba ?
     
  7. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Yes, I don't want formulas in cells when there doesn't need to be formula and other data will eventually go into cells that doesn't have DS and I want it all automated.
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    Like etaf and I myself mentioned. please attach a sample file with dummy data and will like to 'attack' the vba issue.
    So, the ball is in your court, when you have the time just attach a small file which some representative dummy data and I'll take a look at it.
    Also include the information of how you want the result to look too.
     
  9. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Here is the sample and I put notes below the table.
     

    Attached Files:

  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    Will take a look tomorrow.
    It's almost midnight here.
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    Okay, here's a sample

    It consists of two parts of code.
    I imagine you will have a sheet for every month
    The main vba module is okay.
    The sheet's vba module must be copied to each sheet that will contain the schedule matrix

    When the sheet is activated it will tun all the cells and fill in de DS value like your formula did.

    This will ony be doen when the sheet is not opened read only.
    You can change the code if needed

    Let me know how it works and if you need help let us know.
     

    Attached Files:

  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1075120

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice