Solved: Set Worksheet Name and Locate Range Info

[brorick]

I have cell "o1" populated with the name of a worksheet and cell "n1" populated with the name of a range. I then set the cboRangeData combo box to the data in the range. Can anyone tell me why the following code is giving me a subscript out of range error? I just can't figure it out. I am facing a deadline, any help is greatly appreciated. : pray2:


Dim strWkCrit As String
Dim strCrit As String
Dim rTable As Range
Dim ws As Worksheet

strCrit = Range("n1")
strWkCrit = Range("o1")

Set ws = Worksheets(strWkCrit)
Set rTable = Range(strCrit)

Worksheets(ws).Range(strCrit).Sort _
Key1:=Worksheets(ws).Range(strCrit), _
Order1:=xlAscending, Header:=xlYes, _
Key2:=Worksheets(ws).Range(strCrit)

Set rTable = rTable.Resize(rTable.Rows.Count - 1).Offset(1)
Me.cboRangeData.RowSource = rTable.Address

[mikerickson]

There may be a more elegant way to do it, but this workaround might suffice.
Dim strWkCrit As String
Dim strCrit As String
Dim rTable As Range
Dim ws As Worksheet

Dim tempBook As Workbook: Rem new line


strCrit = Range("n1")
strWkCrit = Range("o1")


Set ws = Worksheets(strWkCrit)

Rem begin edit - - - -

Set tempBook = ActiveWorkbook
ws.Activate
Set rTable = Range(strCrit)
tempBook.Activate

Rem end edit - - - -

Worksheets(ws).Range(strCrit).Sort _
Key1:=Worksheets(ws).Range(strCrit), _
Order1:=xlAscending, Header:=xlYes, _
Key2:=Worksheets(ws).Range(strCrit)

Set rTable = rTable.Resize(rTable.Rows.Count - 1).Offset(1)
Me.cboRangeData.RowSource = rTable.Address

[brorick]

Thank you once again Mikerickson. I will give it a try.

[brorick]

Mikerickson, this is the same piece of the code that was giving me a problem. I get the Run-time error 9: Subscript out of range. I am not sure what this error means.

Set ws = Worksheets(strWkCrit)

[mikerickson]

It looks like the contents of cell o1 may be mis-spelt.

I just notice that my first post addresses a workbook issue while you are working with sheets. Is your Name scoped at the sheet level or workbook level?

[brorick]

For example cell o1 populates with the name of the worksheet "frmInvoice" which is identicle to the name of the worksheet in my workbook titled frmInvoice.

[brorick]

I think I may have discovered the problem. It appears the name that populates cell "o1" for some unexplained reason has an extra space in front of the name of the worksheet. I am trying to find out why this is happening.

[brorick]

I resolved the issue. Your question caused me to rethink my code and now it is working as it should. Thanks once again. :thumb