VBA cell formula adding @

[Hudz]

Function below. When valType = Actuals, it should set each cell's formula to f.

The index/match formula compares 4 values in a table to produce the 5th as the result. Works fine as-is, but when vba writes the forumla, it adds @ symbols to each column header in the formula (i.e. ledger[company] becomes ledger[@company}. This is causing #Value instead of a real value. If I remove the @ symbols, the formula works fine.

Sub copyValues(month As String, valType As String, column As String)
    Dim destination As String
    Dim budget As String
    Dim forecast As String
    Dim rangeList As New Collection
    
    '1st val: starting row, 2nd val: end row
    rangeList.Add "7-10"
    rangeList.Add "12-16"
    rangeList.Add "18-36"
    rangeList.Add "47-54"
    rangeList.Add "60-68"
    rangeList.Add "76-84"
    rangeList.Add "90-98"
    rangeList.Add "104-115"
    rangeList.Add "126-157"
    rangeList.Add "176-237"
    rangeList.Add "245-301"
    rangeList.Add "313-374"
    rangeList.Add "382-539"
    rangeList.Add "547-573"
    rangeList.Add "583-605"
    rangeList.Add "613-651"
    rangeList.Add "665-674"

    budget = ThisWorkbook.Sheets("Settings").Cells(2, 4).Value
    forecast = ThisWorkbook.Sheets("Settings").Cells(2, 5).Value
    destination = "Alloc of Exp"
    
    If valType = "---" Then
        For i = 1 To rangeList.Count
            x = Split(rangeList.Item(i), "-")
            ThisWorkbook.Worksheets(destination).Range(column & x(0) & ":" & column & x(1)).Value = ""
        Next
    ElseIf valType = "Actuals" Then
        For i = 1 To 1 'rangeList.Count
            x = Split(rangeList.Item(i), "-")
            For r = x(0) To x(1)
                f = "=INDEX(ledger[balance],MATCH(A7&" & Chr(34) & "|""&B7&" & Chr(34) & "|""&C7&" & Chr(34) & "|""&E7&" & Chr(34) & "|""&F5,ledger[company]&" & Chr(34) & "|""&ledger[cc]&" & Chr(34) & "|""&ledger[account]&" & Chr(34) & "|""&ledger[project]&" & Chr(34) & "|""&ledger[period],0))"
                ThisWorkbook.Worksheets(destination).Range(column & r).Formula = f
            Next
        Next
    End If
End Sub

 

[Fluff]

You need to use Formula2 rather than Formula, especially when entering array formulae.

 

[Hudz]

You need to use Formula2 rather than Formula, especially when entering array formulae.

Perfect! Thanks so much

 

[Fluff]

You're welcome & thanks for the feedback.