I am getting "Run-time error '424: Object required" error

Question

I have made a macros having multiple subs. In one of the subs I am getting the give error. Before this sub, the code is running fine but in this sub as the active sheet switches to Sheet 2, I am getting the error in the specified line.

Sub Matchr()
counter = 0
Dim k As Integer
Sheets("Sheet2").Select

k = Sheet2.UsedRange.Rows.Count + 5 '<-- This line is throwing the error.

Set S1 = Worksheets("Sheet1")
Set S2 = Worksheets("Sheet2")

For i = 7 To S2.UsedRange.Rows.Count
    For J = 7 To S1.UsedRange.Rows.Count
        If Sheet2.Cells(i, "J") = Sheet1.Cells(J, "J") Then
            counter = 1
        End If
    Next J

    If counter = 0 Then
        Sheet2.Cells(k, "I") = Sheet2.Cells(i, "I")
        Sheet2.Cells(k, "J") = Sheet2.Cells(i, "J")
        Sheet2.Cells(k, "K") = Sheet2.Cells(i, "K")
        k = k + 1
    End If
    counter = 0
Next i

Range("I45:I58").Select
    Selection.NumberFormat = "[$-409]d-mmm-yy;@"
    Range("J63").Select

End Sub

Note that the given code is working fine when I am running it making a button for the macro. I am getting the error while running in Personal XLSB file.

You [do not need to select](http://stackoverflow.com/q/10714251/11683) anything. This alone might fix it. — GSerg, Oct 24 '16 at 15:28
I suspect that it's because you use the code names (i.e. `Sheet1` and `Sheet2`) instead of your variables (`S1` and `S2`). Why are you mixing them? — Comintern, Oct 24 '16 at 15:28
If the "highlighted line" is `k = Sheet2.UsedRange.Rows.Count = 5`, that is because there is no such thing as `Sheet2`. Your code will throw errors later on as well. Quick fix: put that line below where you set `S1` and `S2`, and replace `Sheet2` with `S2` — PartyHatPanda, Oct 24 '16 at 15:31
You don't call it correctly. You need `k = Sheets("Sheet2").UsedRange.Rows.Count + 5` . `Sheet2` is undeclared as I see it, and that's not the same as `Sheets("Sheet2")`. Perhaps you mean to add a `Dim Sheet2 as Worksheet`, `Set Sheet2 = Sheets("Sheet2")`. Although I discourage this naming convention, and I'd use say `Sht2` — BruceWayne, Oct 24 '16 at 15:33
Partial duplicate of BruceWayne's comment which was posted while I was typing this. As you discovered, trying to make text bold using ** does not work within code. Sheet2 and Worksheets("Sheet2") may start as the same worksheet but once you start renaming sheets, these two methods of identifying a sheet drift apart. "Object required" means something in that statement is undefined. Do you have a Sheet2? Type `? Sheet2.Name` into your Immediate Window. Is there a used range within this sheet? Try `Sheet2.UsedRange.Address`? — Tony Dallimore, Oct 24 '16 at 15:44

Chrismas007 · Answer 1 · 2016-10-24T15:47:39.533

3

Lots of cleanup needed:

Sub Matchr()

  Dim k As Integer, S1 as Worksheet, S2 as Worksheet
  Set S1 = Worksheets("Sheet1")
  Set S2 = Worksheets("Sheet2")

      k = S2.UsedRange.Rows.Count + 5

  For i = 7 To S2.UsedRange.Rows.Count
      For J = 7 To S1.UsedRange.Rows.Count
          If S2.Cells(i, "J") = S1.Cells(J, "J") Then
              'Do Nothing
          Else
              S2.Cells(k, "I").Resize(1, 3) = S2.Cells(i, "I").Resize(1, 3)
              k = k + 1                  
          End If
      Next J
  Next i

  Range("I45:I58").NumberFormat = "[$-409]d-mmm-yy;@" 'This range needs a S1. or S2. before it to define the correct Sheet!
End Sub

edited Oct 24 '16 at 15:47

answered Oct 24 '16 at 15:38

Chrismas007

6,085
4
24
47

@PranavM I just made an edit you didn't see. Please use the revised code. – Chrismas007 Oct 24 '16 at 15:44
You still have two `S2` references, not one `S1` and one `S2`. – Oct 24 '16 at 15:49
1

Fair enough (although I don't think it is correct). – Oct 24 '16 at 15:51

I am getting "Run-time error '424: Object required" error

1 Answers1