Macro to replace a value greater than 9.

[James8761]

Hi All,

I have a lot of data in excel. One of the columns is a finishing position of a runner. I am happy if this is a number 1 to 9, then this can stay at this number. However, the number could go up to 250. If a number is between 10 and 250 is there a Macro I could build/run that if any number in Column B is greater than 9 then replace it with a zero?

Thanks for any help
James

 

[Fluff]

Are the numbers in Col B, hard values, or the result of a formula?

 

[James8761]

Hi, hard values

 

[Fluff]

In that case, how about

Sub james8761()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@>9,0,if(@="""","""",@))", "@", .Address))
   End With
End Sub

 

[James8761]

Excellent, thanks so much for that, Fluff. Works straight away!

 

[Fluff]

You're welcome & thanks for the feedback