can i condense my code?

[yorkshirewhite]

Hi

I've got some code that works, but its impacting on the size of the spreadsheet severely.

I'm wondering i can do a loop which executes the same code but just increases the row column by 1.

So for example ive got the following code:

Please Login or Register  to view this content.

I really need this code to loop increasing by one number on each of the rows, at the moment ive just done the below, but as this happens from rows 11 through to 83...as you can imagine its effecting the spreadsheet size. Any help would be much appreciated

Please Login or Register  to view this content.

[shg]

Maybe like this:

Please Login or Register  to view this content.

However, I'd look at making other changes to make the formula less complicated.

[Richard Buttrey]

Hi,

You could set a simple loop variable to represent the row number, and use the variable in the loop - untested but something like

Please Login or Register  to view this content.

However I'd be inclined to hold that Vlookup formula in a master cell, say C1, and give it a name. Then have the macro just copy it and paste it to C11:C83 in one hit. So suppose the C1 cell is named "MyForm" use a single line of macro code something like:

Please Login or Register  to view this content.

The problem with your existing code is that you are jumping backwards and forwards between the Visual Basic Environment (VBE) and Excel itself. There is a large time overhead associated with this. Far Better to just do a single Copy in one hit. You should find this speeds up your code and is far tidier to administer.

HTH