0

I'm setting a range which I've called SCT_table which would cover the table on worksheet one called SCT_ws, if this is not the active worksheet I get the error Run-time error '1004' and the Set SCT_table line is highlighted when I press debug. Now if I select this worksheet before setting the range (forcing it to be the active worksheet) it runs without a problem. Below is the code:

Sub Join_Suitable_changes()

Application.ScreenUpdating = False

Dim SCT_ws As Worksheet
Dim SCT_num_rows, SCT_num_cols, score_col As Long
Dim SCT_table As Range

Set SCT_ws = ActiveWorkbook.Worksheets(1) 

score_col = 7 

SCT_num_rows = SCT_ws.Range("A" & Rows.Count).End(xlUp).Row
SCT_num_cols = SCT_ws.Cells(1, Columns.Count).End(xlToLeft).Column

'SCT_ws.Select 'Works with this uncommented

Set SCT_table = SCT_ws.Range("A1", Cells(SCT_num_rows, SCT_num_cols))
'This causes a problem

SCT_table.Sort Key1:=Cells(1, score_col), Order1:=xlAscending, Header:=xlYes
'Sorts the table on column 7

Application.ScreenUpdating = True

End Sub

I tried this question Cannot set a range variable without selecting sheet first which suggested using with, and when I changed the Set part of my code to the following:

With SCT_ws
    Set SCT_table = .Range("A1", .Cells(SCT_num_rows, SCT_num_cols))
End With

I no longer need to select the WS in order to set the range (yay), however, I still need to select the WS in order for the Sort to work. So unfortunately this has not stopped the use of Select.

I'd appreciate any explanation as to why this is the case and how I can avoid the use of Select.

Violet Flare
  • 162
  • 1
  • 8
  • 3
    Do this.... `Set SCT_table = SCT_ws.Range("A1", SCT_ws.Cells(SCT_num_rows, SCT_num_cols))` This has been covered many times in the past. You need to fully qualify the range – Siddharth Rout Aug 28 '19 at 11:11
  • You can do either what @SiddharthRout said or add the sorting inside the `With SCT_ws` and then full qualify the range with a `.` in fron of `Cells...` – Damian Aug 28 '19 at 11:13
  • 2
    The actual explanation is that if you use cells without explicitly specifying a sheet then VBA assumes you are referring to the activesheet. in the case of your sort, this means you are trying to sort cells on one sheet with a key defined on another – Harassed Dad Aug 28 '19 at 11:21
  • Here is [one](https://stackoverflow.com/questions/18962890/2-ways-for-clearcontents-on-vba-excel-but-1-work-fine-why) such question. If you search Google for `fully qualify your cells site:stackoverflow.com`, you will get many more links – Siddharth Rout Aug 28 '19 at 11:27
  • @SiddharthRout If you make your answer a comment I'll accept, though I also needed to do the same for the Sort part of the code `SCT_table.Sort Key1:=SCT_ws.Cells(1, score_col), Order1:=xlAscending, Header:=xlYes` Also thanks for the link to the question, I wasn't sure what the problem was so I didn't know what to search. Thanks @Harassed Dad for the explanation also. – Violet Flare Aug 28 '19 at 11:59
  • 1
    Unfortunately, I can't post an answer to this question as it would not be the right thing to do in my opinion... I will close it as a duplicate though... – Siddharth Rout Aug 28 '19 at 12:34

0 Answers0