View Single Post
 
Old 08-02-2022, 07:45 AM
le_robert le_robert is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2021
Location: France
Posts: 10
le_robert is on a distinguished road
Default VBA: Referring to a table name variable within formula code

Hello,

I have written a macro to split a large table into several smaller ones on separate worksheets. Once this is done, I want to put the same two sumif formulae in the same cell in each sheet. My current code, which works, uses columns as references:

Code:
For Each ws In Sheets

    ws.Activate
        Range("A1:A6").EntireRow.Insert
        Range("F3").Formula = _
        "=sumif(E:E,""<>Translation"",H:H)"
        Range("F4").Formula = _
        "=sumif(E:E,""Translation"",H:H)"
        Range("E3").Value = "Proofreading"
        Range("E4").Value = "Translation"
    
    With Range("E3:F4")
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .BorderAround Weight:=xlMedium
    End With
    
    Range("A1").Select
    
Next ws
However, I would like to be able to reference the named range within the table, the formula for which (based on a recorded macro) would be something like:

Code:
    Range("F3").FormulaR1C1 = _
        "=SUMIF(TABLENAME[[#All],[Issue Type]],""<>Translation"",TABLENAME[[#All],[Word count]])"
Is it possible to define a variable, such as ActiveSheet.ListObjects(1), and inserting the variable into the formula so that when the loop goes to each sheet, it does the calculation based on the table in that sheet (replacing the placeholder "TABLENAME" in my example code? I've tried several different things and none seem to work. If it's of any use, I've made it so the table on each sheet has the same name as the sheet.

Thanks in advance.
Reply With Quote