Make the cursor jump from the last cell of column C to the first cell of column L

Hi JA,

Attached is code that steps across a sheet 9 columns at a time starting with Column "C". I don't know what calculation you are doing and then putting in column C, but it would go in the loop where noted.

I also noticed you were new to the board. You want to put any code you post in Code Tags. You can do that by highlighting the code in your posting and then hitting the "#" symbol in the header. It should put a

Please Login or Register  to view this content.

Thanks,

Daniel

Thank you very much, Daniel,

First, let me explain better. From cell A1 to cell A40, I have the numbers 1 to 40. In cell B1 I have the number 6. The macro shown before combines these 40 numbers in groups of 6 numbers each. This process will give 3,838,380 combinations.

So, I need that when the last cell of colunm "C" is reached, it automatically jumps to the first cell of column "L", and then jumps to column "U", column "AD", and so on.

Somehow, when I place the new code together, the generation of number combinations does not function appropriately.

I think there is a conflict with the term " Cells(Application.Rows.Count, 3).End(xlUp).Offset(1, 0).Resize(, p) = vresult " , and also conflict with the Integer "i".

I'm wondering if there is another way to achieve this goal. I really appreciate your help.

Thank you very much,

John

Hi John,

Here is some code that has been modified so it moves moves across the columns as you described. The data set you are generating doesn't fit on one sheet, so I added some code to add additional sheets as necessary to provide for over 3 million cells of data. I'm using Excel 2003, so whatever version you are using might have more columns available, Excel 2003 is limited to 256 columns per sheet.

Anyway, here is the code, you can give it a shot and see if it helps.

Please Login or Register  to view this content.

Thanks,

Daniel

Hi Daniel,

The code you wrote DID SOLVE the problem. Thank you very much.

Unfortunately, it is occurring a problem that I did not expect: excel stops responding when running this macro. I noticed that this problem is avoided if I frequently interrupt the execution of this macro. Although I have tried many times, I could not accomplish a full execution of this macro. If I close excel, then appears an error message:

Run-time error ‘-2147417848 (80010108)’
Method ‘Select’ of object ‘Range’ failed

I have searched this Excel Forum, but I am not familiar with the solutions presented.

I would be very grateful if you could help me again.

John

Hi John,

It sounds like putting a time delay at certain intervals in the code might help. The macro takes a while to run and it might be the computer is overwhelming itself after a certain amount of time. I have never understood why it works, but putting a time delay in after a certain amount of iterations on a long running macro seems to clear up a macros tendency to crash. I am not sure what the exact code is to put in a time delay, but if you know, try that. I'm heading out for about an hour or so and will take a look when I get back and see if the time delay makes a difference.

I'm going to try running it again on my computer and see if it runs complete. I'm using an Acer Aspire with a 2 Ghz processor and 2 GB Ram with Windows 7. My version of Excel is Office 2003, so maybe that makes a difference somehow.

Thanks,

Daniel

Hi John,

Just an update, I ran the macro on my computer and it ran it out all the way to the end without a hitch. I thought it might bog down at the end as the sheet got larger, but it made it. I would say if you are having issues, my best guess is a time delay at certain increments to allow your computer to "take a breath" will do the trick.

Thanks,

Daniel

Hi Daniel,

Yeah... Now it makes sense why it worked while I kept pausing and resuming the macro’s execution. After some search, I found a code for time delay. Then, I made another search to make this time delay be executed every 10,000 rows. The code I “builded” is shown bellow:

Please Login or Register  to view this content.

Now, the macro works just fine. I would like to thank you again for your help. Due to your help this thread was successfully solved.

Thanks,

John

P.S. Note that this run-time error occurred while using excel 2010. I have just installed excel 2000 and there was no such problem (I executed the macro without time delay). I simply do not understand what causes this run-time error in excel 2010.