MrExcel Publishing
Your One Stop for Excel Tips & Solutions

add cell into a range based on criteria


Posted by Artem on April 10, 2001 12:56 PM

hello,

a simple problem. i have a commercial macro which runs only on selected cells, so what i need is a way to select those cells that i need. basically i have a column with criteria, and 2 columns to the left another column in which i need to select cells. So say if the cell in criteria column equals "Excluded" then i don't do anything, otherwise i include the cell 2 cells to the left in my dynamic range. then i run my macro on the active dynamic range. maybe it is possible to just select a cell based on criteria and keep it selected while another one is being selected too (kind of like selecting a range while holding Ctrl key)? the code below doesn't work (because it's wrong). thanks for help!

Sub Update()

Dim DynamicRange As Range

Range("Criteria").Select
For Each cell In Selection
If cell.Value <> "Excluded" Then
DynamicRange = Union(DynamicRange, cell.Offset(-2, 0).Address)
End If
Next cell
Range("DynamicRange").Activate

' Here i run my update macro on dynamic range

End Sub


Posted by anon on April 10, 2001 5:43 PM


Sub Update()

Dim DynamicRange As Range
Dim cell As Range
Dim x As Integer
x = 0
Range("C1:C5").Select
For Each cell In Selection
If cell.Value <> "Excluded" Then
If x = 0 Then
Set DynamicRange = cell.Offset(0, -2)
x = 1
Else
Set DynamicRange = Application.Union(DynamicRange, cell.Offset(0, -2))
End If
End If
Next cell
DynamicRange.Activate

' Here i run my update macro on dynamic range

End Sub


Posted by Artem on April 10, 2001 6:47 PM

THANKS!!!

Works great, thanks!