run-time error 9

[David Cates]

the following procedure produces a run-time error 9: subscript out of range
on the line r1 = Worksheets("Addresses").Range("A2").CurrentRegion.Select

What is wrong? Using Excel 2007


Public Sub EnterDataInWorksheet()
' Copies data from the user form
' to the next blank row in the worksheet.
Dim Addresses As Worksheet
Dim r As Range, r1 As Range


r1 = Worksheets("Addresses").Range("A2").CurrentRegion
Set r1 = r.Offset(r.Rows.Count, 0)
r1.Cells(1).Value = txtFirstName.Value
r1.Cells(2).Value = txtLastName.Value
r1.Cells(3).Value = txtAddress.Value
r1.Cells(4).Value = txtCity.Value
r1.Cells(5).Value = cmbStates.Value
r1.Cells(6).Value = txtZip.Value
End Sub

 

[VoG]

Try

Set r = Worksheets("Addresses").Range("A2").CurrentRegion

 

[Richard Schollar]

As Peter indicated, when assigning a reference to an object variable (such as a range, a worksheet, a workbook, or a shape) you need to use the Set keyword. When assigning a simple scalar value to a variable (such as a string or an integer) you usse the Let keyword - but because this is the default when assigning values, you can actually omit it, which is why you rarely ever see anyone use it in normal module code.

 

[David Cates]

even with set r = ....., a runtime error is still produced!

 

[VoG]

Do you have a sheet named exactly Addresses without leading or trailing spaces?

 

[Colin Legg]

error 9: subscript out of range
on the line r1 = Worksheets("Addresses").Range("A2").CurrentRegion.Select

What is wrong? Using Excel 2007

even with set r = ....., a runtime error is still produced!

1. Make sure you also removed the Select method from the end of the line as VoG indicated in post #2.
2. Make sure that "Addresses" is a valid worksheet name within the same workbook where the code resides. Especially check for any spaces before or after the name.

Hope that helps...

 

[David Cates]

thanks for the help, it works fine. I had already removed the .select