PDA

View Full Version : Solved: Run time error 424 - object required. Why?



Digita
03-15-2009, 11:08 PM
Hi guys,

Need your help on this. This code errors out on the command marked in red.


With ThisWorkbook.Sheets(1)
For Each cel In .Range("A6:A" & .Range("A6").End(xlDown).Row)
If unik = Empty Then
unik = cel
ElseIf InStr(1, unik, cel, 1) = 0 Then ' if unique
unik = unik & "," & cel
cel = Application.Substitute(cel, ",", "")
cel = Application.Substitute(cel, "Group", "")
Debug.Print cel & " - " & cel.Address
End If
Next

End With

Any idea why?

Thanks & regards


kp

Bob Phillips
03-16-2009, 01:03 AM
Presumably, you haven't declared cel and its data type. It first gets sub-typed as a range in the For loop, but when you use Substitute on it, it gets cast to a string, so it doesn't have an address any longer.

Try



Dim cel As Range
Dim celValue As Variant
With ThisWorkbook.Sheets(1)
For Each cel In .Range("A6:A" & .Range("A6").End(xlDown).Row)
If unik = Empty Then
unik = cel
ElseIf InStr(1, unik, cel, 1) = 0 Then ' if unique
unik = unik & "," & cel.Value
celValue = Application.Substitute(cel, ",", "")
celValue = Application.Substitute(cel, "Group", "")
Debug.Print cel & " - " & cel.Address
End If
Next
End With

Digita
03-16-2009, 02:29 AM
Hi Bob,

Thanks for the explanation.

Best regards


kp