Avatar of stmoritz
stmoritz
 asked on

Excel VBA - using array approach to write formulas to cells

Can the array approach below somehow be adapted to work? Or is it not possible with formulas?

Problem is that my list separator is "," and the comma appears numerous times in the formula itself... How to split?

Dim vFormula As Variant
Dim LastRow As Single
Dim vColHeadCell As Variant
Dim vColHeadNames As Variant
Dim sColHeadStrng As String
Dim vColHead As Variant
Dim i As Long

'set column header strings in data sheet
vColHead = Split("Date,USDEUR,USDJPY,USDCHF,USDGBP,USDAUD", ",")
    
'set formulas to be written in respective cell
vColHeadNames = Split("=IF(WEEKDAY(A195+1,2)<6,A195+1,IF(WEEKDAY(A195+2,2)<6,A195+2,IF(WEEKDAY(A195+3,2)<6,A195+3,A195+4))),=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(H$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0)),=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(I$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))", ",")
    
    For i = LBound(vColHead) To UBound(vColHead)
        sColHeadStrng = vColHead(i)
        LastRow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
        
        Set vColHeadCell = Sheets("Data").Rows(1).Find(What:=sColHeadStrng, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        If vColHeadCell Is Nothing Then
            MsgBox ("Column Heading " & sColHeadStrng & "not found!")
        Else
            Sheets("Data").Cells(LastRow, vColHeadCell.Column).Formula = vColHeadNames(i)
        End If
    
    Next i

Open in new window



Is the above approach possible and how can it be made to work? Or do I need to use the classic approach like for example:

Set vColHeadCell = Sheets("Data").Rows(1).Find(What:="Date", LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
Sheets("Data").Cells(LastRow, vColHeadCell.Column).Formula = "IF(WEEKDAY(A195+1,2)<6,A195+1,IF(WEEKDAY(A195+2,2)<6,A195+2,IF(WEEKDAY(A195+3,2)<6,A195+3,A195+4)))"

Set vColHeadCell = Sheets("Data").Rows(1).Find(What:="USDEUR", LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
Sheets("Data").Cells(LastRow, FormRow2).Formula = "))),=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(H$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))"

Open in new window

Microsoft ExcelVisual Basic ClassicVB Script

Avatar of undefined
Last Comment
stmoritz

8/22/2022 - Mon
Norie

You can use an array to put formulas in cells.

Why not delimit your formulas with something other than a comma eg a semi-colon?
stmoritz

ASKER
Thanks. I will try using a ;   -   in my first attempt it did not work.
How can I put every formula on a separate line in order to have it arranged more clearly?
Adding a _ at the end? with or without space before? or a & _  ?
byundt

You can use the Array statement instead of Split:
vColHeadNames = Array("=IF(WEEKDAY(A195+1,2)<6,A195+1,IF(WEEKDAY(A195+2,2)<6,A195+2,IF(WEEKDAY(A195+3,2)<6,A195+3,A195+4)))", _
    "=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(H$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))", _
    "=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(I$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))", _
    ",")

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
stmoritz

ASKER
So the approach with the ; does not work yet (split basis). Any idea how to fix it or shall I recode to Array instead of split?

Dim vFormula As Variant
Dim LastRow As Single
Dim vColHeadCell As Variant
Dim vColHeadNames As Variant
Dim sColHeadStrng As String
Dim vColHead As Variant
Dim i As Long

'set column header strings in data sheet
vColHead = Split("Date;USDEUR;USDJPY;USDCHF;USDGBP;USDAUD", ",")
    
'set formulas to write to data sheet
vColHeadNames = Split("=IF(WEEKDAY(A195+1,2)<6,A195+1,IF(WEEKDAY(A195+2,2)<6,A195+2,IF(WEEKDAY(A195+3,2)<6,A195+3,A195+4))); _
=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(H$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0)); _
=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(I$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0)); _
=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(K$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0)); _
=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(L$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))"; ",")
  
    For i = LBound(vColHead) To UBound(vColHead)
        sColHeadStrng = vColHead(i)
        LastRow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
        
        Set vColHeadCell = Sheets("Data").Rows(1).Find(What:=sColHeadStrng, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        If vColHeadCell Is Nothing Then
            MsgBox ("Column Heading " & sColHeadStrng & "not found!")
        Else
            Sheets("Data").Cells(LastRow, vColHeadCell.Column).Formula = vColHeadNames(i)
        End If
    
    Next i

Open in new window

byundt

The character used as a separator in Split must not otherwise appear in the text. If a comma is used in your formulas, then you need to use a different character, such as the pipe symbol |.
vColHeadNames = Split("=IF(WEEKDAY(A195+1,2)<6,A195+1,IF(WEEKDAY(A195+2,2)<6,A195+2,IF(WEEKDAY(A195+3,2)<6,A195+3,A195+4)))|=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(H$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))|=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(I$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))", "|")

Open in new window

My previous suggestion of using Array was intended as a direct replacement for the statement using Split. Although it doesn't affect anything, there was a small goof at the end of the statement. I should have suggested the following:
vColHeadNames = Array("=IF(WEEKDAY(A195+1,2)<6,A195+1,IF(WEEKDAY(A195+2,2)<6,A195+2,IF(WEEKDAY(A195+3,2)<6,A195+3,A195+4)))", _
    "=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(H$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))", _
    "=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(I$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0))") 

Open in new window

Brad
stmoritz

ASKER
Thanks Brad.

1) If I use Array instead of Split, I assume I also have to change line 10, right?
2) Whether Split or Array, I do have to separate the formulas with a chracter as separator that is not part of the formula, so no "," - correct?
3) If I change to Array, how do I have to adapt line 19 to 33

