VLookup to get VALUE rather 1

[wayneryeven]

Hey all
I have a vlookup, which places the result of the lookup into a Cell e.g. B2.

When i run the vlookup in the macro, it places the result in B2 successfully, however if i click on the cell i see that is has the formula =Vlookup..... . How can i change this so that it simply places the lookup VALUE in the cell, rather than the formula?

Many Thanks to all,

 

[VincentCrimmins]

You could try the following:

Sub RunCode()
Dim VariableOne
VariableOne = Excel.WorksheetFunction.VLookup("X", Range("C210", 2, False)
Cells(2,2)= VariableOne 'Sets B2 to the VLookup result
End Sub

Or else you could record a macro to copy the result in B2 and then PasteSpecial the value in B2.

 

[PHV]

When i run the vlookup in the macro
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wayneryeven]

hi PHV
sorry i have some additional code, what i was saying was that the vlookup is being performed in a macro, rather than typed to a cell. The code loops through cells performaing a lookup each time. What i want is for the VALUE to be placed in ActiveCell rather than the formula. Here is lookup code:

ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],Actual,2,FALSE)),0,(VLOOKUP(RC[-2],Actual,2,FALSE)))"

The cell successfully has the value 90, but if i click on this cell i see the formula. I just want 90 to appear...?

Hope this clarifies the matter.

Thanks,

 

[VincentCrimmins]

I'm sure there's a better way, but the copy and pastespecial gets around it:

ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],Actual,2,FALSE)),0,(VLOOKUP(RC[-2],Actual,2,FALSE)))"

ActiveCell.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[PHV]

What about this ?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],Actual,2,FALSE)),0,(VLOOKUP(RC[-2],Actual,2,FALSE)))"
[!]ActiveCell.Value = ActiveCell.Value[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wayneryeven]

Legend PHV!! Thanks.

Would it be possible to put this value into a variable rather than a cell at all?

e.g.
aQuantity=

ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],Actual,2,FALSE)),0,(VLOOKUP(RC[-2],Actual,2,FALSE)))"
ActiveCell.Value = ActiveCell.Value

etc?

 

[PHV]

Like this ?
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],Actual,2,FALSE)),0,(VLOOKUP(RC[-2],Actual,2,FALSE)))"
ActiveCell.Value = ActiveCell.Value
aQuantity = ActiveCell.Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886