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

0 votes

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:

enter image description here

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, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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

enter image description here

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)
        .Value = dvData
    End With

    ' Inform.
    MsgBox "Data updated.", vbInformation

End Sub
answered Mar 27, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

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

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

answered Dec 27, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

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

answered Jan 9, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

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

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

answered Jan 12, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

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

answered Jan 31, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

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

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

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

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP