CODEWITHSUNDEEP
excelvba
William Barnes
William Barnes

Reputation: 39

Dynamic Range division with two variables

I'm trying to complete division for a dynamic range with two variables and it keeps dividing the dynamic range by the last number in the range. Below is my VBA code.

 For i = 2 To 8
     For r = 13 To 19
         If ThisWorkbook.Sheets("Sheet1").Cells(i, 28) = "" Then
             ThisWorkbook.Sheets("Sheet1").Cells(r, 28) = ""
         Else
             ThisWorkbook.Sheets("Sheet1").Cells(r, 28) = ThisWorkbook.Sheets("Sheet1").Cells(i, 28) / Range("$AB$8")
         End If
     Next r
 Next i

Essentially it is dividing the last i value (Cell row 8) by the Range("$AB$8") (cells row 19).

What I would like to happen is the values in rows i to divide by Range("$AB$8")....in other words the value in cell (2,28)/ab8, (3/28)/ab8, (4,28)/ab8 etc etc. It current is taking the value in cell (8,28) dividing it by ab8...and applying it to all defined r rows.

Upvotes: 1

Views: 353

Answers (1)

AJD
AJD

Reputation: 2438

There are a number of issues here - all of which are small tweaks but end up with the wrong result you are seeing.

Your example code is not a dynamic range. You have hardcoded Cells(AB2:AB8) and Cells(AB13:AB19). You just did it in a way that is not obvious.

Also not very obvious is that you are writing the results to a single column. See the pattern here:

  • Loop 1: i = 2, results may be writing to Cells(AB13:AB19)
  • […]
  • Loop 7: i = 8, results may be writing to Cells(AB13:AB19)

I said "may" because you have the If statement.

Depending on what you really want to happen, the code can be amended.

  • Instead off the first loop put a conditional there (e.g. If all cells in that range are blank then …, or if any cells are blank then ...)
  • Use an Exit For after fixing the first blank loop
  • Also address the column (i.e. results spread across multiple columns)
  • Use a single loop (For i = 2 to 8 … and then adjust r according to i… r = i+12)

Upvotes: 1

Related Questions