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.

[vba]
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

[/vba]

[mikerickson]

There may be a more elegant way to do it, but this workaround might suffice.
[VBA]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[/VBA]

[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.