Batch update cell values

[Moshe Rosenberg]

I need to batch update all cell values in a sheet over the value of 200 to
become 200.

How can I accomplish this?

 

[Max]

Use a helper sheet ..

Assuming source data in Sheet1, within A120 (say)

In Sheet2,

Put in A1:
=IF(Sheet1!A1="","",IF(Sheet1!A1>200,200,Sheet1!A1))
Copy A1 across to D1, fill down to D20
Then copy A120, & paste and overwrite A120 in Sheet1

 

[Moshe Rosenberg]

I am not sure what to do with Sheet2. When I put that into the A1 cell, then
what? What actually updates the data?

I would be grateful if you can clarify.

Thanks!

 

[Max]

As stated in my response, I assumed your source data was in Sheet1, within
A120. Sheet2 is just a helper sheet, which is used to examine Sheet1's
source data and change the values as required, using a corresponding range
filled with the formulas as given. Hope that clarifies ..

 

[Moshe Rosenberg]

Thanks, that helped!

As stated in my response, I assumed your source data was in Sheet1, within
A120. Sheet2 is just a helper sheet, which is used to examine Sheet1's
source data and change the values as required, using a corresponding range
filled with the formulas as given. Hope that clarifies ..

 

[Max]

Delighted to hear that, Moshe.

 

[Max]

oops, " .. paste .. " in line:

Then copy A120, & paste and overwrite A120 in Sheet1

should read: " .. paste special as values ... "

 

[Gord Dibben]

Sub Make_200()
Dim cell As Range
For Each cell In Selection
If cell.Value > 200 Then
cell.Value = 200
End If
Next
End Sub


Gord Dibben MS Excel MVP