Looping through excel cells to add formulas based on column header

Jonathan Brotto 1,076 Reputation points
2022-01-18T16:31:36.337+00:00

This seems to be a looping problem where I think the best approach is to have the outer loop going from left to right starting on column B and ending where it might be. The project I am working takes raw data and in the annual column will fetch data from first to last year with a minimum of 2 years. Then the inner loop I was thing going from row 3 till the row before total. With if statements for exceptions. For the quarter I have not coded for PY but thinking of an if statement where it would use a 3rd formula, but trying to get the variance vs PY in the annual section.

166018-first-column-formula-screen-shot.png
166019-third-column-formula.png
166113-py-formula-column.png
166104-total-rows2.gif

      columnCount = 1  
        Dim fillRowFormula As Integer = 3  
        Try  
            Do Until columnCount = countYearRange + 12  
                customerNameRng = Globals.ThisAddIn.Application.Sheets("CUSTOMER SUMMARY").range(Chr(65 + columnCount) & 2)  
                'using string as a place holder to see if column in row 2 is a number  
                currentYear = customerNameRng.Value2  
                If currentYear < "a" Then  
                    Do Until fillRowFormula = customerRowCount - 2  
                        customerNameRng = Globals.ThisAddIn.Application.Sheets("CUSTOMER SUMMARY").range(Chr(65 + columnCount) & fillRowFormula)  
                        customerNameRng.Value2 = "=SUM(" & Chr(65 + columnCount) & "3:" & Chr(65 + columnCount) & "3)"  
                        fillRowFormula = fillRowFormula + 1  
  
                    Loop  
                Else  
                    Do Until fillRowFormula = customerRowCount - 2  
                        customerNameRng = Globals.ThisAddIn.Application.Sheets("CUSTOMER SUMMARY").range(Chr(65 + columnCount) & fillRowFormula)  
                        customerNameRng.Value2 = "=(" & Chr(65 + columnCount) & fillRowFormula & "-" & Chr(65 + columnCount) & fillRowFormula & ")"  
                        fillRowFormula = fillRowFormula + 1  
  
                    Loop  
  
                End If  
                fillRowFormula = 3  
                columnCount = columnCount + 1  
  
            Loop  
        Catch ex As Exception  
  
        End Try  
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,554 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,439 questions
0 comments No comments
{count} votes