Run-Time Error 91: Object Variable or With Block Variable Not Set

AMR2050

New Member
Joined
Mar 28, 2023
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
Hello everyone
I am new to excel vba and I have a problem with the code and it gives me error run-time 91
In the code, I want the program to check the cells (A1:A5), if their value is zero or empty, give me this message (no reading) in cell (c1), and if there is a value in the cells, to start checking the cells from bottom to top so that it starts In cell (A5), and if there is a value in it, it subtracts it from the value in cell (b1) and puts the value of the answer in cell (c1), and stops checking the rest of the cells. If there is no value in cell (a5), it moves to cell (a4). And it does the same steps, and so on, until he reaches cell (A1).

Sub TEST()

With Worksheets(2).Range("A1:A5")
Dim x As Integer
Dim sheet(2) As Object




For Each cell In Worksheets(2).Range("A1:A5")
If cell.Value = "0" Or IsEmpty(cell.Value) = True Then
Range("C1").Value = " no reading "

Else

GoTo step2


End If
Next cell


step2:
x = 5

if sheet(2).Cells(x, 1).Value > 0
Range("C1").Value = Range("b1").Value - c

Else

x = x - 1


GoTo step2

End If


End With

End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi there

Untested... try the below

VBA Code:
Sub TEST()
Dim x As Integer
Dim c As Double

For x = 5 To 1 Step -1
    If Range("A" & x).Value <> "" And Range("A" & x).Value <> 0 Then
        c = Range("B1").Value - Range("A" & x).Value
        Range("C1").Value = c
        Exit For
    ElseIf x = 1 Then
        Range("C1").Value = "no reading"
    End If
Next x
End Sub
 
Upvote 1
Solution
Sub TEST() Dim x As Integer Dim c As Double For x = 5 To 1 Step -1 If Range("A" & x).Value <> "" And Range("A" & x).Value <> 0 Then c = Range("B1").Value - Range("A" & x).Value Range("C1").Value = c Exit For ElseIf x = 1 Then Range("C1").Value = "no reading" End If Next x End Sub
Thanks a lot for the help it really works 😀(y)(y)

But may I know why the code that I wrote did not work
 
Upvote 0
Hi

There are several issues with the provided code, including syntax errors, undefined variables, and incorrect use of the "GoTo" statement.

In this corrected version of the code, the loop iterates over cells A5 to A1, checking if each cell contains a non-zero value. If it finds a non-zero value, it subtracts it from the value in cell B1, writes the result to cell C1, and exits the loop. If it reaches the end of the loop without finding a non-zero value, it writes "no reading" to cell C1.

Some important points to note:
  • The variables x and c are declared at the beginning of the code.
  • The loop starts at x=5 and iterates backwards to x=1 using "Step -1".
  • The "Exit For" statement is used to exit the loop once a non-zero value is found.
  • The "ElseIf x=1" statement is used to handle the case where all cells in the range contain zero or are empty.
 
Upvote 1
There are several issues with the provided code, including syntax errors, undefined variables, and incorrect use of the "GoTo" statement.

In this corrected version of the code, the loop iterates over cells A5 to A1, checking if each cell contains a non-zero value. If it finds a non-zero value, it subtracts it from the value in cell B1, writes the result to cell C1, and exits the loop. If it reaches the end of the loop without finding a non-zero value, it writes "no reading" to cell C1.

Some important points to note:
  • The variables x and c are declared at the beginning of the code.
  • The loop starts at x=5 and iterates backwards to x=1 using "Step -1".
  • The "Exit For" statement is used to exit the loop once a non-zero value is found.
  • The "ElseIf x=1" statement is used to handle the case where all cells in the range contain zero or are empty.
Thank you very much for these valuable notes. I am still a beginner in (VBA), and this code was the idea that I want to apply to the basic code that I want to do.
I want to do this code for multi sheets, so that the checking range of cells in the first page and the others pages, until it finds a value and subtracts it from a specific value, and if it does not find, it gives a message that there is no reading

If it doesn't cause trouble to you , I will try to write the basic code at the same idea of your code , and if I face a problems, I will tell you
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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