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