VBA: Referring to a table name variable within formula code

le_robert · #1 08-02-2022, 07:45 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Code to find a named (bookmarked?) table, replicate a row or table, and delete a specified table.	kevinbradley57	Word VBA	9	09-21-2017 04:58 PM
Variable data set in Average formula	Filip88	Excel	12	09-17-2017 06:54 AM
Is there code for variable fill down in excel	jtp607	Excel	4	06-08-2017 11:50 AM
Variable text field code based on occurrences on each page	Cosmo	Word	2	12-29-2015 11:54 AM
Filling a range variable into a formula	Officer_Bierschnitt	Excel Programming	3	11-23-2015 03:30 AM