This macro is driving me crazy - Please help

[tmpollard]

I've got a macro that delete values in a dataset that are higher than a value in a specific cell. This works fine

I then copied the macro, pasted it into a new macro template to reverse the sign so that it delete all values lower than a value in a specific cell and it just will not work. This is driving me crazy, can someone please help. Thanks

Sub MaxValue()
'
' MaxValue Macro
' This macro deletes all values greater than the value in Cell M1.
'

'
Dim cell As Range
For Each cell In Range("D11:I1000")
If cell.Value > ActiveSheet.Range("M1") Then
cell.ClearContents
End If
Next cell
End Sub




Sub MinValue()
'
' MinValue Macro
'This macro deletes all values lower than the value in Cell M2.
'

'
Dim cell As Range
For Each cell In Range("D11:I1000")
If cell.Value < ActiveSheet.Range("M2") Then
cell.ClearContents
End If
Next cell
End Sub

 

[kpark91]

Hi, I don't see a particular reason why the code wouldn't work except for "ActiveSheet"

Change ActiveSheet to something like Worksheets("Sheet1") where Sheet1 would be the name of the worksheet you want to look into.
And for more explicit expression of comparing to a value, use Range.Value instead of just Range
Like this

If cell.Value < Worksheets("Sheet1").Range("M2").Value Then

 

[tmpollard]

Hi, I don't see a particular reason why the code wouldn't work except for "ActiveSheet"

Change ActiveSheet to something like Worksheets("Sheet1") where Sheet1 would be the name of the worksheet you want to look into.
And for more explicit expression of comparing to a value, use Range.Value instead of just Range
Like this

If cell.Value < Worksheets("Sheet1").Range("M2").Value Then

Thank you. I saved the macros in my Personal.xls so that they can be used on various workbooks. The tab names at the bottom vary depending on which spreadsheet I open so, I can't really put the worksheet part in there.

 

[rob.barnes01]

Have you verified that the number in cell M2 is actually formatted as a number and not text?

 

[tmpollard]

Went ahead and edited the macro to do as kpark91 suggested in his initial comments with putting the sheet tab name in the macro, it still didn't work.

Also, verified that the cell M2 is formatted for a number, still no luck.

This is puzzling, last thing I tried was using the exact same cell (M1) which is the one that works, then changed the sign in the macro, still didn't work.

 

[mcorriga]

How about your data in D11:I1000? If that data is being read as text rather than a number it would be deleted by the greater than comparison but ignored by the less than.