VLOOKUP with VB

  • Thread starter Thread starter ram
  • Start date Start date
R

ram

Hi,

I have the current code and would like help with two queestions:

1) is it possible to return the value and not the formula
2) Is it possible to enter the formula in all the cells without using copy
down


'Copy EIF Numbers to NB sheet
Sheets("NB").Range("P3") = "EIF"
Sheets("NB").Range("O3").Copy
Sheets("NB").Range("P3").PasteSpecial Paste:=xlPasteFormats
Sheets("NB").Range("P4").FormulaR1C1 =
"=VLOOKUP(NB!RC[-15]&RC[-14],EIF!C[-15]:C[-9],7,FALSE)"
LastRow = Sheets("NB").Range("D" & Rows.Count).End(xlUp).Row
Sheets("NB").Range("P4").Copy _
Destination:=Sheets("NB").Range("P5:P" & (LastRow))
 
Thank you just what I needed

p45cal said:
ram;693995 said:
Hi,

I have the current code and would like help with two queestions:

1) is it possible to return the value and not the formula
2) Is it possible to enter the formula in all the cells without using copy
down


'Copy EIF Numbers to NB sheet
Sheets("NB").Range("P3") = "EIF"
Sheets("NB").Range("O3").Copy
Sheets("NB").Range("P3").PasteSpecial Paste:=xlPasteFormats
Sheets("NB").Range("P4").FormulaR1C1 =
"=VLOOKUP(NB!RC[-15]&RC[-14],EIF!C[-15]:C[-9],7,FALSE)"
LastRow = Sheets("NB").Range("D" & Rows.Count).End(xlUp).Row
Sheets("NB").Range("P4").Copy _
Destination:=Sheets("NB").Range("P5:P" & (LastRow))
Click to expand...

VBA Code:
Click to expand...
LastRow = Sheets("NB").Range("D" & Rows.Count).End(xlUp).Row
With Sheets("NB").Range("P4:P" & LastRow)
.FormulaR1C1 = "=VLOOKUP(NB!RC[-15]&RC[-14],EIF!C[-15]:C[-9],7,FALSE)"
.Value = .Value
End With
Click to expand...
 
Back
Top