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!

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.

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.