PDA

View Full Version : Using LOOKUP in VBA

PittTrack
02-27-2009, 11:55 AM
I have a bunch of cells with numbers and letters. The numbers are always first. e.g. 10 lbs, 10lb, 10lbs, 10, 10lbs., etc...

Anyway I have found that the following formula works inside of the worksheet:
=LOOKUP(9000000000+307,--LEFT(I1,ROW(\$1:\$99)))

So if any of the examples above were in cell I1 the result of the formula would be 10

I am trying to code this into VBA and I'm getting some resistance when I go to use the \$1:\$99 part.

Below is the code that I'm using. Is anyone able to get it to work correctly?

.Cells(cell.Row, 9) = Application.WorksheetFunction.Lookup(9000000000# + 307, --Left(.Cells(cell.Row, 9),ROW(\$1:\$99)))

Bob Phillips
02-27-2009, 12:34 PM
With ActiveSheet
.Cells(cell.Row, 9) = .Evaluate("LOOKUP(99^99, --Left(I" & cell.Row & ",ROW(1:99)))")
End With

PittTrack
02-27-2009, 12:43 PM
Thanks for the prompt (and correct) reply. I noticed that you used .Evaluate instead of what I had, if you don't mind what is the benefit of using this over the other?

again thanks for the assistance I was playing with that for a while

MaximS
02-27-2009, 02:14 PM
The biggest benefit of using Evaluate is using Excel Fuctions at VBA level and inserting result of calculation to any range/cell. Search on google as there is more benefits of using it.

Bob Phillips
02-27-2009, 03:13 PM
Thanks for the prompt (and correct) reply. I noticed that you used .Evaluate instead of what I had, if you don't mind what is the benefit of using this over the other?

again thanks for the assistance I was playing with that for a while

You need to use .Evaluate to evaluate an array (ROW(1:99)).

You could also use

With ActiveSheet
.Cells(cell.Row, 9) = Application.Lookup(99 ^ 99, .Evaluate("--LEFT(I1,ROW(\$1:\$99))"))
End With