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.

p45cal · #2 08-02-2022, 11:48 AM

If there's only 1 table on each sheet, try:

Code:

For Each ws In Sheets
  With ws
    TblName = .ListObjects(1).Name
    .Range("A1:A6").EntireRow.Insert
    .Range("F3").FormulaR1C1 = "=SUMIF(" & TblName & "[Issue Type],""<>Translation""," & TblName & "[Word Count])"
    .Range("F4").FormulaR1C1 = "=SUMIF(" & TblName & "[Issue Type],""Translation""," & TblName & "[Word Count])"
    .Range("E3").Value = "Proofreading"
    .Range("E4").Value = "Translation"
    With .Range("E3:F4")
      .Font.Bold = True
      .HorizontalAlignment = xlCenter
      .BorderAround Weight:=xlMedium
    End With
  End With
Next ws

Pecoflyer · #3 08-02-2022, 11:43 PM

@ le robert
FYI there is a specific subforum dedicated to VBA questions

le_robert · #4 08-09-2022, 06:45 AM

@p45cal

Quote:

If there's only 1 table on each sheet, try:

That worked a treat, thank you

!

@Pecoflyer

Quote:

@ le robert
FYI there is a specific subforum dedicated to VBA questions

Apologies, I think I did know that. I'll make sure to post properly next time.

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