(all lines refered to the most recent version of code I posted)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

Using ; as the delimiter does work but you are still specifying the comma as the delimiter.

This worked for me, though since both arrays are unequal sizes, vColHead has 6 items and vColHeadNames only has 5, there is a subscript out of range error and the  last currency is missed out.

Dim vFormula As Variant
Dim LastRow As Single
Dim vColHeadCell As Variant
Dim vColHeadNames As Variant
Dim sColHeadStrng As String
Dim vColHead As Variant
Dim i As Long

'set column header strings in data sheet
vColHead = Split("Date;USDEUR;USDJPY;USDCHF;USDGBP;USDAUD", ";")
    
'set formulas to write to data sheet
vColHeadNames = Split("=IF(WEEKDAY(A195+1,2)<6,A195+1,IF(WEEKDAY(A195+2,2)<6,A195+2,IF(WEEKDAY(A195+3,2)<6,A195+3,A195+4)));" & _
"=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(H$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0));" & _
"=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(I$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0));" & _
"=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(K$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0));" & _
"=INDEX('G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A196,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(L$1,'G:\data\[daily_fx_closing_history.xls]HistDailyFxData'!$A$1:$IV$1,0));", ";")
  
  
    For i = LBound(vColHead) To UBound(vColHead)
        sColHeadStrng = vColHead(i)
        LastRow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1
        
        Set vColHeadCell = Sheets("Data").Rows(1).Find(What:=sColHeadStrng, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        If vColHeadCell Is Nothing Then
            MsgBox ("Column Heading " & sColHeadStrng & "not found!")
        Else
            Sheets("Data").Cells(LastRow, vColHeadCell.Column).Formula = vColHeadNames(i)
        End If
    
    Next i

Open in new window

stmoritz

ASKER
Thanks a lot for your hint that the arrays are not identical in numbers. This is a mistake on my side and not willingly. If it works, I shall allocate points and then open a new question on how to replace the row number 196 in the formulas with value of LastRow variable and 195 with LastRow-1.
byundt

At statement 31 in your last code snippet, try the following:
Sheets("Data").Cells(LastRow, vColHeadCell.Column).Formula = _
    Replace(Replace(vColHeadNames(i), "A195", "A" & (LastRow - 1)), "A196", "A" & LastRow)

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
stmoritz

ASKER
@byundt

Thanks a lot. Unfortunately it does not work, because your solution changes first 195 to LastRow -1 which then is 196 and then it gets changed again caused by the second statement. So I guessed I just swap places and then it should work:

Sheets("Data").Cells(LastRow, vColHeadCell.Column).Formula = _
    Replace(Replace(vColHeadNames(i), "A196", "A" & LastRow, "A195", "A" & (LastRow - 1)))
                                            

Open in new window


But I was wrong... It doesn't work like this as it returns the error message:
Argument not optional
While the replace in line 2 here above is highlighted.

Can this be easily fixed or is there a (maybe better) way to define the actual row number to be used in the formulas before the arrays are defined in line 12 and put them into a variable to be used when composing the formula in the array? (Hope this is comprehensible, english is not my mother tongue)
SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
stmoritz

ASKER
Now I get the following error:

err msg
byundt

Could you post a sample workbook so I can reproduce the error?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
stmoritz

ASKER
ok. will take me a while to remove the confidential info. will get back asap.
stmoritz

ASKER
Create a folder "data" on your c: and extract the zip with the two files there. It should work. Or alternatively adjust the path in the formula.

Code is in the file valuation_data.xls
ExpExch.zip
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

Forgot to mention, I added 1 to LastRow to prevent the last row of existing data being overwritten.

If that's incorrect just remove the +1 on that line.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
stmoritz

ASKER
Thanks imnorie. Will test it. Just quick question, can I replace Split with Array in line 13?

 vColHead = Split("Date;USDEUR;USDJPY;USDCHF;USDGBP;USDAUD", ";")

Open in new window

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

Just noticed that myself, here's the updated code with the Split replaced by Array.
Sub InsertFormulasInLastRow()

'Write formulas for date and currencies to cells
Dim vFormula As Variant
Dim LastRow As Single
Dim vColHeadCell As Variant
Dim vColHeadNames As Variant
Dim sColHeadStrng As String
Dim vColHead As Variant
Dim i As Long

    'set column header strings in data sheet
    vColHead = Array("Date", "USDEUR", "USDJPY", "USDCHF", "USDGBP", "USDAUD")

    LastRow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row + 1

    vColHeadNames = Array("=IF(WEEKDAY(A" & LastRow - 1 & "+1,2)<6,A" & LastRow - 1 & "+1,IF(WEEKDAY(A" & LastRow - 1 & "+2,2)<6,A195+2,IF(WEEKDAY(A195+3,2)<6,A" & LastRow - 1 & "+3,A" & LastRow - 1 & ")))", _
                          "=INDEX('C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A" & LastRow & ",'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(H$1,'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$1,0))", _
                          "=INDEX('C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A" & LastRow & ",'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(I$1,'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$1,0))", _
                          "=INDEX('C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A" & LastRow & ",'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(J$1,'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$1,0))", _
                          "=INDEX('C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A" & LastRow & ",'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(K$1,'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$1,0))", _
                          "=INDEX('C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$65536,MATCH($A" & LastRow & ",'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$A$65536,0),MATCH(L$1,'C:\data\ExpExch\[daily_fx_hist.xls]HistDailyFxData'!$A$1:$IV$1,0))")

    For i = LBound(vColHead) To UBound(vColHead)
        sColHeadStrng = vColHead(i)


        Set vColHeadCell = Sheets("Data").Rows(1).Find(What:=sColHeadStrng, LookIn:=xlValues, _
                                                       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                       MatchCase:=False, SearchFormat:=False)

        If vColHeadCell Is Nothing Then
            MsgBox ("Column Heading " & sColHeadStrng & "not found!")
        Else
            Sheets("Data").Cells(LastRow, vColHeadCell.Column).Formula = vColHeadNames(i)
        End If

    Next i


End Sub

Open in new window

stmoritz

ASKER
just included in order to have a complete final working code, hope that's fine.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.