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.
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.
VBA Code:
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