Error when using IsNumeric

[RockyStrong]

Hello,

I wrote this code to find values greater then 150000 and replace it with the value in the previous cell in column 2 staring at row 2 till the last cell that contains a value.

I used the IsNumeric command to look in each cell. I ran the code and it changed all the numbers that were above 150000. Then before it finished the error came up. It is a Aplication-Defiend or Object-Defiend error. Does any one have any ideas why this is happening?

Also is there any way that I can use the IsNumeric command in the While condition line?

Please Login or Register  to view this content.

Thank you for your help.

[Ron Coderre]

Perhaps something like this:

Please Login or Register  to view this content.

Is that something you can work with?

Note: There was some ambiguity in your description. I assumed that you
intend to start at cell B2 and work DOWN through the column.

[shg]

Here's a variation:

Please Login or Register  to view this content.

The reason you get an error is that an empty cell returns True for IsNumeric, so you run past the end of the sheet.

[RockyStrong]

Thanks for the replys,

I do have a question though,
clearly I'm new at this and was wondering about the "with"? I kind of get the idea of what it is doing but would like to read in to it more. I have been looking for a site with all the syntax on it but am having trouble finding one. I checked the msdn site but I could quite seem to find the syntax. I'm guessing it is there and I'm just not naviagting the right way. Do you guys have any suggestions?

[royUK]

It's really a kind of short hand, if you use With you don't need to specify it again, So in shgs code see the added comments

Please Login or Register  to view this content.

Basically,If you are using several statement that apply to the same object, use a WITH statement, rather than fully qualifying the object each time.

[RockyStrong]

Thanks for your help guys, I have it working and understand it. I have another question abouut but I belive it would be benifical in a new Thread.

Thanks again, you guys are supper fast.