How to replace cells in column with another value?

MR7005

New Member
Joined
Dec 8, 2005
Messages
7
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Code:
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

New Member
Joined
Dec 8, 2005
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,635
Members
412,334
Latest member
ExcelForLifeDontHate
Top