leezac
asked on
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,FAL SE))"
__________________________ __________ __________ __________ _____
Sub InsertVS()
Dim Lastrow As Long
Application.ScreenUpdating = False
Sheet27.Range("C:D").Entir eColumn.In sert
Lastrow = Sheet27.Cells(Sheet27.Rows .Count, "B").End(xlUp).Row
Sheet27.Range("C3").Select
Sheet27.Range("C3:C" & Lastrow).FormulaR1C1 = "=(VLOOKUP(B3,'Vendor'!$E$ 2:G497,2,F ALSE))"
Sheet27.Range("D3:D" & Lastrow).FormulaR1C1 = "=VLOOKUP(B3,'Vendor'!$E$2 :H497,3,FA LSE)"
Sheet27.Columns("C:D").Sel ect
Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.ScreenUpdating = True
End Sub
vlookup.zip
Sheet1.Range("C3:C" & Lastrow).FormulaR1C1 = "=(VLOOKUP(B3,'Vendor'!$E$
__________________________
Sub InsertVS()
Dim Lastrow As Long
Application.ScreenUpdating
Sheet27.Range("C:D").Entir
Lastrow = Sheet27.Cells(Sheet27.Rows
Sheet27.Range("C3").Select
Sheet27.Range("C3:C" & Lastrow).FormulaR1C1 = "=(VLOOKUP(B3,'Vendor'!$E$
Sheet27.Range("D3:D" & Lastrow).FormulaR1C1 = "=VLOOKUP(B3,'Vendor'!$E$2
Sheet27.Columns("C:D").Sel
Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.ScreenUpdating
End Sub
vlookup.zip
ASKER
Ok - I changed, but put B3 in C3 and C4. Need for it to copy down B3, B4 B5 etc.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sheet1.Range("D3:D" & Lastrow).Formula = "=VLOOKUP(B3,Vendor!$E$2:$
(Remove "R1C1" and put $ on your entire lookup range. Not sure which one did it, prolly R1C1)