CODEWITHSUNDEEP
vba
Florian
Florian

Reputation: 47

Loop with Lbounds and Ubounds?

My LastRowsht variable count the last row of the worksheet. If the LastRowsht + 1 = 2 (second row in Excel) there is nothing on the cell. After this my code divide a cell by another one but the denominator needs to change depending on the LastRowsht + 1. My lowerbound and upperbound needs to increment by 12 each time and same with the denominator. The probleme is there is no end of upperbound ( i have to do it until the end of worksheet) so i can't figure out how i can do this.

Can you help me make this loop ? Thank you.

Here is my code : 

    If LastRowsht + 1 = 2 Then
           sht.Cells(LastRowsht + 1, 17) = ""
        Else
        If 2 < LastRowsht + 1 < 15 Then
            sht.Cells(LastRowsht + 1, 17) = (sht.Cells(LastRowsht + 1, 7) / sht.Cells(2, 7)) - 1
        Else
        If 14 < LastRowsht + 1 < 27 Then
            sht.Cells(LastRowsht + 1, 17) = (sht.Cells(LastRowsht + 1, 7) / sht.Cells(14, 7)) - 1
        Else
        If 26 < LastRowsht + 1 < 39 Then
            sht.Cells(LastRowsht + 1, 17) = (sht.Cells(LastRowsht + 1, 7) / sht.Cells(26, 7)) - 1
        Else
        If 38 < LastRowsht + 1 < 51 Then
            sht.Cells(LastRowsht + 1, 17) = (sht.Cells(LastRowsht + 1, 7) / sht.Cells(38, 7)) - 1
        End If

Upvotes: 0

Views: 57

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

All you need to do is put this:

If LastRowsht + 1 = 2 Then
    sht.Cells(LastRowsht + 1, 17) = ""
Else
    sht.Cells(LastRowsht + 1, 17) = (sht.Cells(LastRowsht + 1, 7) / sht.Cells((2 + ((LastRowsht + 1 - 3) \ 12) * 12), 7)) - 1
End If

Note that "\" is the integer division operator.

Upvotes: 1

Related Questions