JERRY! OOOOOOOoooooooooooo yeeeeaaaaaaahhhhh! That is wickedly good. Thank
you so much (and not for the first time). You changed my life again.
Regards Brett

"JLatham" wrote:

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.


"Brettjg" wrote:

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