Consulting

Results 1 to 3 of 3

Thread: Loop Thru Columns

  1. #1
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    305
    Location

    Loop Thru Columns

    Good evening all, I am trying to loop thru columns changing the cell formulas and am drawing a blank. It seems easy to me but I just can't get it. I'm new to R1C1, tried it but am having difficulties. Please see the comments in the code for my issues. I've searched the net and found plenty of info on R1C1 but cant get it right in my worksheet formulas. Can someone advise where i've gone wrong. Or advise a different way to accomplish this.
    My formulas should look something like this:
    Cell B5 is =SUM(Jan:June!$B5)
    Cell C5 is =SUM(Julyec!$B5) I don't no why the emoji shows here its July-Dec
    Cell D5 is =SUM(Jan:June!$C5)
    Cell E5 is =SUM(Julyec!$C5) I don't no why the emoji shows here its July-Dec
    And so on.

    Thanks
    Gary

    Sub GenerateFirstRowFormulas()
    Dim xRow As Integer
    Dim yCol As Integer
    Dim iCol As Integer
    Dim iRow As Integer
    
    
    ProtectSheet (False)
    
    iRow = 5
    xRow = 5
    yCol = 0
    'Generate the formulas in Row 5
    For iCol = 2 To 16
       yCol = yCol + 2
       
       'These formulas work except it inserts $ for absolute references but need relative references for copy routine
       Cells(xRow, yCol).FormulaR1C1 = "=SUM('Jan:June'!R" & iRow & "C" & iCol & ")"    
       Cells(xRow, yCol + 1).FormulaR1C1 = "=SUM('July:Dec'!R" & iRow & "C" & iCol & ")" 
       
       'These fomulas use relative references but inserts the wrong row in the formula (row 10 instead of 5)
       Cells(xRow, yCol).FormulaR1C1 = "=SUM('Jan:June'!R[" & iRow & "]C" & iCol & ")"    
       Cells(xRow, yCol + 1).FormulaR1C1 = "=SUM('July:Dec'!R[" & iRow & "]C" & iCol & ")" 
    Next iCol
    
    ProtectSheet (True)
    End Sub
    Last edited by zoom38; 11-17-2017 at 07:26 PM.

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    763
    Location
            Cells(xRow, yCol).FormulaR1C1 = "=SUM('Jan:June'!RC" & iCol & ")"
            Cells(xRow, yCol + 1).FormulaR1C1 = "=SUM('July:Dec'!RC" & iCol & ")"

  3. #3
    VBAX Mentor
    Joined
    Jan 2006
    Posts
    305
    Location
    Thank you very much. Works perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •