See cell content instead of formula in formulabox in Excel using VBA

Question

I'm looking up the sales value from a table called Shop based on the first column (A) and first row (1) in my active sheet, as shown below:

I'm using the following working code:

lastRow = Range("A1").End(xlDown).Row
lastColumn = Range("A1").End(xlRight).Column

'i -> rows
For i = 2 to lastRow
   
   shopName = Cells(i, 1).Value

   'j -> cols
   For j = 2 to lastColumn

      shopRegion = Cells(1, j).Value
      
      Cells(i,j).FormulaArray = "=Index(Shop[Sales], Match(1, (RC[" & (1- j) & "] = Shop[Name])*(R[" & (1- i) & "]C = Shop[Region]), 0))"

   Next j
Next i

I see the correct values populate in the cells.

1. I want to see the cell content instead of a formula in the formulabox in Excel. I tried Application.Evaluate, but that didn't work.

2. (Not important) Is there a way to use the variable shopName and shopRegion in the Cells(i,j).FormulaArray instead of Relative Reference RC?

Answer 1

A VBA Lookup: Lookup Headers in an Excel Table

• Evaluate in either flavor will not work with this kind of formula.

• After you have written the formulas you could copy/paste values e.g.:

  Dim rg As Range: Set rg = Range("A1").CurrentRegion
  rg.Value = rg.Value
  

  This will also copy the headers but they won't mind.

• If you want to be more accurate and exclude the headers (Shops and Regions), use:

  With rg.Resize(rg.Rows.Count - 1, rg.Columns.Count - 1).Offset(1, 1)
      .Value = .Value
  End With
  

Sub UpdateData()
    
    ' Constants
    Const SRC_SHEET As String = "Sheet1"
    Const SRC_TABLE As String = "Shop"
    Const SRC_ROWS As String = "Name"
    Const SRC_COLUMNS As String = "Region"
    Const SRC_VALUES As String = "Sales"
    Const DST_SHEET As String = "Sheet1"

    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook  ' workbook containing this code
    
    ' Source to Arrays
    
    Dim sws As Worksheet: Set sws = wb.Sheets(SRC_SHEET)
    Dim slo As ListObject: Set slo = sws.ListObjects(SRC_TABLE)
    Dim srCount As Long: srCount = slo.DataBodyRange.Rows.Count
        
    Dim srData(): srData = slo.ListColumns(SRC_ROWS).DataBodyRange
    Dim scData(): scData = slo.ListColumns(SRC_COLUMNS).DataBodyRange
    Dim svData(): svData = slo.ListColumns(SRC_VALUES).DataBodyRange

    ' Destination to Dictionaries

    Dim dws As Worksheet: Set dws = wb.Sheets(DST_SHEET)
    Dim drg As Range: Set drg = dws.Range("A1").CurrentRegion
    
    ' Names
    Dim drCount As Long: drCount = drg.Rows.Count - 1
    Dim drData(): drData = drg.Resize(drCount, 1).Offset(1).Value
    Dim rDict As Object: Set rDict = CreateObject("Scripting.Dictionary")
    rDict.CompareMode = vbTextCompare
    Dim dr As Long
    For dr = 1 To UBound(drData, 1)
        rDict(drData(dr, 1)) = dr
    Next dr
    Erase drData
    
    ' Region
    Dim dcCount As Long: dcCount = drg.Columns.Count - 1
    Dim dcData(): dcData = drg.Resize(1, dcCount).Offset(, 1).Value
    Dim cDict As Object: Set cDict = CreateObject("Scripting.Dictionary")
    cDict.CompareMode = vbTextCompare
    Dim dc As Long
    For dc = 1 To UBound(dcData, 2)
        cDict(dcData(1, dc)) = dc
    Next dc
    Erase dcData
    
    ' Values
    Dim dvData(): ReDim dvData(1 To drCount, 1 To dcCount)
    
    ' Dictionary to Destination Values Array
    
    Dim sr As Long
    
    For sr = 1 To srCount
        If rDict.Exists(srData(sr, 1)) Then
            If cDict.Exists(scData(sr, 1)) Then
                dvData(rDict(srData(sr, 1)), cDict(scData(sr, 1))) _
                    = svData(sr, 1)
            End If
        End If
    Next sr
    
    ' Destination Values Array to Destination Range
   
    With drg.Resize(drCount, dcCount).Offset(1, 1)
        .ClearContents
        .Value = dvData
    End With

    ' Inform.
    MsgBox "Data updated.", vbInformation

End Sub