VLOOKUP with VB

Prev: String manipulation in 2007
Next: Time Spent

From: ram on 8 Apr 2010 12:31

---

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

From: p45cal on 8 Apr 2010 12:51

---

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/showthread.php?t=194048

http://www.thecodecage.com/forumz

From: ram on 8 Apr 2010 13:41

---

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/showthread.php?t=194048
>
> http://www.thecodecage.com/forumz
>
> .
>

 | 
Pages: 1
Prev: String manipulation in 2007
Next: Time Spent