Excel VBA loop (1 Viewer)

Nomadscot

Registered User.
Local time
Today, 09:23
Joined
Apr 9, 2014
Messages
26
I need to write 40 lines of code in which only two cell references change in each line. Can I do a 'For Next Loop' for this instead, and if so how would this be constructed?

The code I have is:

With ActiveSheet
.Range("A181").Formula = "=IF($AA$2<2,"""",A180+2)"
.Range("A182").Formula = "=IF($AA$2<2,"""",A181+2)"
.Range("A183").Formula = "=IF($AA$2<2,"""",A182+2)"
.Range("A184").Formula = "=IF($AA$2<2,"""",A183+2)"
.Range("A185").Formula = "=IF($AA$2<2,"""",A184+2)"
etc....
End With

Any assistance on this would be greatlyappreciated.

Many thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:23
Joined
Sep 21, 2011
Messages
14,050
Code:
Sub Enter_Formula()
Dim iloop As Integer
Dim strFormula As String
    For iloop = 181 To 221
        strFormula = "=IF($AA$2<2,""""," & "A" & iloop - 1 & "+2)"
        Range("A" & iloop).Formula = strFormula
    Next
End Sub

HTH
 

Nomadscot

Registered User.
Local time
Today, 09:23
Joined
Apr 9, 2014
Messages
26
Thanks so much, Gasman - works perfectly, and I even managed to understand it (eventually) and adapt it to the next column, with a different formula structure!

Only 8 more columns to go....

:)

Many thanks again, and a 'Thank You' duly awarded.
 

namliam

The Mailman - AWF VIP
Local time
Today, 08:23
Joined
Aug 11, 2003
Messages
11,696
Why would you write functions into each and every row?
Why not only create them in the top row and copy them down (ab)using the $ :)

Another is your function isnt quite logical, returning either a string "" or a number + 2 ??
 

Nomadscot

Registered User.
Local time
Today, 09:23
Joined
Apr 9, 2014
Messages
26
Good morning namliam,

Thanks for your comments.

I think the answer to your first point, is that these lines are written in VBA rather than as formulae on a worksheet.

The reason this is being run as a macro is that I previously created a form in a workbook - a 'master' - with a macro to copy that sheet as a new sheet whenever needed. However Excel took forever and a day to copy the master, and the time taken seemed to get longer with every form produced, until it more or less ground to a halt.

My solution was (is), not to copy a previously constucted 'master' form, but rather to 'Insert new worksheet', and build the form from scratch each time by VBA.

This has speeded up the process dramatically - I now get the new form more or less instantaneously despite running a long macro each time. Excel seems to be much better at Inserting a new sheet than at copying an existing one.

On your second point,, the string "" is of course to ensure that the cell is blank if there is no value to be displayed, while the cell+2 is because these lines are part of a Vlookup range that I had previously copied and pasted on the 'master' form as previously described.

When changing over to building the form each time by macro, I used the formulae from the existing master sheet to include in the macro for building the new form. You have made me realise that I could probably have done it better, but this method was the easiest and quickest for me, given the limitations on my Excel skills.

Again, thanks very much for your interest and your comments.
 
Last edited:

Nomadscot

Registered User.
Local time
Today, 09:23
Joined
Apr 9, 2014
Messages
26
By the way, namliam, it is perhaps appropriate to mention that the '+2' is a result of referring to a previously defined list of items into two separate lists, with every alternate row going to one of two separate new lists, using 'copy and paste' to minimise the work involved.

Did I explain that properly?

:)
 

namliam

The Mailman - AWF VIP
Local time
Today, 08:23
Joined
Aug 11, 2003
Messages
11,696
I dont mind much of anything you do, just seems like a waste to create 100 formula's vs creating 1 and copying it down....
 

Nomadscot

Registered User.
Local time
Today, 09:23
Joined
Apr 9, 2014
Messages
26
'Morning, :)

And when I was using formulae in the spreadsheet that's what I did.

And now that I'm building the spreadsheet with macros rather then directly on the sheet, this query was to find out how to do the same thing in VBA rather than having to type hundreds of lines of code.

Anyway, I muddle along old chap, not claiming to be an expert on VBA, but merely an enthusiastic enthusiast who occasionally has to rely on people like yourself to help me through the tricky bits.

And long may you all continue to do so - my thanks go out to all the members of the forum who kindly contribute their expertise and experience.

:)
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:23
Joined
Sep 21, 2011
Messages
14,050
I'm all for learning new stuff, that is pretty much how I learnt what little Excel I know, out of need to make something easier, or prevent repetitive tasks by hand.

However if the formulae are remaining the same, i would be thinking of constructing a master template woorkbook/sheet and saving that, then opening and saving as whatever each time you will reinput the data.
 

Nomadscot

Registered User.
Local time
Today, 09:23
Joined
Apr 9, 2014
Messages
26
Hi again, Gasman,

That was my original method, however as I explained above, it took ages to copy the master template - I mean really ages.

However by inserting a new sheet and then constructing the form with macro(s) on the newly inserted sheet it is ready more or less instantly.

This is the case on three separate computers I've tried it on.

It appears that Excel is much better at inserting a new worksheet using a macro, than at copying an existing one also by using a macro. This becomes even more the case when the sheet to be copied is loaded. :)

Fun & games, eh? Never stop learning.

Thanks again for your comments and assistance.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:23
Joined
Sep 21, 2011
Messages
14,050
I think you misunderstood me.

I would not copy the sheet. The sheet/woorkbook would be created once and saved empty except for the formulae and any static data (data that does not change, like lookup tables etc)

Then I would open the template file, save it as whatever you want and then add the data that the formula works on.

One benefit of this is the ease of changing the template, as opposed to amending that vba code to handle a few extra cells/rows/columns?

To each their own. :)
 

Users who are viewing this thread

Top Bottom