|
VLOOKUP with VB
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)) |
VLOOKUP with VB
ram;693995 Wrote: 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)) 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 -------------------- -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194048 http://www.thecodecage.com/forumz |
VLOOKUP with VB
Thank you just what I needed
"p45cal" wrote: ram;693995 Wrote: 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)) 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 -------------------- -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194048 http://www.thecodecage.com/forumz . |
| All times are GMT +1. The time now is 02:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com