Object variable or With block variable not set Error Receicing

Shazir

Banned - Rules violations
Joined
Jul 28, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
I have been using below code where an error is occurred when it is applied to other sheets.

Error is Object variable or With block variable not set

Can i have solution for this please.

VBA Code:
Sub caluematch()
Dim row1, month3, month2, month1, Rating3, Rating2, Rating1, sku As String
Dim ws As Worksheet
Dim xa, xb, xc As Integer
xa = 1
xc = Worksheets("Main").Cells(1, 5)
Sheets("Main").Range("A:A").Clear
 
For Each ws In Worksheets
     Sheets("Main").Cells(xa, 1) = ws.Name
     xa = xa + 1
Next ws
Sheets("Main").Range("A2:C8").Sort Key1:=Sheets("Main").Range("B2"), Order1:=xlAscending, Header:=xlNo
For xb = 4 To xc

    month3 = Worksheets("Main").Cells(xb, 3)
    month2 = Worksheets("Main").Cells(xb - 1, 3)
    month1 = Worksheets("Main").Cells(xb - 2, 3)
    
    For x = 2 To 800
        If Worksheets(month3).Cells(x, 17) = "D" Then
            sku = Worksheets(month3).Cells(x, 2).Text
            If MatchFound(month2, sku, "D") And MatchFound(month1, sku, "D") Then
                Worksheets(month3).Cells(x, 18) = "Remove"
            Else
            End If
        Else
        End If
    Next x
Next xb
MsgBox ("Worksheets Updated")
End Sub

Function MatchFound(shtname, sku, val) As Boolean
   MatchFound = (Worksheets(shtname).Range("b:b").Find(What:=sku, LookIn:=xlValues).Offset(0, 15) = val)
End Function
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sir that one.

MatchFound = (Worksheets(shtname).Range("b:b").Find(What:=sku, LookIn:=xlValues).Offset(0, 15) = val)

1607949891273.png
 
Upvote 0
You will get an error on that line if the sku that you're searching for doesn't exist.

I suspect that there may be other problems with your code but for now, see if this fixes the first error.
VBA Code:
Function MatchFound(shtname, sku, val) As Boolean
Dim tmprng As Range
    Set tmprng = Worksheets(shtname).Range("b:b").Find(What:=sku, LookIn:=xlValues)
        If Not tmprng Is Nothing Then
            MatchFound =(tmprng.Offset(0, 15) = val)
        Else
            MatchFound = False
        End If
End Function
When using .Find in vba it is wise to set all arguments (options) not just the ones that you think are important. There are no default settings so anything not specified will be set to whatever was used last time (either in vba or by using the the manual search box (crtl f) in excel.

Also, see the section about intx, inty and intz in this link, the first example is the ideal way to declare your variables, the second example is what you have done.

These are just the things that I've noticed at a quick glance, there could be other potential problems that I haven't noticed.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top