Getting Address of Current Value Throws 424 Error

[soccerkingpilot]

Hi Guys,
I'm having a really weird problem right now. I just used a simple for each operation with an if statement wrapped inside and I want to get the corresponding row number for the value that met my condition.

Set MnS = Workbooks("Metrics and Status.xls")
FAlog = Sheets("Critical").Cells(2, 2).Value
Set FAlogS = Workbooks(FAlog).Sheets("Cases")
lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
cllRng = FAlogS.Range("C4", "C" & lastrow)
For Each cll In cllRng
If cll > MnS.Sheets("Critical").Cells(2, 3) Then
    firstrow = cll.Row 'This is the line throwing the "Object Required" Error
    Exit For
End If

This one's got me scratching my head, and I have a feeling it's something very simple. Any ideas?

Thanks,
Austin

 

[texasalynn]

I think this

cllRng = FAlogS.Range("C4", "C" & lastrow)
should be
cllRng = FAlogS.Range("C4:C" & LastRow)

 

[Norie]

Austin

How have you declared LastRow?

 

[shg]

Object variables require Set:

[COLOR=#ff0000]Set [/COLOR]cllRng = FAlogS.Range("C4", "C" & lastrow)

 

[soccerkingpilot]

texasalynn, I think Range works both ways...you can write it as Range("A1:B1") or Range(fromhere,tohere)

shg's note fixed the problem. Thanks, didn't recognize Range as an object variable.

-Austin

 

[shg]

You would if you declared it