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)))

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)))