Excel Slow performance

[snb]

For jj = 2 To UBound(sp)
  If sn(j, 3) = sp(jj, 1) Then Exit For
Next

If the value in column 3 in the row j of array sn is identical to the value in column 1 of row jj in Array sp, then stop the loop.
Als de waarde van kolom 3 in rij j van Array sn gelijk is aan de waarde van kolom in rij jj van Array sp, stop dan het zoeken.

[SamT]

If the value in column 3 in the row j of array sn is identical to the value in column 1 of row jj in Array sp, then stop the loop.

Stop which loop?

Sub t()
For i = 1 To 3
For J = 1 To 100000
If J = 1 Then Exit For
MsgBox "Inner Loop # " & J
Next
MsgBox "Outer Loop # " & i
Next
End Sub

[snb]

For jj = 2 To UBound(sp) 
    If sn(j, 3) = sp(jj, 1) Then Exit For 
Next

I see only one loop.

If you know a method in VBA which exits an outer loop: please reveal/unveil your secret....

[SamT]

see only one loop.

Me too. What does that loop do?

If you know a method in VBA which exits an outer loop: please reveal/unveil your secret

GoTo

Sub t() 
    For i = 1 To 3 
        For J = 1 To 100000 
            If J = 1 Then GoTo AfterEndOfOuterLoop            
        Next 
        MsgBox "Outer Loop # " & i 
    Next 
AfterEndOfOuterLoop:
End Sub

Counter > For To

Sub t() 
    For i = 1 To 3 
        For J = 1 To 100000 
            If J = 1 Then 
i = 4
Exit For  
End If       
        Next 
    Next 
End Sub

[Svmaxcel]

Hi,
I removed the values from grey areas and ran code again.
However it was not completed.
VB only got values for 2 rows only, please check

[Svmaxcel]

Attaching 2 images for sample

[Svmaxcel]

One more thing to add.roster sheet which is created has repeated names and dates. So I would not like names to be repeated for same dates

[Paul_Hossler]

In my approach, it's only update rows in the selection that get updated.
Since you have so much data and it seems to be added daily, there did not seem to be a reason to re-compute thousands of lines of data. Just process the new data


I selected A2:A51 and ran the macro and all 2:51 gray areas were filled in

If you select A17 and run the macro, only row 17 is processes


If you really want to re-process all the data, that's easy to change

Deleted dups in roster also

[Sandler]

Windows System Settings.jpg

At work, I use these settings on my Windows computer to speed things up. It's not exactly Excel help, but it will help the computer process things more efficiently.

[Svmaxcel]

Got it Buddy, it was cool and very informative.
I selected around 20000 rows and it calculated that in 30 sec.
I am OK with that.
In case I have to change any column name in Data sheet or rows in Mapping, it needs to be changed in VB also.
Can you tell me the line number for the same

[Svmaxcel]

Sub M_snb() 
  sn = Sheets("Data").Cells(1).CurrentRegion 
  sp = Sheets("Mapping").UsedRange 
  sf= split("dddd_'mmm-yy_\Wk ww_\WB dd-mm-yyyy_\WE dd-mm-yyyy","_")
 
  For j = 2 To UBound(sn) 
    For jj = 2 To UBound(sp) 
      If sn(j, 3) = sp(jj, 1) Then Exit for
    next
    sn(j, 17) = sp(jj, 2) 
    sn(j, 18) = sp(jj, 4) 
    sn(j, 19) = sp(jj, 5) 
         
    For jj = 2 To UBound(sp) 
      If sn(j, 1) = sp(jj, 16) Then exit for
    next
    sn(j, 20) = sp(jj, 20) 
    sn(j, 21) = sp(jj, 21) 
         
    w_00 = sn(j, 2) - Weekday(sn(j, 2)) 
    For jj = 22 To 28 
      If jj < 27 Then sn(j, jj - 10) = Format(Choose(jj - 21, sn(j, 2), sn(j, 2), sn(j, 2), w_00 + 1, w_00 + 7), sf(jj - 21)) 
      sn(j, jj) = Choose(jj - 21, sn(j, 5) * sn(j, 4), sn(j, 6) * sn(j, 4), sn(j, 7) * sn(j, 4), sn(j, 8) * sn(j, 4), sn(j, 9) / 600, sn(j, 10) / 60, sn(j, 11) / 60) 
    Next 
  Next 
     
  Sheets("Data").Cells(1).CurrentRegion.Offset(19) = sn 
End Sub

The code didn't get through properly, I copied the code and got error variable or block variable not found.
While stepping it has a error on sn(j, 17) = sp(jj, 2)

[Svmaxcel]

Reports which I would be sending would be very confidential.
I wanted a (CONFIDENTIAL) Stamp with high transparency and little Slanted to be in between the report.
How can that be done.
I mean on several documents we have seen things like Confidential or Destroy after use.

[snb]

Why working with VBA when you haven't got a clue ?

[Svmaxcel]

You are Brain Child using your programming language skills, I am learning VBA and keep on learning by looking at codes.
I cannot be as smart as you in snb,
I removed Option Explicit

[snb]

Taking a course in the fundamentals of VBA would be very helpful.
VBA is a language in which we can't communicate if you are unfamiliar with the fundamentals.
Language skill are not hereditary but have to be acquired (at least I had to by studying books for instance, not by reading code; that comes much later)

[Svmaxcel]

Thanks for this.
I have already started the course and I am now on Do loop.

[snb]

[Svmaxcel]

Thanks a lot to Paul, SNB and Vbaexpress team.
The way you are helping others solve their problem in excel and climb up VBA ladder is appreciated.
Paul/Snb can you tell me which would be a better idea to go with I mean normal loop or arrays loop for the file attached earlier

[snb]

Apparently you have an empty column (column Q) in your file
You'd better remove that empty column.

You only have to adapt

Sub M_snb() 
    sn = Sheets("Data").Cells(1).CurrentRegion.resize(,28)

[Svmaxcel]

I got error Subscript out of range