xlUser
07-19-2012, 02:29 PM
Hi,
I need to identify all instances of cells/columns in a given range that meet specific criteria. To do this I have created the macro below but this only seems tp shows the first cell in the range that meets the criteria.
Sub AAAAAAtest()
For Each cell In Range("d8:M8")
If Cells(8, cell).Value < 1.9 Then
MsgBox Cells(8, cell).Column
else ' do something else
End If
Next cell
End Sub
Is there any way to make this work for all cells? So for example, I have cell E8 with 1.2, it displays column 5 in the msg box. But cell F8 also has a value less than 1.9 but the next msg box does not display 6, just 5.
Also I would like to store the columns references to those cells as variables so I can perform action on them later. So instead a msgbox, I need to create a new variable for every instance of values less than 1.9.
Var1 = Cells(8, cell).Column 'first instance of e8
Var2 = Cells(8, cell).Column ' 2nd instance of f8
So that later I could do something like: Range(cells(8,var1), cells(100, var1)).clear
If there are no cells in the range with values less than 1.9 than I don't want any variables to be created. I just want to run the else part of the code.
Greatful for any suggestions/help.
Many Thanks
XlUser
I need to identify all instances of cells/columns in a given range that meet specific criteria. To do this I have created the macro below but this only seems tp shows the first cell in the range that meets the criteria.
Sub AAAAAAtest()
For Each cell In Range("d8:M8")
If Cells(8, cell).Value < 1.9 Then
MsgBox Cells(8, cell).Column
else ' do something else
End If
Next cell
End Sub
Is there any way to make this work for all cells? So for example, I have cell E8 with 1.2, it displays column 5 in the msg box. But cell F8 also has a value less than 1.9 but the next msg box does not display 6, just 5.
Also I would like to store the columns references to those cells as variables so I can perform action on them later. So instead a msgbox, I need to create a new variable for every instance of values less than 1.9.
Var1 = Cells(8, cell).Column 'first instance of e8
Var2 = Cells(8, cell).Column ' 2nd instance of f8
So that later I could do something like: Range(cells(8,var1), cells(100, var1)).clear
If there are no cells in the range with values less than 1.9 than I don't want any variables to be created. I just want to run the else part of the code.
Greatful for any suggestions/help.
Many Thanks
XlUser