Sep 23 2021 07:13 PM
Hi everyone,
Newbie here. (Office 365, Windows 10 user)
I run a lot of report on Excel files, and want to automatically create and format the cell range as a table and apply a table style.
However the macro I record and want to use for this limits the cell range to the spreadsheet cell range for which I record it.
This cell range will vary for different Excel spreadsheets.
The other thing it does is include the 'Table2' name (as it was the second document I had open).
Is there an edit I can make to the recorded Macro to automatically capture the cell range and name of table?
Thanks so much.
Sub Report1_Weekly()
'
' Weekly Report
'
'
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$6"), , xlYes).Name = _
"Table2"
Range("Table2[#All]").Select
ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight8"
Selection.Rows.AutoFit
Selection.Columns.AutoFit
End Sub
Sep 23 2021 07:35 PM - edited Sep 23 2021 07:37 PM
I broke rule number one in forums: always search first... Think I found my answer.
In the Macro I changed:
Range("$A$1:$F$6"),
to:
Range("A1").CurrentRegion,
Sep 26 2021 03:44 AM
@redhandler Sometimes I need the name and range in the workbook.
Addressing the table and then parse the formula is one way to do it.
G4 =Table1
F4 =MID(FORMULATEXT(G4);2;4711)
where 4711 just is high enough to get all the characters.
F2 parses the cell references;
=LET(table;Table1;
firstCell;INDEX(table;1;1);
lastcell;(INDEX(table;ROWS(table);COLUMNS(table)));
firstCellAddress;ADDRESS(ROW(firstCell);COLUMN(firstCell));
lastCellAddress;ADDRESS(ROW(lastcell);COLUMN(lastcell));
output;firstCellAddress & ":" & lastCellAddress;
output
)
.CurrentRegion is just fine and this gives similar possibilities without macros, eg if the table is moved from A1.
Sep 26 2021 04:12 AM - edited Sep 26 2021 04:17 AM
Considering you example:
To get the range you type:
=ADDRESS(@ROW(Table1),@COLUMN(Table1),4)&":"&ADDRESS(@ROW(Table1)+ROWS(Table1)-1,@COLUMN(Table1)+COLUMNS(Table1)-1,4)
LET() function is not available to all excel users thats why I developed another way to get the range.
To get the table name:
On G6 Type =Table1[[#Headers],[Text]]
On F6 Type=MID(FORMULATEXT(G6),2;FIND("[",FORMULATEXT(G6))-2)
Sep 26 2021 04:29 AM - edited Sep 26 2021 04:41 AM
In case you perfer an UDF and get all possible desired outputs.
Function GetTableInfo(AnyCell As Range, InfoType As Integer) As Variant
Dim TableOutput As String
TableOutput = vbNullString
On Error Resume Next
Select Case InfoType
Case 1 ' Table Name
TableOutput = AnyCell.ListObject.Name
Case 2 ' Table Range
TableOutput = AnyCell.ListObject.DataBodyRange.Address
Case 3 ' Number of Rows
TableOutput = AnyCell.ListObject.DataBodyRange.Rows.Count
Case 4 ' Number of Columns
TableOutput = AnyCell.ListObject.DataBodyRange.Columns.Count
Case Else
TableOutput = "Undefined"
End Select
GetTableInfo = TableOutput
End Function
Sep 26 2021 05:24 AM
One way to use LET is to make long formulas easier to read. Positive for both the builder in the future and for others using the model. Sep 2021 though, only available in 365.
Using =Table1 is like using =Table1[#Data] and when still easy to read, I prefer short versions.
@Juliano-Petrukio's approach with finding special characters holds more possibilities :) and is among others a way to get the name of linked files.
Two cells linking to different files
='[Open Workbook.xlsx]Sheet1'!$A$1
='C:\Users\bosinander\[Closed Workbook.xlsx]Sheet1'!$A$1
=LET(
string;FORMULATEXT(A1);
first;FIND("'";string);
last;FIND("]";string);
output;MID(string;first+1;last-first-1);
SUBSTITUTE(output;"[";"")
)
For version not having the LET function, it is the same as
=SUBSTITUTE(MID(FORMULATEXT(A1);FIND("'";FORMULATEXT(A1))+1;FIND("]";FORMULATEXT(A1))-FIND("'";FORMULATEXT(A1))-1);"[";"")
Output;
Open Workbook.xlsx
C:\Users\bosinander\Closed Workbook.xlsx
Sep 26 2021 05:34 AM
Sep 26 2021 05:44 AM
Review what is more applicable considering your environment. New formulas, "global" formulas or UDF (Using VBA Code)
Macro is the last resource to be used. Specially now that excel has tons of different features.
Yes, code must be easy to understand. Thanks