PDA

View Full Version : Indentify column references for cells that meet criteria and assign them to variable



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

Bob Phillips
07-19-2012, 02:38 PM
Sub AAAAAAtest()
Dim cell As Range
Dim var(1 To 10) As Variant

For Each cell In Range("D8:M8")

If Cells(8, cell.Column).Value < 1.9 Then

var(cell.Column - Range("D8").Column + 1) = cell.Column
Else ' do something else
End If
Next cell

MsgBox Join(var, ",")
End Sub

xlUser
07-20-2012, 05:11 AM
Thanks XLD.

This now shows all effected columns in a message box all at once.

I would however like to take this code further. Using the above code how do I individually reference and do something with each column impacted?

So say the above macro returns columns 6 and 8.

In something like below how do reference var to relate to column 6 versus column 8, i.e how do it tell it to do something with just the first instance or just the last instance etc.

So here I might just want to copy part of column 6 to range Z8

Range(Cells(8, var), Cells(100, var)).Copy 'do something with column6
Range("z8").Select
Selection.Paste

Appologies if I am not explaining myself clearly!

Thanks,

XlUser