Select method of Range class failed. A range address is found by msgBox, but won't ".Select"

Question

I am working with named ranges (mostly in Sheet 2) and am in the process of creating a summary in a sheet named "Signups". I am mostly cutting and pasting code that has worked in other parts of the program. For some reason VBA will not Select a range in Sheet 2.

In the code below, "SignupPairs" is a range in sheet "Signups" and "NewMem" is a range in "Sheet2".

''''''''''
MsgBox "SignupPairs 2,1 " & Range("SignupPairs")(2, 1)
Range("SignupPairs")(2, 1).Select
MsgBox "NewMem 1,1 " & Range("NewMem")(1, 1)
'Sheets("Sheet2").Range("NewMem")(1, 1).Select  'Doesn't work.
Range("NewMem")(1, 1).Select
''''''''''

The first four lines have been added for debugging purposes. The last line is the one that brought me to a screeching halt. Line four was an attempt to be specific about the sheet I wanted to use; it didn't work. MsgBox correctly reports the contents of cells in "SignupPairs" and "NewMem" but I can't select NewMem(1,1) in order to do a "Range(Selection, Selection.End(xlDown)).Rows.Count.

For bonus points: Does anyone have a link, or a reference, for a good primer or manual on VBA so I can educate myself and not have to ask these simple questions? VBA for Dummies only got me into this mess.

P.S. Clicking "F1" on "Select" in VBA Editor sends me to a page on Select Case which seems like a dead end.

Read this stackoverflow.com/questions/10714251/… You can't select a cell on a non-active sheet so you have to either activate the sheet first or use application.goto. — SJR
Since these are named ranges, you can also get their data directly to an array in vba, like 'ThisWorkbook.Names("SignupPairs)..RefersToRange.Value` — jessi
I created an answer from my comment. I hope that it helps the op move forward with this project. I appreciate the encouragement @MathieuGuindon — jessi

jessi jessi · Accepted Answer · 2019-04-24T20:09:38

Since you are new to VBA, I highly recommend always writing down what you intend to do as comments in your code. This will help helpers and future you.

I am not sure what you want to do with the data in your summary sheet. I recommend that you create a reference to your summary sheet and then you can use arrays from your named ranges.

Here's a way that you can get both Sign Up Pairs and New Mem into arrays and then msg box out what you were looking at (but I assume that you want to match them or do something else)

Sub doSomethingWithSignups()
    Dim ws As Worksheet, signUps As Variant, newMems As Variant
    ' these arrays will have whatever data you have identified in the Named Ranges
    signUps = ThisWorkbook.Names("SignupPairs").RefersToRange.Value2
    newMems = ThisWorkbook.Names("NewMems").RefersToRange.Value2

    ' you can do anything with these arrays now
    MsgBox("SignupPairs 2,1 " & signUps(2, 1))
    MsgBox("NewMem 1,1 " & newMems(1, 1))
End Sub

Select method of Range class failed. A range address is found by msgBox, but won't ".Select"

1 Answers