# Looping through excel cells to add formulas based on column header

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.

``````      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