CODEWITHSUNDEEP
vbasetrange
Ardnic
Ardnic

Reputation: 51

Set range via Range object -> Range object fails?

Can someone help me debug this piece of code?

....    
Dim searchRng as Range

With Sheets("Database")

    Set searchRng = Range("pointer.address:.cells(pointer.End(xlDown).Row,pointer.Column).address") 

End with

I keep getting error 1004: Method 'Range' of object '_Global' failed "pointer" is a previously defined range of one cell (B6). After various debugging attempts, the problem seems to be connected to .address... Thats as far as I've been able to trace it back.

Thanks in advance! Leo

Upvotes: 0

Views: 99

Answers (1)

DMcLaren
DMcLaren

Reputation: 75

The issue here is that the quotation marks are for when you are passing the name of a range to the .Range() object, but you are wanting to pass it the results of calling the .address method. If you put those method calls in quotation marks VBA wont run them and will instead try to interpret what you have in them as the name of the range you are referring to. You need to construct the range name string using these methods and pass the result to the .Range() object.

There are several ways you could do this. This first one separates out the construction of the range name and assigns that to a variable which can then be passed to .Range().

Sub test()

Dim searchRng As Range
Dim CllNameA As String
Dim CllNameB As String
Dim CllRange As String

With Sheets("Database")
    CllNameA = .Range("pointer").Address
    CllNameB = .Range("pointer").End(xlDown).Address


    CllRange = CllNameA & ":" & CllNameB


    Set searchRng = .Range(CllRange)


End With

End Sub

This next subroutine condenses the same methodology into one line. It's still doing the same thing, but as its doing it all at once its slightly more difficult for a human reader to follow.

Sub test2()

Dim searchRng As Range

With Sheets("Database")

    Set searchRng = .Range(.Range("pointer").Address & ":" & .Range("pointer").End(xlDown).Address)

End With

End Sub

There are other ways of achieving the same goal, but I hope this at least sets you on the right path.

Upvotes: 2

Related Questions