Using LOOKUP in VBA

[PittTrack]

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]

With ActiveSheet
.Cells(cell.Row, 9) = .Evaluate("LOOKUP(99^99, --Left(I" & cell.Row & ",ROW(1:99)))")
End With

[PittTrack]

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]

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]

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