VBA Macro Performance Problem

A

ashwinghanta

Board Regular
Joined
Dec 6, 2011
Messages
118
I fill out Random values in two sheets (Testfall-Input_Vorschlag) and (Testfall-Input_Antrag) out of another sheet (ADMIN_ARB11).

I have 371 rows in sheet (Testfall-Input_Vorschlag) & I have 488 rows in sheet (Testfall-Input_Antrag)

I have 859 columns in sheet (ADMIN_ARB11).

I pick a random value from each of the 1st 371 columns(from ADMIN_ARB11) and I put them in the 371 rows in sheet (Testfall-Input_Vorschlag) and then I pick a random value from each of the next 488 columns(from ADMIN_ARB11) and put them in 488 rows in sheet (Testfall-Input_Antrag). To achieve this I have formulated a code.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">
Code: 
[COLOR=#101094]Sub[/COLOR][COLOR=#303336] Random_Bef[/COLOR][COLOR=#303336]ü[/COLOR][COLOR=#303336]llung_Vorschlag_ARB11[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] sh1 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] sh2 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] LB [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] UB [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] sh1 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Vorschlag"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] sh2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"ADMIN_ARB11"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]


Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] j [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]7[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]300[/COLOR][COLOR=#303336]
        LB [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"ARB11"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"ARB13"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"FVB1"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"FVB1E"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"FVB4"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"FVB4E"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
            sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"_Schicht 1"[/COLOR][COLOR=#303336]
            sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"TPL maximale Eingaben"[/COLOR][COLOR=#303336]
            [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] j [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]7[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
                sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]6[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]
            [/COLOR][COLOR=#101094]Else[/COLOR][COLOR=#303336]
                sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]6[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]6[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]
            [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]
            sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]5[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"TF "[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]6[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
            sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]7[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"Test_GE"[/COLOR][COLOR=#303336]
            sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]8[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"x"[/COLOR][COLOR=#303336]


            [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]11[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]382[/COLOR][COLOR=#303336]
            UB [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] sh2[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]10[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlUp[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Row [/COLOR][COLOR=#858C93]'i - 10 controls column in Admin start at col 1.[/COLOR][COLOR=#303336]

            sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] sh2[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Int[/COLOR][COLOR=#303336](([/COLOR][COLOR=#303336]UB [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#303336] LB [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]*[/COLOR][COLOR=#303336] Rnd [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] LB[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]10[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

            [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]

        [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]



    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] vbNullString [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Exit[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Sub[/COLOR]
Code: 
[COLOR=#101094]Sub[/COLOR][COLOR=#303336] Random_Bef[/COLOR][COLOR=#303336]ü[/COLOR][COLOR=#303336]llung_Antrag_ARB11[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] sh1 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] sh2 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] LB [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] UB [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] wb [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet

[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] wb [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ThisWorkbook
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] ws [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] wb[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Vorschlag"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] sh1 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Antrag"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] sh2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"ADMIN_ARB11"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]


Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'Testfallinfo in Testfall-Input_Antrag kopieren[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] j [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]7[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]300[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Vorschlag"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"ARB11"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Vorschlag"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"ARB13"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Vorschlag"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"FVB1"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Vorschlag"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"FVB1E"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Vorschlag"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"FVB4"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Or[/COLOR][COLOR=#303336] Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Testfall-Input_Vorschlag"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"FVB4E"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
    Union[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]3[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]4[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]5[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]6[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]7[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] ws[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]8[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#303336]Copy
    sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"IV1"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlToLeft[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Offset[/COLOR][COLOR=#303336](,[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]PasteSpecial xlValues
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]



        LB [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"ARB11"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
            [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]13[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]501[/COLOR][COLOR=#303336]
                UB [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] sh2[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]364[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlUp[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Row [/COLOR][COLOR=#858C93]'i - 10 controls column in Admin start at col 1.[/COLOR][COLOR=#303336]
                sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] sh2[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Int[/COLOR][COLOR=#303336](([/COLOR][COLOR=#303336]UB [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#303336] LB [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]*[/COLOR][COLOR=#303336] Rnd [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] LB[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]364[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]


            [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] sh1[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] j[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] vbNullString [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Exit[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] j
Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Sub[/COLOR]

It works as expected but it takes 5 min to run the code. How can I optimize this?

</code>
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
You did the Application.ScreenUpdating = False. I would definitely also include Application.Calculation = xlCalculationManual at the beginning of the execution, and reset it at the end. This is before trying to optimize the rest of the algorithm.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA to Copy Value of Cell in a Do Until Loop
Replies
2
Views
374
JarrydBarnard
J
S
  • Solved
Copy only the data shown with adding a blank row between all copied rows
Replies
4
Views
237
sofas
S
S
  • Solved
Apply formula in first 25 visible cells
2
Replies
13
Views
516
Siddhu11011
S
S
  • Question
Copy specific columns to another closed file
Replies
1
Views
224
sofas
S
J
  • Question
VBA Help: Macro finds text string, but need it to find whole words ONLY
Replies
5
Views
366
DanteAmor
DanteAmor

Forum statistics

Threads
1,203,174
Messages
6,053,925
Members
444,694
Latest member
JacquiDaly

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top