VLOOKUP with VB

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))
 
P

p45cal

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 usin 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:
--------------------


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
 
R

ram

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A simple if <> nn then Msgbox "No Match" 5
Array with many cells from a row but not in order or together 11
Copy columns and paste with blank column between 5
Macro will copy formula instead of value 6
Object variable or with Block variable not set 9
Find Date error message 2
vlookup issue 2
Code copies twice...? 13

Top