Pulling data from a cell in Excel 1

[alforjj]

I need to pull data from a cell with VBA. The problem is that the cell is doing a VLOOKUP. So when the code runs, it gets a Type Mismatch error.

How do I pull the data that is displayed in the cell from the VLOOKUP as opposed to the actual VLOOKUP?

Thanks!

 

[EBGreen]

This command will paste just the values into a cell then you can get them and clear out the cell:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

THere is probably a better way, but this should work.

 

[EBGreen]

Sorry, this looks better:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks :=False, Transpose:=False

 

[alforjj]

Thanks, TomT.

I am, however, confused about how to impliment this code. Is this a VBA scipt? I couldn't find anything about it in the Help feature or in the Object Browser.

Thanks again

 

[EBGreen]

It is VBA. For an example of how to implement it, start the macro recorder. Copy and Paste Special(Value Only) the cell(s) that you are interested in. Stop the macro recorder and edit the macro.

 

[PHV]

I couldn't find anything about it in the Help feature or in the Object Browser.

Really? Even PasteSpecial in the Object Browser and the F1 key on the PasteSpecial function, member of Excel.Range?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[alforjj]

I see now. Wasn't exactly how I planned on doing it, but it works great!!

Thanks!

 

[EBGreen]

Like I said, its probably not the most elegant solution, but ugly and working is better than pretty and not IMO.

 

[PHV]

So when the code runs, it gets a Type Mismatch error.
Which code ?
Have you tried to play with the .Text property of the cell ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884