VBA, i want the value, not the formula

[Sir Babydum GBE]

Hi, the following code places a formula in a cell for me which returns the desired result.

Trouble is, I want the code to put the value in there, not the formula


Sub LookupTalkNumber()
Dim TalkNumber$

'get the talk number that was just entered
TalkNumber = Format(ActiveCell.Value, "0")

Cells(53, ActiveCell.Column).Formula = "= vlookup(" & TalkNumber & ", PublicTalkTitles!$A$2:$K$201, 11, FALSE)"

End Sub



How do I do it please?

Many thanks

Sir BD

[Sir Babydum GBE]

So I solved it with

With Cells(53, ActiveCell.Column)
.Value = .Value
End With
And that's fine, but I'd still like to know how to use vlookup to put the result straight in.

Thanks

[p45cal]

not tested:
Cells(53, ActiveCell.Column).value =application.vlookup(TalkNumber, Sheets("PublicTalkTitles").range("A2:K201"), 11, FALSE)

[Sir Babydum GBE]

Thanks for the reply

Tried that but it returns "#N/A"

:(

[p45cal]

Does it work if you:
Dim TalkNumber
TalkNumber = ActiveCell.Value

$ removed from TalkNumber variable name, and no formatting applied to the activecell value (so that it stays as a number (I don't know what's in the first column of that table)).