Macro that won't run Trying to use DoWhile Loop and Offset to compare cells

[DavidSchilp]

I'm trying to write a macro that uses a condition to check cells and then go down one or two cells and then check again. It will keep doing this until an end condition is met.
I tried uploading the add-in but Excel tells me I can't because I'm using Protected Viewing. That may be because this version is through a university.
I think I'm close. Putting in the EndIf let it run until it tells me that It doesn't see a Loop Command. It's there, but somethings off.
Any help appreciated. I've used Excel since it came out in 1986. But don't do macros much. I'm sure it's not as pretty as it could be.
I'm not sure at what stage the Dim statements should be.

Sub Checker()

       If Range("N7").Value < 0 And Range("H7").Value > 0 Then                                      'Initial results that need to be met to bother running this
            Dim x As Integer: x = 2
            Dim e As Integer: e = 0
                Do While x < 35 And Range("N7").Offset(x).Value < 0
                      If Range("H7").Offset(x).Value < 0 Then
                            x = x + 2                                                                                                 'skip next line down in sheet
                      ElseIf Range("K7").Value < Range("K7").Offset(x).Value And Range("L7").Value > Range("L7").Offset(x).Value Then
                            Range("P7").Value = "Buy" And Range("J7").Offset(x).Value = Range("Q7").Value _
                            And e = 1                                                                                              'post results in cells
                      ElseIf x > 35 Or Range("N7").Offset(x).Value > 0 Then
                            e = 1                                                                                                     'Out of range so stop
                      ElseIf e = 1 Then Exit Sub                                                                           'Done with this process
                      Else: x = x + 1                                                                                             'Go to next line down
                      End If
                Loop
        Else: Exit Sub
       
End Sub

 

[Micron]

Step though your code (F8) and check variable and reference values as you go. Note that a code line must be executed/processed in order to have these values applied. You can also use the immediate window to inquire about the values. If you type ?x and hit enter, the value of x should appear in the next line. If you're going to dabble in code, you really should research how to trouble shoot it. As for declarations, I prefer to see them all in the same block at the top as I hate looking for them when trouble shooting. Nice that you use code tags, but your indentation is a bit much. I can't see all from left to right without scrolling.

 

[Micron]

it tells me that It doesn't see a Loop Command.

Will happen when you don't terminate an If block (or similar blocks such as With). You have an un-terminated If block. Note the change I made at the end.

Sub Checker()
Dim x As Integer: x = 2
Dim e As Integer: e = 0

If Range("N7").Value < 0 And Range("H7").Value > 0 Then 'Initial results that need to be met to bother running this
    Do While x < 35 And Range("N7").Offset(x).Value < 0
        If Range("H7").Offset(x).Value < 0 Then
            x = x + 2  'skip next line down in sheet
        ElseIf Range("K7").Value < Range("K7").Offset(x).Value And Range("L7").Value > Range("L7").Offset(x).Value Then
            Range("P7").Value = "Buy" And Range("J7").Offset(x).Value = Range("Q7").Value _
            And e = 1  'post results in cells
        ElseIf x > 35 Or Range("N7").Offset(x).Value > 0 Then
            e = 1      'Out of range so stop
        ElseIf e = 1 Then Exit Sub 'Done with this process
        Else: x = x + 1 'Go to next line down
        End If
    Loop
End If
       
End Sub

 

[DavidSchilp]

Will happen when you don't terminate an If block (or similar blocks such as With). You have an un-terminated If block. Note the change I made at the end.

Sub Checker()
Dim x As Integer: x = 2
Dim e As Integer: e = 0

If Range("N7").Value < 0 And Range("H7").Value > 0 Then 'Initial results that need to be met to bother running this
    Do While x < 35 And Range("N7").Offset(x).Value < 0
        If Range("H7").Offset(x).Value < 0 Then
            x = x + 2  'skip next line down in sheet
        ElseIf Range("K7").Value < Range("K7").Offset(x).Value And Range("L7").Value > Range("L7").Offset(x).Value Then
            Range("P7").Value = "Buy" And Range("J7").Offset(x).Value = Range("Q7").Value _
            And e = 1  'post results in cells
        ElseIf x > 35 Or Range("N7").Offset(x).Value > 0 Then
            e = 1      'Out of range so stop
        ElseIf e = 1 Then Exit Sub 'Done with this process
        Else: x = x + 1 'Go to next line down
        End If
    Loop
End If
      
End Sub

And that would be why I suck at programming. Plus, not being able to spell.

