Hi,I am looking to do a Macro for Excel, where it reduces numbers in rows to a certain number?

Question

If the number is less than a specific number I want it to stay the same.

If the number is higher than a specific number I want it to be lowered to the specific number. Does this make sense? Or can anyone help me to develop a macro to do this?

An IF function is not viable for the amount of data as there is 250+ rows and up to 75 columns of data.

Answer 1

This macro will perform the task requested.  Make sure to change the 'X' to your number BEFORE copying to the clipboard.

Sub Audit_Replace()

Dim theNum

theNum = X

For Each cell In ActiveSheet.UsedRange

cell.Select

If ActiveCell.Value < theNum Then

ActiveCell.Value = ActiveCell.Value

ElseIf ActiveCell.Value >= theNum Then

ActiveCell.Value = theNum

End If

Next

End Sub

ALT + F11

Insert  >  Module

Paste the macro into the Module space to the right.

Close back to Excel

Tools  >  Macro  >  Macros

Highlight the Audit_Replace macro

Click 'Options'

Select a letter to be used as a keyboard shortcut

Close back to Excel.

Press CTRL + your keyboard shortcut letter  to call the macro

Answer 2

I don't have a direct answer to your problem, but post it (free login) to mrexcel.com.  A truly great site.  I've used it for several years and there are some VERY smart people that could problem get you an answer in 15 minutes.

good luck

Answer 3

I didn't like the second person's answer, that would perform the macro on every cell, not just the cells you want the macro performed on.  That could turn out bad.

Here's what I'd recommend:

First, select your cells in Excel.  Then you'll want to go to the Name Box which is normally above cell A1 and type in a name for the range of cells to name that range of cells and hit the enter button.

Let's say you call the name of that range MyRange.  Here's what your macro would look like:

Sub MySpecificNum

Dim x As Range

Dim SpecificNum As Double

Application.ScreenUpdating = False

SpecificNum = 100

For Each x In Range("John")

If IsNumeric(x.Value) = True Then

If x.Value > SpecificNum Then

x.Value = SpecificNum

End If

End If

Next x

Application.ScreenUpdating = True

End Sub

If you wanted, you could use an input box to get the range and also to get the specific number.  That would make the macro a little more flexible.  But the macro I showed you helps you get the idea of what you need to do.  You want to turn the screen updating off while the macro is running so the macro runs faster.  I also limited the macro to just the cells you were concentrating on so it runs faster.  And I also checked to make sure the cell contained a number before evaluating it otherwise you'd get an error or it might evaluate it in a text fashion.