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?
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:
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
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))"
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 & _ ?
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))", _
",")
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
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))", "|")
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))")
Brad
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)
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.
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
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.
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)
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:
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)
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)))
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Could you post a sample workbook so I can reproduce the error?
ASKER
ok. will take me a while to remove the confidential info. will get back asap.
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
Code is in the file valuation_data.xls
ExpExch.zip
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
If that's incorrect just remove the +1 on that line.
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", ";")
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
just included in order to have a complete final working code, hope that's fine.
Why not delimit your formulas with something other than a comma eg a semi-colon?