Macro to iterate through rows in a worksheet (problem)

[Mark Hanley]

Hi

I'm trying to create a macro that will do the following:
When the user changes a value or values in column 'B', the macro will
iterate through all cells in columns 'C' to 'AO' but only in the same
row(s) that were changed in column 'B'.
So basically, whatever row or rows got changed, those same rows but
columns C to AO will be altered. The macro is actually just applying
some simple formatting to those cells.
Here's what I have so far and it fails on 'theRow = Target.Rows(i)':
Excerpt
*****************************************************************
Dim theCell As Range
Dim theRow As Range
Dim i As Integer
For i = 1 To Target.Rows.Count
theRow = Target.Rows(i) ' <------------------Throws up
error 'Object variable or With block variable not set'
For Each theCell In Intersect(Range("C1:AO35"), theRow)
If theCell.Value > 0 Then
'Is a multiple of 6 - GOLD
If theCell.Value Mod 6 = 0 Then
Call setGold(theCell)
Else
'Is a multiple of 3 but not a multiple of 6 - SILVER
If (theCell.Value Mod 3) = 0 Then Call setSilver
(theCell)
End If
End If
Next theCell
Next i
***************************************************************************
*
Why can't I just iterate through a Rows collection?
Any help would be appreciated!
Mark

 

[Per Jessen]

Hi Mark

I think this is what you want:

For i = 1 To Target.Rows.Count
theRow = Target.Row
For Each theCell In Intersect(Range("C1:AO35"), Rows(theRow))
If theCell.Value > 0 Then
'Is a multiple of 6 - GOLD
If theCell.Value Mod 6 = 0 Then
Call setGold(theCell)
Else
'Is a multiple of 3 but not a multiple of 6 - SILVER
If (theCell.Value Mod 3) = 0 Then Call setSilver(theCell)
End If
End If
Next theCell
Next i

But assuming you are usint a worksheet_change event, I would do it
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theCell As Range
Dim theRow As Range

Set isect = Intersect(Target, Columns("B"))
If Not isect Is Nothing Then
theRow = Target.Row
For Each theCell In Range("C" & theRow & ":AO" & theRow)
If theCell.Value > 0 Then
If theCell.Value Mod 6 = 0 Then
'Is a multiple of 6 - GOLD
'Call setGold(theCell)
ElseIf (theCell.Value Mod 3) = 0 Then
'Is a multiple of 3 but not a multiple of 6 - SILVER
'Call setSilver(theCell)
End If
End If
Next theCell
End If
End Sub

Regards,
Per

 

[Mike]

Some of your problem is you have 3 If statements and only 2 End if

 

[Bernie Deitrick]

You need to set an object rather than just use =

Change

theRow = Target.Rows(i)
to
Set theRow = Target.Rows(i)

HTH,
Bernie
MS Excel MVP