Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert value from vlookup and not formula with VBA
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" wrote in message ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert value from vlookup and not formula with VBA
Thanks Bob, have a great weekend.
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert value from vlookup and not formula with VBA
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert value from vlookup and not formula with VBA
-- HTH RP (remove nothere from the email address if mailing direct) "Les Stout" wrote in message ... 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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert value from vlookup and not formula with VBA
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" wrote in message ... 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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert value from vlookup and not formula with VBA
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a dynamic reference of a range inside VLOOKUP? | Excel Worksheet Functions | |||
Vlookup to insert num in mult. columns | Excel Discussion (Misc queries) | |||
VLOOKUP insert rows | Excel Worksheet Functions | |||
how do I insert picture into cell so vlookup can return picture? | Excel Worksheet Functions | |||
VBA insert messing up Vlookup and Match | Excel Programming |