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

Question

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.

change Dim QrtID() As Range to Dim QrtID() As VAriant

Scott Craner
– Scott Craner

2021-03-12 23:06:07 +00:00
Commented Mar 12, 2021 at 23:06 — Scott Craner
– Scott Craner, Commented Mar 12, 2021 at 23:06
or Set QrtID(i) = x

Scott Craner
– Scott Craner

2021-03-12 23:06:33 +00:00
Commented Mar 12, 2021 at 23:06 — Scott Craner
– Scott Craner, Commented Mar 12, 2021 at 23:06

VBasic2008 · Accepted Answer · 2021-03-13 05:00:08Z

1

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

answered Mar 13, 2021 at 5:00

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Izatster Over a year ago

Ok thank you so much for this. It works perfectly!

Collectives™ on Stack Overflow

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

1 Answer 1

Check Transactions 2

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Check Transactions 2

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related