Share via

Can I use a variable while setting a different variable?

Thank Snakula 1 Reputation point
2021-04-30T21:27:46.13+00:00

Is it possible to use a string variable while setting the value of another variable?
I am trying to create a macro that works on the first table of any sheet no matter the name of the sheet or the table.

Below is what I have so far, but I keep getting the error:

Run-time error '1004':

Method 'Range' of object '_Global' failed

It's coming from the lines where I am setting the ranges and I assume it is because it is putting the TableName string into the formula with quotes around it, which seems to break the reference. Any way around this? I'm really stuck.

Sub PrioritySort()

'
' PrioritySort Macro
' Sort
'
' Keyboard Shortcut: Ctrl+Shift+P
'
' Declare Variables

' Create a named Variable for TableName
Dim TableName As String
' Create a named Variable for Tbl
Dim Tbl As ListObject
' Create a named Variable for Impact
Dim Impact As Range
' Create a named Variable for kWp
Dim kWp As Range

' Set TblName to be the first ListObject on the sheet
TableName = ActiveSheet.ListObjects(1)
' Set Tbl to be the first ListObject on the sheet
Set Tbl = ActiveSheet.ListObjects(TableName)
' Set Impact to be the highest impact column
Set Impact = Range("Table4[Highest Alert Impact]")
' Set kWp to be the Peak Power (kWp) column
Set kWp = Range("Table4[Peak Power (kWp)]")

'Sort Table
With Tbl.Sort
'Specify if header available
.Header = xlYes

' Clear Sorting on Tbl
.SortFields.Clear

' Sort Tbl by Color Red
.SortFields.Add(Key:=Impact, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(241, 169, 167)

' Sort LO by Color Orange
.SortFields.Add(Key:=Impact, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(244, 176, 132)

' Sort LO by Color Yellow
.SortFields.Add(Key:=Impact, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(255, 230, 153)

' Sort LO by Color Blue
.SortFields.Add(Key:=Impact, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(180, 198, 231)

' Sort LO by Color Indigo
.SortFields.Add(Key:=Impact, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(204, 204, 255)

' Sort LO by Color Gray
.SortFields.Add(Key:=Impact, Order:=xlAscending, SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(201, 201, 201)

' Sort LO by kWp High to Low
.SortFields.Add2 Key:=kWp, SortOn:=xlSortOnValues, Order:=xlDescending

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply
End With
End Sub

Thanks in advance for any help!

TS

Developer technologies | Visual Basic for Applications
0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.