VBA Range using a variable to set a location

Question

I want to have my program begin a count at a specific location defined by a variable. I know that you can use rn = sh.Cells(9, 1) for example, and the variable rn is now holding the location of cells(9,1). But when I use the variable rn in my count command, I am getting the following error:

Method 'Range' of object' _worksheet' failed

Here's my code:

Option Explicit
Sub Struc_Equa()
Dim n As Integer

End sub
Sub countN(n)

Dim sh As Worksheet
Dim rn As Range
Dim SheNa As String
SheNa = ActiveSheet.Name
Set sh = ThisWorkbook.Sheets(SheNa)

Set rn = sh.Cells(9, 1)

' The command below does not work
'n = sh.Range(rn, sh.Range(rn).End(xlToRight)).Columns.Count

' I am able to do what I want but in a inefficient way (shown below)
'n = sh.Range("A9", sh.Range("A9").End(xlToRight)).Columns.Count
rn.Activate
 MsgBox (n)

End Sub

Why is this error happening?

L42 · Accepted Answer · 2017-10-19 01:51:48Z

1

Try this:

n = sh.Range(rn, rn.End(xlToRight)).Columns.Count

It follows the Range([Cell1],[Cell2]) syntax.
Remember that rn is already a fully referenced range address so you can use it directly as arguments to Range object. More about Range Syntax.

Why does your original code fail?

Because based on this article, in Range(arg) syntax, arg names the range.
And take note that Range Object's default property is Value.
So your code:

sh.Range(rn).End(xlToRight)

is actually evaluating the value of rn and supplies it as arg for the Range(arg) syntax. If the value of that cell is not a valid cell or range address (or name as the article mentioned), it will throw an error. Above actually evaluates like below:

sh.Range("value from rn range object").End(xlToRight)

If you try changing the value of that cell with a valid cell or range name/address, it will work. For example:

With sh
    Set rn = .Cells(9, 1)
    rn.Value2 = "A9"
    n = .Range(rn, .Range(rn).End(xlToRight)).Columns.Count
    MsgBox n
End With

In short:

Range(arg) expects a valid range name or address.
Range([cell1],[cell2]) expects a valid range object reference.

edited Oct 19, 2017 at 1:51

answered Oct 18, 2017 at 21:40

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

LA_MAR2 Over a year ago

This was exactly the issue. Can you explain further what was wrong with the the original code?

L42 Over a year ago

@MaxHollander See my update. I hope it is clearer now. ;)

LA_MAR2 Over a year ago

Do you have much experiance with C++ i have a few questions regarding it ability etc

L42 Over a year ago

@LA_MAR2 No, I am not well versed in C++ but you can post your question here and I'm sure there are a lot of gurus here.

Collectives™ on Stack Overflow

VBA Range using a variable to set a location

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related