Thread: [Solved] VBA: Referring to a table name variable within formula code
View Single Post
 
Old 08-02-2022, 11:48 AM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2019
Expert
  
Join Date: Apr 2014
Posts: 871
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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