How do I Store a Range?

[danielexcelvba]

Sorry, this is probably very simple but I can't figure out how to store an actual range as a variable rather than the value of the desired range. My code is:

Please Login or Register  to view this content.

I would like my searcher variable to store the range("A80"), but instead it is storing the string insde "A80". This gives me a "Range of Object_Global Failed" error. How do I resolve this? Thanks!

[shg]

How about ...

Please Login or Register  to view this content.

[Stormin']

Hi,

Here you have enclosed searcher in quotes, telling VBA that it is text and not a variable:

Please Login or Register  to view this content.

Try the following:

Please Login or Register  to view this content.

[Sc0tt1e]

.Misunderstood what you were trying to do

[danielexcelvba]

Stormin', that gives me a "mismatch" error (I'm guessing because it expects "searcher" to be a range not a string?)
shg the rfind value is storing "nothing", giving me an error in the "Range("B2")" line

Sc0tt1e I am trying to store the last cell in column "A" of my table as a range variable (my table is dynamic), so that I can store column "A" as a range.

[shg]

shg the rfind value is storing "nothing"

That means the value was not found.

Please Login or Register  to view this content.

[danielexcelvba]

Yes that works shg sorry I had it sorted incorrectly. Still doesn't store it as a range but now I don't need to!

[thatandyward]

you are correctly setting the variable Searcher to the range, but once you set a variable to a range object, referencing that variable is equivalent to referencing the range directly and by default, if the range is a single cell, then excel will return the cell value

Sub TestRng()

Dim Searcher As Range

'set value of cell A1
Sheet1.Cells(1, 1).Formula = "=10+10"

Debug.Print Sheet1.Cells(1, 1) 'output => 20
Debug.Print Sheet1.Cells(1, 1).Value 'output => 20
Debug.Print Sheet1.Cells(1, 1).Formula 'output => =10+10


'assign A1 to Searcher variable
Set Searcher = Sheet1.Cells(1, 1)

Debug.Print Searcher 'output => 20
Debug.Print Searcher.Value 'output => 20
Debug.Print Searcher.Formula 'output => =10+10
Debug.Print Searcher.Address 'output => $A$1

'resize to 2 rows
Set Searcher = Searcher.Resize(2)

Debug.Print Searcher 'error 13 type mismatch
Debug.Print Searcher.Value 'error 13 type mismatch
Debug.Print Searcher.Address 'output => $A$1:$A$2

End Sub

when the variable references a range object of more than a single cell then just referencing the variable will cause a type 13 mismatch error as excel can't return a value from multiple cells.

you can use Searcher.Address to return the address of the range so in this instance you would replace Range("A1:Searcher") with Range("A1:" & Searcher.address), where you're effectively concatenating the text string "A1:A80"; however a better approach in this instance would be to set Searcher to the entire range, rather than just the last row, Set Searcher = Cells(1, 1).Resize(last_row) and as Stormin mentions above, you can then use that range object directly with .find to set your Finder variable

[danielexcelvba]

That makes a lot of sense thanks! I didn't know about .address

[Stormin']

.Misunderstood what you were trying to do

Ha, oops