DO LOOP in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello there
I have the following code (which works properly) but by the time I extend it
out to run 120 times with 40 IF statements in each it will be MASSIVE! What I
would like to do is use a DO Loops instead, if you could please advise. I
have only included a sample of the code for brevity in this question. The
code is as follows:

Sub Allocate_loans_to_properties()
ASSET_1_LOAN_1:
Range("AS32").ClearContents
If Range("G32") = "1" Then
Range("F32").Copy
Range("AS32").PasteSpecial Paste:=xlPasteValues
GoTo ASSET_1_LOAN_2
ElseIf Range("G33") = "1" Then
Range("F33").Copy
Range("AS32").PasteSpecial Paste:=xlPasteValues
GoTo ASSET_1_LOAN_2

' and so on up to cell G72

End If
ASSET_1_LOAN_2:
Range("AT32").ClearContents
If Not Range("AS32") = Range("F32") And Range("G32") = "1" Then
Range("F32").Copy
Range("AT32").PasteSpecial Paste:=xlPasteValues
GoTo ASSET_1_LOAN_3
ElseIf Not Range("AS32") = Range("F33") And Range("G33") = "1" Then
Range("F33").Copy
Range("AT32").PasteSpecial Paste:=xlPasteValues
GoTo ASSET_1_LOAN_3

' and so on up to cell G72

End If
' then it repeats this 4 more times to allocate up to six loans to ASSET 1
' then it repeats all of that for the next 19 assets!!

Thankyou for your time and help
Regards, Brett
 
Hi

It seems that you want to make your code generic so only one set of code
template is OK. I can see that all your code is in absolute address. So I
think the first step is to try to use some functions, such as offset,
selection XLDOWN to generalize all the cells movement or editing in order to
make the code running whereve the starting point is located, e.g. work in A1,
also B10 or az10. Try to use more selection object as well.

hope this help.

Leung
 
Hi Leung, Thanks for your response, but sorry it's a little over my head. I
don't mind recreating (say) 120 do loops. That will make the code much
shorter (and therefore the file smaller). What I'm really keen to know is how
to write a DO LOOP (perhaps 2) to replace the code I have supplied. From
there I can duplicate it out. I don't expect that using DO LOOPs will make
the macro any faster to run, it's all about cutting down the lines of code.

Regards, Brett
 
Sub Allocate_loans_to_properties()
Dim LC As Integer ' Loop Counter
ASSET_1_LOAN_1:
Range("AS32").ClearContents
For LC = 32 To 72
If Range("G" & LC)="1" Then
... your copy and paste here use Range("F" & LC).Copy
Exit For ' jump out of loop
End If
Next ' end of LC loop

ASSET_1_LOAN_2:
Range("AT32").ClearContents
For LC = 32 To 72
If Not Range("AS32") = Range("F" & LC) _
And Range("G" & LC) = "1" Then
...your copy and paste here use Range("F" & LC).Copy
Exit For ' jump out of this loop
End If
Next ' end of this LC loop
..... code continues on in similar fashion

Hope this helps get you started.
 
JERRY! OOOOOOOoooooooooooo yeeeeaaaaaaahhhhh! That is wickedly good. Thank
you so much (and not for the first time). You changed my life again.
Regards Brett
 
Glad I could help (again).

JLatham

Brettjg said:
JERRY! OOOOOOOoooooooooooo yeeeeaaaaaaahhhhh! That is wickedly good. Thank
you so much (and not for the first time). You changed my life again.
Regards Brett
Click to expand...
 
Back
Top