Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-02-2022, 07:45 AM
le_robert le_robert is offline VBA: Referring to a table name variable within formula code Windows 10 VBA: Referring to a table name variable within formula code Office 2016
Novice
VBA: Referring to a table name variable within formula code
 
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
  #2  
Old 08-02-2022, 11:48 AM
p45cal's Avatar
p45cal p45cal is offline VBA: Referring to a table name variable within formula code Windows 10 VBA: Referring to a table name variable within formula code Office 2019
Expert
 
Join Date: Apr 2014
Posts: 648
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

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
Reply With Quote
  #3  
Old 08-02-2022, 11:43 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline VBA: Referring to a table name variable within formula code Windows 7 64bit VBA: Referring to a table name variable within formula code Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,605
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

@ le robert
FYI there is a specific subforum dedicated to VBA questions
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 08-09-2022, 06:45 AM
le_robert le_robert is offline VBA: Referring to a table name variable within formula code Windows 10 VBA: Referring to a table name variable within formula code Office 2016
Novice
VBA: Referring to a table name variable within formula code
 
Join Date: Jul 2021
Location: France
Posts: 10
le_robert is on a distinguished road
Default Thank you!

@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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA: Referring to a table name variable within formula code 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
VBA: Referring to a table name variable within formula code 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
VBA: Referring to a table name variable within formula code Variable text field code based on occurrences on each page Cosmo Word 2 12-29-2015 11:54 AM
VBA: Referring to a table name variable within formula code Filling a range variable into a formula Officer_Bierschnitt Excel Programming 3 11-23-2015 03:30 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft