+ Reply to Thread
Results 1 to 5 of 5

VBA - Compare a value to an offset value?

  1. #1
    Registered User
    Join Date
    01-29-2007
    Posts
    40

    VBA - Compare a value to an offset value?

    Hi all,

    I've got a macro that inserts a random integer (between a ComboBox value and 29) into a cell every third column. This is wrapped up in a Next/For sequence (which I've not shown here) to repeat this as many times as I need.

    I want to add some code that makes sure that the random number generated doesn't match the previous one. I've been trying this:

    Please Login or Register  to view this content.
    I don't think I've Offset part correct.

    Any help muchly appreciated!
    Last edited by creationracing; 04-26-2019 at 07:58 AM. Reason: spelling

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,232

    Re: VBA - Compare a value to an offset value?

    You have Value in the wrong place. Also your code assumes a With statement but you didn't show one. You should always show all your code when asking a question, even if you don't think it's relevant.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-29-2007
    Posts
    40

    Re: VBA - Compare a value to an offset value?

    Hi Jeff,

    Thanks very much for your advice. See full code below. As it's still giving me an error I guess I'm missing a With somewhere:

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,232

    Re: VBA - Compare a value to an offset value?

    When you use .Offset, the leading dot tells the compiler that you have a preceding With statement to specify what is supposed to come before the dot. In your case it looks like what you intend is

    ActiveCell.Offset

    If you make that change I think your code will work.

    However, it is generally not necessary and not a good programming practice in Excel to use ActiveCell to navigate a range. This is for two reasons:

    1. When you change ActiveCell, the user sees it changing. This can be disconcerting to a user and takes a lot more execution time.
    2. Your code may not always have control over which cell is active.

    Here is a rewrite of your code to eliminate ActiveCell. I also cleaned up the indentation.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-29-2007
    Posts
    40

    Re: VBA - Compare a value to an offset value?

    Thanks Jeff, everything working now. Well, once I changed my deliberate error...

    (should have been 0, -3)

    Thanks again - every day is a school 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. Select offset variable offset cells
    By papasmurfuo9 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2014, 06:12 AM
  2. Replies: 3
    Last Post: 06-01-2014, 10:26 AM
  3. [SOLVED] Compare a cell value to a range of values, copy offset cell
    By mtt23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-11-2012, 09:08 AM
  4. function similar to offset to offset the offset
    By Jerseynjphillypa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-03-2012, 03:07 PM
  5. Compare offset to a range variable
    By GaidenFocus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-10-2012, 04:17 PM
  6. Reformatting - insert formula with offset, then offset to next cell
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2011, 05:41 AM
  7. [SOLVED] How to compare a cell value to an offset cell value?
    By Father Guido in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-20-2005, 01:06 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