CODEWITHSUNDEEP
vbaexcel
LA_MAR2
LA_MAR2

Reputation: 31

VBA Range using a variable to set a location

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?

Upvotes: 2

Views: 938

Answers (1)

L42
L42

Reputation: 19737

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:

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

Upvotes: 1

Related Questions