| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DO LOOP in VBA
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DO LOOP in VBA
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 "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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DO LOOP in VBA
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 "Leung" wrote: 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 "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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DO LOOP in VBA
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DO LOOP in VBA
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DO LOOP in VBA
Glad I could help (again).
JLatham "Brettjg" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How would I add a loop for this? | Excel Discussion (Misc queries) | |||
| loop | Excel Discussion (Misc queries) | |||
| help with a loop | Excel Discussion (Misc queries) | |||
| Help with Do...Loop | Excel Discussion (Misc queries) | |||
| Which loop to use | Excel Discussion (Misc queries) | |||
Linear Mode
