troubleshooting Question

Excel VBA - using array approach to write formulas to cells

Avatar of stmoritz
stmoritz asked on
Visual Basic ClassicMicrosoft ExcelVB Script
21 Comments3 Solutions410 ViewsLast Modified:
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


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 CERTIFIED SOLUTION
NorieAnalyst Assistant
Join our community to see this answer!
Unlock 3 Answers and 21 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 21 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros