vendredi 19 avril 2019

How can I truncate these code blocks into one nested for loop?

I have 4 code blocks that take values from a row and do offsets +1, +2, +3, +4 and multiply the values under those cells (span 3 columns).

The code takes values I list in Sheet 2 and updates based off the rIterator variable (unique keys that show only what I need to update).

After I get the initial values in, I have to do the offset multiplication. How could I truncate this:

        wsA.Cells(fndRow, 3).Value = rIterator.Offset(, 2).Value
        wsA.Cells(fndRow, 4).Value = rIterator.Offset(, 3).Value
        wsA.Cells(fndRow, 5).Value = rIterator.Offset(, 4).Value

        wsA.Cells(fndRow + 1, 3).Value = rIterator.Offset(, 2).Value * 1.1
        wsA.Cells(fndRow + 1, 4).Value = rIterator.Offset(, 3).Value * 1.1
        wsA.Cells(fndRow + 1, 5).Value = rIterator.Offset(, 4).Value * 1.1

        wsA.Cells(fndRow + 2, 3).Value = rIterator.Offset(, 2).Value * 1.15
        wsA.Cells(fndRow + 2, 4).Value = rIterator.Offset(, 3).Value * 1.15
        wsA.Cells(fndRow + 2, 5).Value = rIterator.Offset(, 4).Value * 1.15

        wsA.Cells(fndRow + 3, 3).Value = rIterator.Offset(, 2).Value * 1.2
        wsA.Cells(fndRow + 3, 4).Value = rIterator.Offset(, 3).Value * 1.2
        wsA.Cells(fndRow + 3, 5).Value = rIterator.Offset(, 4).Value * 1.2

        wsA.Cells(fndRow + 4, 3).Value = rIterator.Offset(, 2).Value * 1.3
        wsA.Cells(fndRow + 4, 4).Value = rIterator.Offset(, 3).Value * 1.3
        wsA.Cells(fndRow + 4, 5).Value = rIterator.Offset(, 4).Value * 1.3

into something like :

        For i = 0 To 4
            For j = 3 To 5
                wsA.Cells(fndRow + i, j).Value = rIterator.Offset(, j - 1).Value
            Next j
        Next i

and just implement some kind of Case statement to take care of the 1.1 1.15 1.2 1.3 multipliers from the original value before the offsets?

Can provide rest of code if needed

worksheet example

Aucun commentaire:

Enregistrer un commentaire