[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • 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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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