But, while it steps through now it doesn't leave any results. By that I mean that it increments, but the EsleIf doesn't leave anything in the cells it should. I can see that the x and e increment like they should.
So, I am guessing the
ElseIf Range("K7").Value < Range("K7").Offset(x).Value And Range("L7").Value > Range("L7").Offset(x).Value Then
Range("P7").Value = "Buy" And Range("J7").Offset(x).Value = Range("Q7").Value
line doesn't work.
I tried putting another statement in that would leave something in the R row but nothing gets there.

Thanks for trying on this.

 

[Micron]

Happy to assist where I can. Don't be too hard on yourself. When I first started dabbling in code I didn't know how to raise a message box, but I like to think I have come a long way (more so in Access vba but am gaining ground it Excel vba). I just re-formatted your code and hopefully eliminated a problem you were having. If it doesn't work as expected, that is another issue but I'm happy to assist if I can.

the EsleIf doesn't leave anything in the cells it should.

That portion and what comes after reads like you're familiar with stepping through and trouble shooting. If not, step through (f8) and validate your variable and reference values. It may be a simple logic error, like And vs Or, or a reference/variable is not what you expect at some point. F'rinstance, maybe x is not what you expect when you expect it.

 

[DavidSchilp]

Step though your code (F8) and check variable and reference values as you go. Note that a code line must be executed/processed in order to have these values applied. You can also use the immediate window to inquire about the values. If you type ?x and hit enter, the value of x should appear in the next line. If you're going to dabble in code, you really should research how to trouble shoot it. As for declarations, I prefer to see them all in the same block at the top as I hate looking for them when trouble shooting. Nice that you use code tags, but your indentation is a bit much. I can't see all from left to right without scrolling.

I had someone else look at it and they probably found the mistake. It's the old problem of trying to edit your own writing.
Looks like I left the _ off at end of wrapping lines. It was there at some point and in copying and pasting disappeared.
I'll let you know if that fixes it.

 

[DavidSchilp]

Happy to assist where I can. Don't be too hard on yourself. When I first started dabbling in code I didn't know how to raise a message box, but I like to think I have come a long way (more so in Access vba but am gaining ground it Excel vba). I just re-formatted your code and hopefully eliminated a problem you were having. If it doesn't work as expected, that is another issue but I'm happy to assist if I can.

That portion and what comes after reads like you're familiar with stepping through and trouble shooting. If not, step through (f8) and validate your variable and reference values. It may be a simple logic error, like And vs Or, or a reference/variable is not what you expect at some point. F'rinstance, maybe x is not what you expect when you expect it.

So I straightened it out a bit which will make it hard to post, but excel had me get rid of some stuff. Now when I run it this line:

ElseIf ((Range("K7").Value < Range("K7").Offset(x).Value) And (Range("L7").Value > Range("L7").Offset(x).Value)) Then Range("P7").Value = "Buy": Range("J7").Offset(x).Value = Range("Q7").Value: Range("R7").Offset(x).Value = "Here": Exit Sub

which I ran as a single line (no line break like it shows here) gives the Error of
Compile error
Else without If

The previous line is in the format --
IF xxx Then _
cccccc
which I read was needed for that If statement to then go on to ElseIf statements.

I did step through and it acts like it should, with exception of all the Then items being done. Another way to put it, all the IF parts make the macro advance, none of the pieces that should be making replacements or pasting seem to work. (I put more spaces in to make the parts more obvious.)
ie from above
Range("P7").Value = "Buy" : Range("J7").Offset(x).Value = Range("Q7").Value : Range("R7").Offset(x).Value = "Here"

 

[DavidSchilp]

Happy to assist where I can. Don't be too hard on yourself. When I first started dabbling in code I didn't know how to raise a message box, but I like to think I have come a long way (more so in Access vba but am gaining ground it Excel vba). I just re-formatted your code and hopefully eliminated a problem you were having. If it doesn't work as expected, that is another issue but I'm happy to assist if I can.

That portion and what comes after reads like you're familiar with stepping through and trouble shooting. If not, step through (f8) and validate your variable and reference values. It may be a simple logic error, like And vs Or, or a reference/variable is not what you expect at some point. F'rinstance, maybe x is not what you expect when you expect it.

I've been looping through it and am pretty sure I did not program it to do quite what I thought it was doing. I need to go back to starting over. I hope I have enough hair left.
Thanks for your help.

 

[Micron]

Probably should start a new thread if you get stuck. This one is getting hard to follow. Keep your blocks (If ... End If, Do ... Loop, etc.) properly aligned as I did because it helps to spot missing statements that end the block, or statements that are misplaced, causing blocks to be improperly nested.
Good luck!