We help IT Professionals succeed at work.

Excel VBA - using array approach to write formulas to cells

407 Views
Last Modified: 2012-06-03
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

Comment
Watch Question

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
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?

Author

Commented:
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 & _  ?
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

Commented:
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

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

Commented:
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)
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

Commented:
@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)
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Now I get the following error:

err msg
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Could you post a sample workbook so I can reproduce the error?

Author

Commented:
ok. will take me a while to remove the confidential info. will get back asap.

Author

Commented:
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
Analyst Assistant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
NorieAnalyst Assistant
CERTIFIED EXPERT

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

Author

Commented:
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

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
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

Author

Commented:
just included in order to have a complete final working code, hope that's fine.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.