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

[AMR2050]

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

 

[Jimmypop]

Hi there

Untested... try the below

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

 

[AMR2050]

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

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

 

[Jimmypop]

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.

 

[AMR2050]

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