Loop formula in excel vba

Question

I am trying to loop a formula instead of using filldown because I have two formulas depending on the length of text in Column A's Cell.

My two formulas are like

.Range("C2").Formula = "=CHAR(83)&CHAR(45)&LEFT(A2,LEN(A2)-9)&CHAR(45)&UPPER(MID(A2,4,LEN(A2)-8))&CHAR(32)&D2&E2"

.Range("C2").Formula = "=LEFT(A2,LEN(A2)-9)&CHAR(45)&UPPER(MID(A2,8,LEN(A2)-12))&CHAR(32)&D2&E2"

This is what I have so far.

Sub AddFormulas()
Application.ScreenUpdating = False

Dim aCell As String
Dim cCell As String
Dim dCell As String
Dim eCell As String
Dim rowStart As Integer
Dim aCol As Integer
Dim cCol As Integer
Dim dCol As Integer
Dim eCol As Integer
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

rowStart = 2
aCol = 1
cCol = 3
dCol = 4
eCol = 5
'Range("A2")
aCell = Cells(rowStart, aCol).Address(RowAbsolute:=False, _
                                ColumnAbsolute:=False)
'Range("C2")
aCell = Cells(rowStart, cCol).Address(RowAbsolute:=False, _
                                ColumnAbsolute:=False)
'Range("D2")
dCell = Cells(rowStart, dCol).Address(RowAbsolute:=False, _
                                ColumnAbsolute:=False)
'Range("E2")
eCell = Cells(rowStart, eCol).Address(RowAbsolute:=False, _
                                ColumnAbsolute:=False)

With ThisWorkbook.Sheets("Sheet1")
        .Range(cCell).Formula = "=CHAR(83)&CHAR(45)&LEFT(aCell,LEN(aCell)-9)&CHAR(45)&UPPER(MID(aCell,4,LEN(aCell)-8))&CHAR(32)&(dCell)&(eCell)"
End With
Application.ScreenUpdating = True
End Sub

Answer 1

You can use an IF statement to ascertain the number of chars in a cell using LEN since you haven't specified how many chars are needed to determine which formula to employ. As Excel is intelligent enough to increment by row, you can then post your formula with just one line of code to the full range.

then change the LEN(A1)=2 to the length to trigger the first formula, change 123 to the first formula and change 987 to the second formula you want. If you are testing any cell other than column A, also change that in the LEN part of the formula.