how to return value not formula

[Kade]

Hi all

I would like this code to return the value into the d7, not the formula

Range("d7") = "=(VLookup(0, myrange, 3, False))"


currently I am add the following but it is cumbersome

Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

tia

 

[Expel]

Try

Range("d7").Formula = "=(VLookup(0, myrange, 3, False))"

 

[Kade]

Hi expel

no when I try it
Range("d7").Formula = "=(VLookup(0, myrange, 3, False))"
I still get the formula in the cell d7 not the value from the table

I originally thought range("d7").value but this also only returns formula.

Is it possible I have made some setting wrong?

tia

 

[Expel]

Sorry, I couldn't test your formula, as i didn't have that data. I tested it substituting NOW(), and it worked fine.

It is possible to alter the sheet to display formulae in the cells instead of the values.

Try Press CTRL + ` (grave accent), to toggle this setting, or go to Tools | Options | View tab, and see if formulas is checked in the Window Options at the bottom.

Hope this helps

 

[Bariloche]

Kade,

See if this code helps your understanding:<pre>
Sub UseVLookup()

Set rngVL = Workbooks("Book1.xls").Worksheets("Sheet1").Range("A:B")

For i = 2 To 10
LUvalue = Cells(i, 1).Value
Cells(i, 2).Value = Application.WorksheetFunction.VLookup(LUvalue, rngVL, 2, False)
Next i


End Sub</pre>

Make changes as appropriate to accommodate your situation.

enjoy
This message was edited by Bariloche on 2002-12-12 08:48

 

[bosco_yip]

Yes, follow Bariloche's advice, modify your code to :
Range("D7").Value = Application.WorksheetFunction.VLookup(0, myrange, 3, False)

Merry Christmas

 

[bosco_yip]

Yes, follow Bariloche's advice, modify your code to :
Range("D7").Value = Application.WorksheetFunction.VLookup(0, myrange, 3, False)

Merry Christmas

 

[Kade]

Thank you all, another note in for the diary

kd