0

I am coding two loops. The first loop sums a column 14 times and then goes through each worksheet in the workbook until they are all calculated. The second loop is more complex. The formula, if you were to enter it manually should be =(OFFSET(N39, -1, 0, 1, 1)) / (OFFSET($N$39, -1, -1, 1, 1)). This formula should loop 13 times. Each time it calculates, it should give the percentage of a total bucket, so as an example, it could tell you that 93% of your accounts receivables are current, then 2% are 1-10 days late, then 1.5% is 11-30 days late, etc. I cannot figure out how to code the loop that is calculating the percentage of each column. Below is my code:

    Sub Totals()
      Dim LastCell As Range
        For Each e In Worksheets
            e.Activate
            Range("M1").Select
                For i = 1 To 14
                    Set LastCell = Cells(Columns.Count, ActiveCell.Column).End(xlUp)
                    LastCell.Offset(1).Value = WorksheetFunction.Sum(Range(LastCell.End(xlUp), LastCell))
                    ActiveCell.Offset(0, 1).Select
                Next i
          ActiveCell.Offset(1, 0).Select
        Next e

            For Each d In Worksheets
            d.Activate
            Range("N1").Select
                For g = 1 To 13
                    Set LastCell = Cells(Columns.Count, ActiveCell.Column).End(xlUp)
                    LastCell.Offset(1).Value = "=(Offset(N39, -1, 0, 1, 1)) / (Offset($N$39, -1, -1, 1, 1))"
                    ActiveCell.Offset(0, 1).Select
                Next g
            Next d
    End Sub

The first loop works fine. The second loop does not, however.

End result:

End Result

Community
  • 1
  • 1
Gizzzmo68
  • 3
  • 3
  • Why would you use `OFFSET($N$39, -1, -1, 1, 1)` rather than just `$M$38`? – Rory Nov 30 '17 at 16:23
  • I used offset because each worksheet is using a dynamic number of records, so $M$38 won't work. I don't know how to code the portion of the formula that should be dynamic. I just provided the formula so anyone reading could understand what I am trying to do. Does that makes sense? – Gizzzmo68 Nov 30 '17 at 16:27
  • But you've got hardcoded offsets from a fixed reference, so it's exactly the same as a different fixed reference. I suspect your issue is related to using `Columns.Count` as the **row** argument for `Cells`. – Rory Nov 30 '17 at 16:41

1 Answers1

0

Try to alter the For Loop to the following: 

x = 1
For g = 14 To 26
lastrow = ActiveSheet.Cells(Rows.Count, "N").End(xlUp).Row + 1
ActiveSheet.Cells(lastrow, g).Value = "=R[-1]C/R[-1]C[-" & x & "]"
x = x + 1
Next g

I have now tested this and it works accordingly...

Xabier
  • 7,587
  • 1
  • 8
  • 20
  • When I replace the code with yours, this is what I see: #NAME? #NAME? #NAME? #NAME? – Gizzzmo68 Nov 30 '17 at 16:52
  • That still won't work - you can't use `Cells` in a formula as it's VBA. – Rory Nov 30 '17 at 16:59
  • I appreciate you trying to help me very much. I am receiving the same result, but I can see the formulas changing this time. Here's what I need this thing to do: The first column needs to be N#/$M$#. The second column needs to be O#/$M$#. The third column needs to be P#/$M$#, then Q#/$M$#, then R#/$M$#, then S#/$M$#, then T#/$M$#, then U#/$M$#, then V#/$M$#, then W#/$M$#, until this is done 13 times over the course of 13 columns...not rows. It will always begin at column N and will go to column Z, but the row number will be dynamic and the denominator will be fixed. Does this help? – Gizzzmo68 Nov 30 '17 at 17:06
  • I have updated my answer, I think now it should do as you expect. – Xabier Nov 30 '17 at 17:07
  • That's close, but it must always divide by the value in column M, as each bucket is a percentage of the number in column M. Does this make sense? I don't know how to fix that cell like you would do with a dollar sign in an actual formula (without VBA). – Gizzzmo68 Nov 30 '17 at 17:11
  • Oh my gosh...this is SO CLOSE. Thank you for helping me thus far. The only thing it's not doing is advancing to a new row to make the calculations, so it is currently overwriting the cells that the first loop calculated. If it could find the first blank row and begin the calculation, it will be correct. I think this is a simple fix, but my brain is now scrambled and I am having difficulty figuring out where in the nested loops this advancement needs to occur. – Gizzzmo68 Nov 30 '17 at 17:35
  • How about now? :) – Xabier Nov 30 '17 at 17:36
  • Still so close! I have stepped through the loop and I can't see where to make the change, but here's what is happening. The first iteration of the loop locates the first blank row and makes the proper calculation. The second and all subsequent iterations advance to the next row and make the calculation, so as an example, the first iteration calculates on row 39. Subsequent iterations all occur on row 40, so row 39 is blank in columns O through Z. Row 40 is calculating columns O through Z. Thank you again for all of your help. I just need this last piece and I'm going to do a happy dance! – Gizzzmo68 Nov 30 '17 at 17:51
  • I got it! Yay! Thank you so much! x = 1 For g = 14 To 26 Set LastCell = Cells(Columns.Count, ActiveCell.Column).End(xlUp) LastCell.Offset(1).Value = "=R[-1]C/R[-1]C[-" & x & "]" x = x + 1 ActiveCell.Offset(0, 1).Select Next g – Gizzzmo68 Nov 30 '17 at 18:36
  • If this has helped you, please could you mark it as an Answer... Thanks – Xabier Nov 30 '17 at 18:53
  • I marked it as the answer already, but it won't allow me to vote because I am not a high enough rank. Thank you again for your help. You really positively impacted my workload. – Gizzzmo68 Nov 30 '17 at 20:16