Based on the first column (A) and first row (1) in my active sheet, I'm calculating the sales value from a table called Shop, as seen 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?

Mar 27 in Others 158 views

## 1 answer to this question.

A VBA Lookup: Lookup Headers in an Excel Table

• Evaluating 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```
• 63,180 points

## Activating a Specific Cell in Excel Using VBA Results to Error 400

I think you trying to select cells(4, ...READ MORE

## Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

## Highlighting Unique List of Words in Each Cell of a Selection of Cells - Excel VBA

In a Textbox it is a vbcrlf ...READ MORE

## Converting Textboxes Link and/or Formula to Values in a Copied Sheet using Excel VBA

Copy the values using Range and Value2 With ActiveSheet.UsedRange ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE