CODEWITHSUNDEEP
vbaexcel
R. Arctor
R. Arctor

Reputation: 728

Microsoft Visual Basic, Run-Time error 424: Object Required

so I know this error has been asked about time and time again but I couldn't find one which replicates the issue I am having.

The following is declared in the PERSONAL.XLSB workbook so I can easily use the macro on different projects. The error started popping when I added the second If block in the for loop, note that this macro worked as expected before the aforementioned addition. 

Sub AddNote()
Dim Dict As New Scripting.Dictionary
Dict.Add "101-104", "Includes 101, 102, 103, 104"
Dict.Add "061, 071", "Includes 061, 071"
Dict.Add "076, 077, 081", "Includes 076, 077, 081"
Dict.Add "111, 112, 113, 221, 222", "Includes 111, 112, 113, 221, 222"
Dict.Add "111, 112, 221, 222", "Includes 111, 112, 221, 222"
Dict.Add "111-115, 221, 222", "Includes 111, 112, 113, 114, 115, 221, 222"
Dict.Add "101-104 Early", "Includes 101, 102, 103, 104"
Dict.Add "101-104 Late", "Includes 101, 102, 103, 104"
Dict.Add "101-104 Mid", "Includes 101, 102, 103, 104"
Dict.Add "111-115, 221, 222 Early", "Includes 111, 112, 113, 114, 115, 221, 222"
Dict.Add "111-115, 221, 222 Late", "Includes 111, 112, 113, 114, 115, 221, 222"
Dict.Add "161-164", "Includes 161, 162, 163, 164"
Dict.Add "161-164 Early", "Includes 161, 162, 163, 164"
Dict.Add "161-164 Late", "Includes 161, 162, 163, 164"
Dict.Add "131, 132", "Includes 131, 132"
Dict.Add "062, 064, 066-068", "Includes 062, 064, 066, 067, 068"
Dict.Add "078, 104, 105-107", "Includes 078, 104, 105, 106, 107"
Dict.Add "104, 108, 121", "Includes 104, 108, 121"
Dict.Add "231, 241, 242", "Includes 231, 241, 242"
Dict.Add "072, 074", "Includes 072, 074"
Dict.Add "231, 241, 242 Early", "Includes 231, 241, 242"
Dict.Add "231, 241, 242 Late", "Includes 231, 241, 242"
Dict.Add "114, 115", "Includes 114, 115"

Dim Rng As Range
Dim ws1 As Worksheet
Set ws1 = Sheets("BY HUNT")
Set Rng = ws1.Range("A2:A162")
For Each cell In Rng
    If Dict.Exists(VBA.Trim(cell)) Then
        ws1.Range("AA" & cell.Row).Value = Dict.Item(VBA.Trim(cell.Value))
    End If

    If VBA.Trim(cell.Value) Like "*Early*" Then
        wsl.Range("Z" & cell.Row).Value = "Early"
    ElseIf VBA.Trim(cell.Value) Like "*Late*" Then
        wsl.Range("Z" & cell.Row).Value = "Late"
    ElseIf VBA.Trim(cell.Value) Like "*Mid*" Then
        wsl.Range("Z" & cell.Row).Value = "Mid"
    Else
        wsl.Range("Z" & cell.Row).Value = "All"
    End If

Next cell

End Sub

I'm not overly experienced with VBA but I did read the other questions I found on here related to mine, and none bore fruit. I don't understand why I would be able to mess with the value in the first if statement but in the second I get the error mentioned in the title. Debug points to the line in the else clause, if I remove that one the debug just points to another line in that second if block. Any direction would be much appreciated.


Edit: Moving this macro to the actual workbook did not fix the error

Upvotes: 0

Views: 862

Answers (1)

Tim Williams
Tim Williams

Reputation: 166156

Add Option Explicit to the top of your module and recompile. You have typos in your variable names. ws1 vs wsl

Upvotes: 3

Related Questions