CODEWITHSUNDEEP
excelvba
Rosie
Rosie

Reputation: 27

In called subroutines, getting an "object required" error when reading cells

My project includes reading data out of multiple different workbooks in a folder using a Macro in an unused workbook. I have been defining exactly which workbook to pull information from, and that is working fine so far. However, my procedure got to be too large, so I now have a few subroutines at the bottom that I call in the middle of my process. When I call this, I get an "object required" error. Here are the relevant clips:

Sub LoopFiles()
Dim MyFileName, FilenameNew, MyPath As String
Dim wb1 As Workbook

MyPath = "C:\ExcelT2\"
MyFileName = Dir(MyPath & "*.csv")

Do Until MyFileName = ""
    Set wb1 = Workbooks.Open(MyPath & MyFileName)

With wb1

    .Sheets.Add After:=.Worksheets(.Worksheets.Count)
    .Sheets.Add After:=.Worksheets(.Worksheets.Count)

    wb1.Worksheets(1).Select

    For i = 17 To 5000

        ElseIf wb1.Worksheets(1).Cells(i, 3) <> wb1.Worksheets(1).Cells((i - 1), 3) Then
            cn = cn + 1
            prevval = prevval + 1

            Call One_Wep
'......

        End If
    End With
Loop

End Sub

Private Sub One_Wep()
        If wb1.Worksheets(1).Cells(i, 1) = 1 And wb1.Worksheets(1).Cells((i + 3), 1) = 11 And wb1.Worksheets(1).Cells(i, 3) = wb1.Worksheets(1).Cells((i + 3), 3) And wb1.Worksheets(1).Cells((i + 2), 6) <> 57 Then
            OneWep = 1

'......
        End If
End Sub

The error occurs on the If statement shown under One_Wep. I think it might have to do with referencing wb1 in the called function, but after looking around I can't figure out how to solve this. Thanks in advance!

Upvotes: 1

Views: 173

Answers (3)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

Because wb1 has not been declared Globally, the sub can't resolve it.

Move the Dim above all subs and functions in the Module.

Upvotes: 1

Rob Anthony
Rob Anthony

Reputation: 1813

The issue is that you don't define wb1 or i inside the subroutine. The easiest way of solving this would be to pass them as parameters.

Private Sub One_Wep(i as Integer, wb1 as WorkBook)

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

You need to pass the wb1 workbook object.

Change the way you call your Sub from:

Call One_Wep

To:

One_Wep wb1 '<-- this will pass wb1 object to your sub, there's no need to use `Call`

And change the way your Sub is defined from:

Private Sub One_Wep()

To:

Private Sub One_Wep(wb1 As Workbook)

The same applies to your i variable.

Overall, you need to call it with:

One_Wep wb1, i

and change you Sub definition to:

Private Sub One_Wep(wb1 As Workbook, i As Integer)

Upvotes: 1

Related Questions