VBA: I get the "object variable or with block variable not set"

user_15139612 0
Viewed 82

So this is my code:

Option Explicit
Sub Check_Transactions2()
Dim QrtID() As Range
Dim Missing() As Range
Dim a, b As Range
Dim w, x, p, r As Variant
Dim i, iRw As Integer

Application.ScreenUpdating = False

r = Worksheets("Transac Check").Range("D1").Value

i = 0
Set b = Worksheets(r).Range("H4:H" & Worksheets(r).Range("I1"))
For Each x In b
    i = i + 1
    ReDim Preserve QrtID(1 To i)
    QrtID(i) = x.Value
Next x

i = 0
For Each p In QrtID
    If Application.WorksheetFunction.CountIf(Worksheets("Transactions").Range("W4:W" & Worksheets("Transactions").Range("X1").Value), p.Value) = 0 Then
        i = i + 1
        ReDim Preserve Missing(1 To i)
        Missing(i) = p.Value
    End If
Next p

For iRw = LBound(Missing) To UBound(Missing)
    Worksheets("Transac Check").Cells(iRw + 1, 15).Value = Missing(iRw)
Next iRw

Application.ScreenUpdating = True

End Sub

When I get to the line

    QrtID(i) = x.Value

I receive the "object variable or with block variable not set" error, and I can't really figure out why. Would appreciate any help.

vba excel
1 Answers

Check Transactions 2

Option Explicit

Sub Check_Transactions2()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim cws As Worksheet: Set cws = wb.Worksheets("Transactions")
    Dim dws As Worksheet: Set dws = wb.Worksheets("Transac Check")
    
    Dim sName As String: sName = dws.Range("D1").Value
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim srg As Range: Set srg = sws.Range("H4:H" & sws.Range("I1").Value)
    Dim Data As Variant: Data = srg.Value
    
    Dim i As Long, k As Long
    For i = 1 To UBound(Data, 1)
        If Application.WorksheetFunction.CountIf( _
                cws.Range("W4:W" & cws.Range("X1").Value), Data(i, 1)) = 0 Then
            k = k + 1
            Data(k, 1) = Data(i, 1)
        End If
    Next i
    
    dws.Range("O2").Resize(k).Value = Data

End Sub
user_98140690
Related