+ Reply to Thread
Results 1 to 6 of 6

Different ways of using Range: I don't understand the difference

  1. #1
    Registered User
    Join Date
    07-15-2015
    Location
    Amsterdam
    MS-Off Ver
    Office 2010
    Posts
    4

    Different ways of using Range: I don't understand the difference

    Hi everyone,

    I am a relatively new user of Excel/VBA.

    In the past days I ave been working in making as automatic as possible a macro, using a lot of help that I found online.

    However, I am now trying to do something which in my head (read: from previous experience with other programming languages) should be possible, but I cannot make it work.

    The working code is the following:

    Please Login or Register  to view this content.
    NB: localFile, dataSheet, dataRow, scNameCol etc...have been declared previously of the appropriate type and set at the correct values. The code, indeed, works.

    Since I want to make it more re-usable, I want to change the first line with a generic range (let's call it scRange) which will be defined in the code. So, I modified the codee like this:

    Please Login or Register  to view this content.
    This piece of code does not work. I am probably biased from my C++ bakground, since I do not understand why it shouldn't work.

    If someone could explain what is wrong and how to solve it I would be grateful!


    ADDITIONAL:

    the final goal is that scRange should be defined differently based on the content of a cell in a sheet. Something like (I know this is not VBA, but I am just trying to send the message)

    Please Login or Register  to view this content.
    An answer to the first part would be already good, if you happen to know the answer to the second part also, even better

    thanks a lot in advance!

    Konx

  2. #2
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Different ways of using Range: I don't understand the difference

    it can be done like this:
    Please Login or Register  to view this content.
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Different ways of using Range: I don't understand the difference

    Range doesn't expect and object parameter, so you want:
    Please Login or Register  to view this content.
    Then:
    Please Login or Register  to view this content.
    Bear in mind that:
    Please Login or Register  to view this content.
    Is actually:
    Please Login or Register  to view this content.
    And:
    Please Login or Register  to view this content.
    Is
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-15-2015
    Location
    Amsterdam
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Different ways of using Range: I don't understand the difference

    Quote Originally Posted by Kyle123 View Post
    Range doesn't expect and object parameter, so you want:
    Thank you very much! The explanation is really clear and it helped also in clarify other things I am doing that work even if I didn't fully understand why!

    thanks again!

    Konx

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Different ways of using Range: I don't understand the difference

    Glad it helped

  6. #6
    Forum Contributor
    Join Date
    09-26-2014
    Location
    Moscow, Russia
    MS-Off Ver
    MSE 10, MSE 13
    Posts
    179

    Re: Different ways of using Range: I don't understand the difference

    Hello, konx!

    You don't need to refer to a range variable through full path of files and sheets. Once variable value is defined - it's path now is being stored in applications memory. So, just rewrite your part:

    Please Login or Register  to view this content.
    To switch range by cells value, you may use "Select Case" operator, which allows you to make far more than 2 branches.

    If your range shifts are based on linear logic - you may be interested in investigation of "Offset" operator just not to be bothered by thousand switches.

    By the way, your last code part is almost VBAnian =) Just a few changes needed according to syntax:

    Please Login or Register  to view this content.
    By the way, if you need to transport only values - you may use something like this. More effective, higher performance, but scRange must not be wrapped on two ranges as it is in your code.

    Please Login or Register  to view this content.
    Best wishes and have a nice day!

+ 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] cannot understand error with range handling
    By fredderf81 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-12-2013, 03:58 PM
  2. A formula to understand range and apply criteria
    By ChrisE in forum Excel General
    Replies: 4
    Last Post: 09-22-2011, 01:01 AM
  3. Is this the same range written two different ways?
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-20-2011, 05:52 PM
  4. Don't understand error with Range()
    By achinfish in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-28-2007, 09:08 PM
  5. Replies: 1
    Last Post: 09-18-2007, 02:07 PM
  6. different ways to copy a range?
    By botha822 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-07-2006, 07:10 PM
  7. Alternative Ways to Spellcheck a Range
    By Jay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2006, 04:50 PM
  8. [SOLVED] Calculating Date difference in 2 ways
    By Hari in forum Excel General
    Replies: 5
    Last Post: 01-15-2005, 07:06 PM

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