VBA cell formula adding @

Hudz

New Member
Joined
Oct 27, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.



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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,149
Office Version
  1. 365
Platform
  1. Windows
You need to use Formula2 rather than Formula, especially when entering array formulae.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,149
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,290
Messages
5,577,218
Members
412,777
Latest member
jmulldome
Top