Help needed with FOR EACH CELL IN RANGE macro

[jjsauer]

Hello,

I was trying to use the reference point (moving address) of the cells that get cycled in a FOR EACH CELL IN RANGE macro. MY issue is I cannot figure out how to return the info from the cell offset from For List cell...See in red. I get a "Type Mismatch" error...


Sub DCL()

Dim rCell As Range
Dim Range1a As Range
Dim Range1b As Range
Set Range1a = Range("Start")
Set Range1b = Range("End")

Windows("FY12-Q3 Value Tracker Key Measure Summary Tables FINAL.xlsm").Activate
Sheets("Division Category Level").Select
For Each rCell In Range(Range1a, Range1b)
If rCell Is Nothing Then
Else
With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("TOTAL RESPONDENTS").Columns(1)
Set c = .Find("[" & rCell & "B" & "]", LookIn:=xlValues)
Set c = .Find(rCell.Offset(0, 1).Value, After:=c)
Set c = .Find("Bottom 2 (NET)", After:=c)
End With
rCell.Offset(0, 14).Value = c.Offset(, 14).Value
rCell.Offset(0, 26).Value = c.Offset(, 3).Value
rCell.Offset(0, 27).Value = c.Offset(, 12).Value
rCell.Offset(0, 28).Value = c.Offset(, 15).Value
With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("TOTAL RESPONDENTS").Columns(1)
Set c = .Find("[" & rCell & "C" & "]", LookIn:=xlValues)
Set c = .Find(rCell.Offset(0, 1).Value, After:=c)
Set c = .Find("Top 2 (NET)", After:=c)
End With
rCell.Offset(0, 20).Value = c.Offset(, 14).Value
rCell.Offset(0, 29).Value = c.Offset(, 3).Value
rCell.Offset(0, 30).Value = c.Offset(, 12).Value
rCell.Offset(0, 31).Value = c.Offset(, 15).Value
End If
Next
End Sub



Thanks in advance

 

[jjsauer]

bump