Loop formula in excel vba

0 votes

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
Nov 25 in Others by Kithuzzz
• 20,660 points
23 views

1 answer to this question.

0 votes
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.
answered Nov 25 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Language independent way to get "My Documents" folder in VBA Excel 2003

 Hello :)  This code may help you in your ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
86 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18 in Others by Edureka
• 13,640 points
81 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
103 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
119 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
451 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,218 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP