#1
|
|||
|
|||
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 Code:
Range("F3").FormulaR1C1 = _ "=SUMIF(TABLENAME[[#All],[Issue Type]],""<>Translation"",TABLENAME[[#All],[Word count]])" Thanks in advance. |
#2
|
||||
|
||||
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 |
#3
|
||||
|
||||
@ 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 |
#4
|
|||
|
|||
Thank you!
@p45cal
Quote:
@Pecoflyer Quote:
|
Thread Tools | |
Display Modes | |
|
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 |