• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

Excel VBA run time error

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
           
            .Cells(RowIndex, 1) = AppendStr
        Next RowIndex
       
    End With

End Sub
0
tesla764
Asked:
tesla764
  • 2
2 Solutions
 
Martin LissOlder than dirtCommented:
Try this:

RowLast  = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

...or, change:

With ActiveSheet.Name 'Sheet1

To read:

With ActiveSheet

BFN,

fp.
0
 
tesla764Author Commented:
Both of these solutions worked.
Thanks everybody.
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now