Insert value from vlookup and not formula with VBA

Hi all, could you please advise me how to change the script below. The
script inserts the Vlookup formulas and i was wondering if it is
possible just to put in the values and not the formula. If this is
possible, could you show me how to change the script.

Sub Lookups()

Dim myLookUpRng As Range
Application.StatusBar = "Your prices are being compared to the
supplier prices"
Range("D4").Select
With Workbooks(SuppFileNameC).Worksheets(SheetName)
Set myLookUpRng = .Range("D:N")
End With
Do Until ActiveCell = ""
ActiveCell.Offset(0, 8).FormulaR1C1 _
= "=VLOOKUP(RC[-8]," _
& myLookUpRng.Address(external:=True,
ReferenceStyle:=xlR1C1) _
& ",9,0)"
ActiveCell.Offset(0, 9).FormulaR1C1 _
= "=VLOOKUP(RC[-9]," _
& myLookUpRng.Address(external:=True,
ReferenceStyle:=xlR1C1) _
& ",10,0)"
ActiveCell.Offset(1, 0).Select
Loop
Range("A4").Select
' InsPriceDiff
End Sub


Thanks in advance.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Instead of

ActiveCell.Offset(0, 8).FormulaR1C1 _
= "=VLOOKUP(RC[-8]," _
& myLookUpRng.Address(external:=True,
ReferenceStyle:=xlR1C1) _
& ",9,0)"

use

With ActiveCell.Offset(0, 8)
.FormulaR1C1 _
= "=VLOOKUP(RC[-8]," _
& myLookUpRng.Address(external:=True,ReferenceStyle:=xlR1C1) _
& ",9,0)"
.Value = .Value
End With

etc.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Les Stout" <[email protected]> wrote in message
news:O%[email protected]...
> Hi all, could you please advise me how to change the script below. The
> script inserts the Vlookup formulas and i was wondering if it is
> possible just to put in the values and not the formula. If this is
> possible, could you show me how to change the script.
>
> Sub Lookups()
>
> Dim myLookUpRng As Range
> Application.StatusBar = "Your prices are being compared to the
> supplier prices"
> Range("D4").Select
> With Workbooks(SuppFileNameC).Worksheets(SheetName)
> Set myLookUpRng = .Range("D:N")
> End With
> Do Until ActiveCell = ""
> ActiveCell.Offset(0, 8).FormulaR1C1 _
> = "=VLOOKUP(RC[-8]," _
> & myLookUpRng.Address(external:=True,
> ReferenceStyle:=xlR1C1) _
> & ",9,0)"
> ActiveCell.Offset(0, 9).FormulaR1C1 _
> = "=VLOOKUP(RC[-9]," _
> & myLookUpRng.Address(external:=True,
> ReferenceStyle:=xlR1C1) _
> & ",10,0)"
> ActiveCell.Offset(1, 0).Select
> Loop
> Range("A4").Select
> ' InsPriceDiff
> End Sub
>
>
> Thanks in advance.
>
> best regards,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***

Thanks Bob, have a great weekend.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Hi Bob, must one loop down the sheet or is it possible to do it another
way ?

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Les Stout" <[email protected]> wrote in message
news:O%[email protected]...
> Hi all, could you please advise me how to change the script below. The
> script inserts the Vlookup formulas and i was wondering if it is
> possible just to put in the values and not the formula. If this is
> possible, could you show me how to change the script.
>
> Sub Lookups()
>
> Dim myLookUpRng As Range
> Application.StatusBar = "Your prices are being compared to the
> supplier prices"
> Range("D4").Select
> With Workbooks(SuppFileNameC).Worksheets(SheetName)
> Set myLookUpRng = .Range("D:N")
> End With
> Do Until ActiveCell = ""
> ActiveCell.Offset(0, 8).FormulaR1C1 _
> = "=VLOOKUP(RC[-8]," _
> & myLookUpRng.Address(external:=True,
> ReferenceStyle:=xlR1C1) _
> & ",9,0)"
> ActiveCell.Offset(0, 9).FormulaR1C1 _
> = "=VLOOKUP(RC[-9]," _
> & myLookUpRng.Address(external:=True,
> ReferenceStyle:=xlR1C1) _
> & ",10,0)"
> ActiveCell.Offset(1, 0).Select
> Loop
> Range("A4").Select
> ' InsPriceDiff
> End Sub
>
>
> Thanks in advance.
>
> best regards,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi Les,

Try this

Sub Lookups()

Dim myLookUpRng As Range
Dim i As Long

Application.StatusBar = "Your prices are being compared to the supplier
prices"
Range("D4").Select
With Worksheets(SheetName)
Set myLookUpRng = .Range("D:N")
End With
For i = 4 To Cells(Rows.Count, "D").End(xlUp).Row
Cells(i, "L").Value = Application.VLookup(Cells(i, "D").Value, _
myLookUpRng, 9, 0)
Cells(i, "L").Value = Cells(i, "L").Value
Cells(i, "M").Value = Application.VLookup(Cells(i, "D").Value, _
myLookUpRng, 10, 0)
Next i
Range("A4").Select
' InsPriceDiff
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Les Stout" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob, must one loop down the sheet or is it possible to do it another
> way ?
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi Bob, sorry about the time delay due to time differences.
Thanks a million works 100%

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***