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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,144
Members
430,342
Latest member
Sailingexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top