2

I am new with VBA and I am working on a table which has electric vehicle loads. I have to check some conditions, if the conditions are not satisfied the car load should be decreased.

I have the following code but instead of reducing the load eg. from 11 to 10 or 9 or so on, it just deletes the load from the table. screenshots of starting case and after running code are attached.

Can someone help to correct the code?

Sub loopwork()    
    Column = 215
    Row = 1097
    columnmove = 109

    For Column = 215 To 315
        columnmove = columnmove + 1

        For Row = 1097 To 1192
            columnload = Column - columnmove
            Cells(Row, Column).Select
            Cells(Row, Column - 211).Select
            Cells(Row, columnload).Select
            self = Cells(Row, Column)
            firstcheck = Cells(Row, Column - 211)
            loadvalue = Cells(Row, columnload)

            If ((firstcheck < -0.05 Or firstcheck > 0.05 Or loadvalue > 100) And (self > 0)) Then
                Cells(Row, Column).Select
                Cells(Row, Column) = Cells(Row, Column) - 1
                Cells(1, 1).Select
                Row = Row - 1
            End If
        Next
    Next
End Sub

Before running code before running code

After running code after running code

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
osk
  • 63
  • 7
  • I think the issue is that `Row = Row - 1` makes your counter stay in this row and this way it keeps counting down from 11 to 0 because it increases that cell not only once but until `self > 0` is not true anymore. • To find out what your issue is I recommend to go through the code step by step (using F8) and check the values of your variables and see what happens at all. Also I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) because you used a lot of unnecessary `.Select` statements. – Pᴇʜ Sep 10 '18 at 08:29
  • What's with all the .Select statements that a) don't do anything at all b) are not desired in the first place? –  Sep 10 '18 at 08:41

1 Answers1

2

Take a look at,

If ((firstcheck < -0.05 Or firstcheck > 0.05 Or loadvalue > 100) And (self > 0)) Then

If self > 0 and one of the other conditions is true then you reduce self by 1 (e.g. Cells(Row, Column) = Cells(Row, Column) - 1) and adjust the For ... Next iterator by -1 so you repeat the same cycle.

However, you do nothing to change the values of firstcheck or loadvalue so if it passed the If test once, it will just keep passing the If test until self is not greater than 0.

That's why the loop exits once self is zero or as you put it 'it just deletes the load from the table'.