Forum Discussion

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jun 22, 2018

Hey Mark-

 

Nice to see you on the forum again.  You can use VLookup in VBA code like the following...

 

Sub VlookupSyntax()

        MyVariable = Application.WorksheetFunction.VLookup(Range("A2"), Range("$A$2:$J$1000"), 3, False)
        
        'or WorksheetFunction isn't necessary if you know how to use the function
        
        MyVariable = Application.VLookup(Range("A2"), Range("$A$2:$J$1000"), 3, False)
        
End Sub
mark ainscough's avatar
mark ainscough
Brass Contributor
Jun 24, 2018

Thanks Matt

 

Also struggling with the formula below which refuses to see numbers in my drop down list cell Q19 if I use text ie "TEN" "TWENTY" it works fine but using numbers "10" "20" the formula does not seem to see them and does nothing. Is there a quick fix? perhaps a formatting issue?

 

=(IF(Estimate!Q19="10",A2,IF(Estimate!Q19="20",B2,IF(Estimate!Q19="25",C2,IF(Estimate!Q19="23",D2,IF(Estimate!Q19="30",E2,IF(Estimate!Q19="50",F2,IF(Estimate!Q19="60",G2,))))))))

 

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 24, 2018
    Take the quotes out. The formula is seeing them as text rather than numbers.
    • Mackenzie1220's avatar
      Mackenzie1220
      Copper Contributor
      Oct 27, 2020

      Matt Mickle Hello - i saw this post as part of a google search. I am trying to create a code that looks up an identification id number on one worksheet, and finds the corresponding data to it on another sheet. I will end up with columns of data corresponding to different entry times in my data sheet. I have the movement flow of the formula down, but when i got to run the code consecutively, it seems that my vlookup formula continues to move right in the spreadsheet, instead of holding constant at the identification id cell. I am using the formula below. do you know how to hold the cell constant, so that it will always look up the same ID number? The part i am having issues with is the RC[-4], i would need it to increase every time (-4,-5,-6,etc...) if i leave it in this format...

       

      ActiveCell.Formula = "=VLOOKUP(RC[-4],'Data Import'!R3C5:R2226C6,2,FALSE)"

       

Resources