When the following statement is executed the error below occurs…
ColLast = .Cells(4, .Columns.Count).End(xlToLeft).Column
Run-time error ‘424’;
Object required
Can anyone helpwith this?
Thanks in Advance
Code...
Sub UpdateData()
Dim ColLast As Long, RowLast As Long
Dim AppendStr As String, Str As String
Dim RowIndex As Long, ColIndex As Long
With ActiveSheet.Name 'Sheet1
ColLast = .Cells(4, .Columns.Count).End(xlToLeft).Column
RowLast = .Cells(.Rows.Count, "D").End(xlUp).Row
Dim StrArray() As String, i As Integer
For RowIndex = 5 To RowLast
AppendStr = ""
i = 0
For ColIndex = 2 To ColLast
If .Cells(4, ColIndex) = "Regulatory" And .Cells(3, ColIndex).Interior.ColorIndex <> 43 Then
Str = .Cells(RowIndex, ColIndex)
If i = 0 Then
AppendStr = Str
ReDim Preserve StrArray(i)
StrArray(UBound(StrArray)) = Str
i = i + 1
ElseIf UBound(Filter(StrArray, Str)) <= -1 Then
AppendStr = AppendStr & ", " & Str
ReDim Preserve StrArray(i)
StrArray(UBound(StrArray)) = Str
i = i + 1
End If
End If
Next ColIndex
RowLast = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPreviou