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, 2022 in Others by Kithuzzz
• 38,010 points
626 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, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
852 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,264 views
0 votes
1 answer

See cell content instead of formula in formulabox in Excel using VBA

A VBA Lookup: Lookup Headers in an ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,700 points
305 views
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, 2022 in Others by Edureka
• 13,670 points
606 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,740 points
957 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
3,288 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, 2022 in Others by gaurav
• 23,260 points
581 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, 2022 in Others by Edureka
• 13,670 points
815 views
0 votes
1 answer

Excel VBA: Open Hyperlinks in a loop and copy paste download link in a sheet

Refer this tutorial for your solution: https://evermap.com/Tutorial_AB ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,700 points
1,060 views
0 votes
1 answer

Converting Textboxes Link and/or Formula to Values in a Copied Sheet using Excel VBA

Copy the values using Range and Value2 With ActiveSheet.UsedRange ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,700 points
406 views
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