How to replace cells in column with another value?

[MR7005]

I am need of some code assistance. I am new to VB and still learning. The code I am looking for help on is as follows:

- Would like to select a column on one sheet that is filled with numeric values and replace any valve that is greater than a specific value with another.
-The number of instances within the column is unknown and the length of the column varies.
-The value to "judge" by is located on another sheet in the same workbook. It is a single cell entry. This "judge" value is also the same value to replace the cells in the column with.

In other words, if any cell in a column is greater than x, then replace it with value x. If it is less than x, do nothing. X is a user enter value on another sheet within the same workbook.

Any help would be appreciated. Remember I am a newbie to this.

MR

 

[tmcfadden]

Sub test()
Dim Judge As Integer
Dim rng As Range

Judge = Sheet1.Range("F4").Value
Set rng = Sheet2.Range("C5:C19")
For Each cell In rng
    If cell.Value > Judge Then
        cell.Value = Judge
    End If
Next cell

End Sub

The range in this example is hard coded. Would hardcoding work for you? If not there are various ways of getting to the data you want to adjust.

It's also hard to say if this example will fit because you haven't said what type of data you are working with (I've assumed INTEGER here).

This might be enough to get you started anyway.

-Tim

 

[MR7005]

The numbers are integers.

For the range to be searched/replaced is column L and in this case begins at L2. From there the number of rows in the column may vary. One time it might be 4000 rows, another 6000, etc. How would I write the code to search from L2 to the end of all the values in the row?

Thanks in advance.

MR