+ Reply to Thread
Results 1 to 10 of 10

How do I Store a Range?

  1. #1
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    How do I Store a Range?

    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!

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How do I Store a Range?

    How about ...

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: How do I Store a Range?

    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.

  4. #4
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: How do I Store a Range?

    .Misunderstood what you were trying to do

  5. #5
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: How do I Store a Range?

    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.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How do I Store a Range?

    shg the rfind value is storing "nothing"
    That means the value was not found.

    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: How do I Store a Range?

    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!

  8. #8
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: How do I Store a Range?

    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

  9. #9
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: How do I Store a Range?

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

  10. #10
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: How do I Store a Range?

    Quote Originally Posted by Sc0tt1e View Post
    .Misunderstood what you were trying to do
    Ha, oops

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to store range of cells as text!
    By Keibri in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-02-2016, 06:18 AM
  2. [SOLVED] Formula to create store by store inventory transfers
    By DamianTaylor in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-08-2016, 08:37 AM
  3. store all the values of cells which are in range
    By starlev in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-23-2015, 05:33 PM
  4. [SOLVED] Store consecutive cells range
    By fgq in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2013, 09:51 AM
  5. [SOLVED] Store range into array?
    By matchooo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-03-2013, 06:28 PM
  6. Trying to store a range into a cell
    By Noivilbo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2011, 12:47 PM
  7. Referring to range and store values
    By Ranjani in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-27-2009, 05:01 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1