Link to home
Start Free TrialLog in
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

Avatar of Norie
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?
Avatar of 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 & _  ?
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

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

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

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

@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
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Now I get the following error:

User generated image
Could you post a sample workbook so I can reproduce the error?
ok. will take me a while to remove the confidential info. will get back asap.
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

just included in order to have a complete final working code, hope that's fine.