• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Double Loop

Hello,

I have 2 sheets. One is all data and another one is Daily Target.
Everyday, i need to generate 5 sample record per office to store in Daily Target.
After that, i need to mark those record as Picked in sheet All Data. Next day, i need to generate another 5 records which its status is not Picked.
I tried with below code, but seems it spend much time until not response.
Can you help to recommend what should i do to get it done faster and correctly?

>>> use code - tags <<<
Please, You should reread instructions - how to use code - tags.

Code:
Code:
Sub generate()
Set ws_all = Worksheets("All Data")
Set ws_Tag = Worksheets("Daily Target")
Range("B4:D100000").ClearContents

For i = 2 To 30580

    For j = 4 To 748
    n = Application.WorksheetFunction.CountA(Range("D:D")) + 3
If ws_all.Range("A" & i).Value = ws_Tag.Range("A" & j).Value And ws_all.Range("V" & i).Value <> "Picked" Then
ws_Tag.Range("B" & n & ":D" & n).Value = ws_all.Range("C" & i & ":E" & i).Value
Exit sub
End If

    Next j
Next i
End Sub
 

Attachments

  • Testing 2.xlsb
    147.6 KB · Views: 1
Last edited by a moderator:
The attached has a macro generate2 which updates the Daily Target sheet, including column A; all the names on the All Data sheet are examined, but only those with at least one row to copy over will appear on the Daily Target sheet, and only as many rows as needed up to 5.
The All Data Status column is updated by the macro.
The macro has been run once.
It takes about 20 seconds on my machine.

There is also the macro generate modified, but I wouldn't recommend using it. It was too slow and I didn't test it fully.
I got similar results using Power Query which ran in less than 5 seconds but it wasn't easy to update the All Data sheet Status column.
 

Attachments

  • Chandoo45654Testing 2.xlsb
    308.7 KB · Views: 6
The attached has a macro generate2 which updates the Daily Target sheet, including column A; all the names on the All Data sheet are examined, but only those with at least one row to copy over will appear on the Daily Target sheet, and only as many rows as needed up to 5.
The All Data Status column is updated by the macro.
The macro has been run once.
It takes about 20 seconds on my machine.

There is also the macro generate modified, but I wouldn't recommend using it. It was too slow and I didn't test it fully.
I got similar results using Power Query which ran in less than 5 seconds but it wasn't easy to update the All Data sheet Status column.
Thanks very much for your kind support. I'll check to understand more and will reach you again if any concern.
 
Back
Top