CODEWITHSUNDEEP
vbaexcel
stack mark
stack mark

Reputation: 101

Object variable or With block variable not set error in vba

I have the below code that was running fine. I don't know what changed and suddenly I'm getting the error "Object variable or With block variable not set" in line

Range(Test(0)) = wf.CountIfs(.Rows(1).Find(Test(2), lookat:=xlWhole).EntireColumn, Test(3))

My code:

Sub WBR()

Dim Count1Criteria As Variant
Dim Count3Criteria As Variant
Dim Test As Variant
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction



Filter1InSummary = Array(Array("AH4", "Latency", "Pass/Fail", "Pass"), _
                         Array("AH5", "Latency", "Pass/Fail", "Fail"), _
                         Array("AH44", "TT", "Able to repro", "Not Tested"), _
                         Array("AH47", "TT", "Reason for Reasssignment/Resolving", "Duplicate TT"), _
                         Array("AH51", "TT", "Able to repro", "Yes"), _
                         Array("AH52", "TT", "Able to repro", "No"), _
                         Array("AH61", "Reactive", "Item Type", "Item"), _
                         Array("AH46", "TT", "Reason for Reasssignment/Resolving", "Hardware Unavailable"), _
                         Array("AH41", "TT", "Severity", "2"), _
                         Array("AH62", "Reactive", "Trigger Key Name", "*App Crashes*"), _
                         Array("AH63", "Reactive", "Trigger Key Name", "*Download*"), _
                         Array("AH49", "TT", "Reason for Reasssignment/Resolving", "Insufficient Information"), _
                         Array("AH15", "Latency", "Comments", "*Waived since unable to repro issue*"), _
                         Array("AH6", "Latency", "Comments", "*Waived since unable to repro issue*"), _
                         Array("AH16", "Latency", "Comments", "*Waived due to business reasons*"), _
                         Array("AH18", "Non-Mhowls", "Type of testing", "Full Testing"), _
                         Array("AH19", "Non-Mhowls", "Type of testing", "Upgrade Testing"), _
                         Array("AH21", "DRG", "Failure testing type", "Normal Testing"), _
                         Array("AH22", "DRG", "Failure testing type", "Deep Testing"))




 For Each Test In Filter1InSummary
    With Worksheets(Test(1))
        Range(Test(0)) = wf.CountIfs(.Rows(1).Find(Test(2), lookat:=xlWhole).EntireColumn, Test(3))
    End With
Next

Upvotes: 1

Views: 1000

Answers (2)

stack mark
stack mark

Reputation: 101

I fixed the error. One of the column was not present in the test data that I was testing with!

Once I figured out the missing data and added it, the issue got fixed!!

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

You forgot to full quallify your Range(Test(0)) with the Worksheet(Test(1)).

change your line :

Range(Test(0)) = wf.CountIfs(.Rows(1).Find(Test(2), lookat:=xlWhole).EntireColumn, Test(3))

to:

.Range(Test(0)).Value = wf.CountIfs(.Rows(1).Find(Test(2), lookat:=xlWhole).EntireColumn, Test(3))

Note: I've added the Value as good coding practive, it's not necessary

Upvotes: 1

Related Questions