[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Vlookup not Inserting Correctly

I having problems with this formula.  It does not enter correctly in C3.  I either does not copy down to the next row correctly or it adds B2 reference not B3.  

Sheet1.Range("C3:C" & Lastrow).FormulaR1C1 = "=(VLOOKUP(B3,'Vendor'!$E$2:G9,2,FALSE))"

Sub InsertVS()
    Dim Lastrow As Long

    Application.ScreenUpdating = False

    Lastrow = Sheet27.Cells(Sheet27.Rows.Count, "B").End(xlUp).Row
    Sheet27.Range("C3:C" & Lastrow).FormulaR1C1 = "=(VLOOKUP(B3,'Vendor'!$E$2:G497,2,FALSE))"
    Sheet27.Range("D3:D" & Lastrow).FormulaR1C1 = "=VLOOKUP(B3,'Vendor'!$E$2:H497,3,FALSE)"
        Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    Application.ScreenUpdating = True
End Sub
1 Solution
Sheet1.Range("C3:C" & Lastrow).Formula = "=(VLOOKUP(B3,Vendor!$E$2:$G$9,2,FALSE))"
    Sheet1.Range("D3:D" & Lastrow).Formula = "=VLOOKUP(B3,Vendor!$E$2:$H$9,3,FALSE)"

(Remove "R1C1" and put $ on your entire lookup range. Not sure which one did it, prolly R1C1)
leezacAuthor Commented:
Ok - I changed, but put B3 in C3 and C4.  Need for it to copy down B3, B4 B5 etc.
Rob HensonIT & Database AssistantCommented:
In your script you are effectively telling the formula to be string of text which Excel then recognises as a formula. If you rewrite the formula to R1C1 notation, even as a string of text excel will then evaluate correctly.

Formula for C3 would be:


Formula for D3 would be:


So scripts would be:

Sheet1.Range("C3:C" & Lastrow).Formula ="=VLOOKUP(RC[-1],Vendor!R2C5:R9C7,2,FALSE)"
Sheet1.Range("D3:D" & Lastrow).Formula ="=VLOOKUP(RC[-2],Vendor!R2C5:R9C7,3,FALSE)"

Open in new window

Rob H

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now