Object variable or With block variable not set error in vba

Question

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

Presumably it's not finding a value? You are only specifying one parameter which may not help. — SJR
– SJR, Commented Feb 7, 2017 at 11:40
One parameter? Can you pl explaing in laymen term? What are the info that I need to provide to figure out what is wrong here? — stack mark
– stack mark, Commented Feb 7, 2017 at 11:47
Step through your code to check what's going on. If you think a value is there but you are not finding it, it might be due to one of Find's parameters. Look in VBA help or eg msdn.microsoft.com/en-us/library/office/ff839746.aspx (By the way I think you only need CountIf.) — SJR
– SJR, Commented Feb 7, 2017 at 11:50
@stackmark nice idea on how to implement the multiple CountIfs through multiple Worksheets, see my answer below — Shai Rado
– Shai Rado, Commented Feb 7, 2017 at 12:21

Shai Rado · Accepted Answer · 2017-02-07 12:20:46Z

1

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

answered Feb 7, 2017 at 12:20

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

1 Comment

stack mark Over a year ago

thanks for the suggestion @Shai Rado. But even after I add the suggested line I get the same error in the same code...Will this error occur if any of the Col/Worksheet specified doesn't exist?

stack mark · Accepted Answer · 2017-02-08 07:09:33Z

0

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!!

answered Feb 8, 2017 at 7:09

stack mark

1012 silver badges15 bronze badges

Collectives™ on Stack Overflow

Object variable or With block variable not set error in vba

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related