looking for a way to increase macro performance

**s4driver** · 06-19-2013, 12:23 PM

Currently this portion of my code takes about 15-20 minutes to run. I was wondering if any one had any suggestions on how to increase the speed of this code. Screen updating and display updates would already be set to false and calculations are set to manual. just would like to know if there is a way to replace the IF Else commands with something that will accomplish the same results quicker.

thanks, s4

Set a = wsSAPBL.Range("C5:C" & wsSAPBL.Cells(Rows.Count, 5).End(xlUp).Row)

For Each sdnfix In a
    
    If sdnfix.Offset(, 5) = "" Then
    
    Set sdnupdate = sdnfix.Offset(, 5)

        sdnupdate.Value = WorksheetFunction.VLookup(sdnfix.Offset(, 16), wsLu.Range("B3:D329"), 2, False)
        sdnupdate.Offset(, 1).Value = WorksheetFunction.VLookup(sdnfix.Offset(, 16), wsLu.Range("B3:D329"), 3, False)
    Else
    'do nothing
    End If
Next sdnfix

'Move relevant SAPBLOG data to Current BackLog tab


Set b = wsSAPBL.Range("N5:N" & wsSAPBL.Cells(Rows.Count, 14).End(xlUp).Row)

For Each sdn In b

    If IsNumeric(sdn) And sdn.Offset(, -1) <> 0 Then
    
    Set found = wsCurBlog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    
    If sdn.Offset(, -7) = "" Then 'for when date is blank (include in current backlog)
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = "Not Assigned"
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    
    Else
    
    If sdn.Offset(, -7) > wsCurBlog.Range("D1").Value Then 'for when date is greater than D1 (include in current backlog)
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = sdn.Offset(, -7).Value
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    
    Else
    
    ' Check orders within prior 14 days of current Sunday
    If wsCurBlog.Range("D1").Value - sdn.Offset(, -7) <= 14 And wsCurBlog.Range("D1").Value - sdn.Offset(, -7) >= 0 Then
    
    On Error GoTo not_found4
    include = WorksheetFunction.VLookup(sdn.Offset(, -11), wsBOI.Range("L:P"), 5, False)
    On Error GoTo 0
    
    If include = "Keep" Then
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = sdn.Offset(, -7).Value
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    
    Else
        'No match was found
    End If
    End If
    End If
    End If
    End If

Next sdn

**Norie** · 06-19-2013, 12:43 PM

Do columns C and N have a different no of rows?

If they don't you can probably combine the loops which should cut the time down a bit.

Set rngSuffixs = wsSAPBL.Range("C5:C" & wsSAPBL.Cells(Rows.Count, 5).End(xlUp).Row)

For Each sdnfix In rngSuffixs

      Set sdn = sdnfix.Offset(,11)

      ' code from first loop

      ' code from second loop

Next sdnfix

As well as that you can turn off screen updating, events and automatic calculation.

   With Application
      .Calculation = xlCalculationManual
      .EnableEvents = False
      .ScreenUpdating = False
   End With

   ' the code 

    With Application
      .Calculation = xlCalculationAutomatic
      .EnableEvents = True
      .ScreenUpdating = True
   End With

**s4driver** · 06-19-2013, 01:26 PM

that sounds like a good place to start. however i am struggling combining the two loops. the first loop fills in missing data on the original/source tab. the second loop moves the data based on certian criteria from the source tab to a new tab. when I try to combine them it skips the second loop entirely. do you know of a way to to this without using IF Else?

**Norie** · 06-19-2013, 03:30 PM

Without using what If Else?

I would have thought removing this,

Next sdnfix

'Move relevant SAPBLOG data to Current BackLog tab


Set b = wsSAPBL.Range("N5:N" & wsSAPBL.Cells(Rows.Count, 14).End(xlUp).Row)

For Each sdn In b

changing Next sdn to sdnfix and adding Set sdn = sdnfix.Offset(,11) would have worked.

It might have needed further tweaks but I don't see how an entire section of code would be skipped.

Mind you all I can go on is the code, so perhaps there's something happening (not happening) that I don't know about.

Any chance of some sample data?

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**s4driver** · 06-19-2013, 03:49 PM

yes, I was wondering if there was another command that could replace the If Then Else. I don't have a sample file available due to confidential content. where I'm struggling on combing the loops is I need to look at each row and verify that all the data is present in the original table. if it's not then I would have to perform the "original first" loop, then while on the same row check the criteria to determine what part of the "original second" loop to execute. so in some cases this code needs to execute

If sdnfix.Offset(, 5) = "" Then
    
    Set sdnupdate = sdnfix.Offset(, 5)

        sdnupdate.Value = WorksheetFunction.VLookup(sdnfix.Offset(, 16), wsLu.Range("B3:D329"), 2, False)
        sdnupdate.Offset(, 1).Value = WorksheetFunction.VLookup(sdnfix.Offset(, 16), wsLu.Range("B3:D329"), 3, False)

then execute one of these 3 block Ifs

If IsNumeric(sdn) And sdn.Offset(, -1) <> 0 Then
    
    Set found = wsCurBlog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    
    If sdn.Offset(, -7) = "" Then 'for when date is blank (include in current backlog)
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = "Not Assigned"
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    
    Else
    
    If sdn.Offset(, -7) > wsCurBlog.Range("D1").Value Then 'for when date is greater than D1 (include in current backlog)
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = sdn.Offset(, -7).Value
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    
    Else
    
    ' Check orders within prior 14 days of current Sunday
    If wsCurBlog.Range("D1").Value - sdn.Offset(, -7) <= 14 And wsCurBlog.Range("D1").Value - sdn.Offset(, -7) >= 0 Then
    
    On Error GoTo not_found4
    include = WorksheetFunction.VLookup(sdn.Offset(, -11), wsBOI.Range("L:P"), 5, False)
    On Error GoTo 0
    
    If include = "Keep" Then
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = sdn.Offset(, -7).Value
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    
    Else
        'No match was found
    End If
    End If
    End If
    End If
    End If

I'm not sure how to code it so it know when to do both (if needed) or to skip the "first" step and simply execute the proper block if in the "second" step.

But I agree if there is a way to combine the two loops it should save significant time.

**Norie** · 06-19-2013, 04:49 PM

You seem to have lost, or moved, the End If for the first If.

That's the one from the first loop.

If you have moved it that could explain why a large section of the code is, apparently, being skipped.

**AlphaFrog** · 06-19-2013, 05:27 PM

This is not tested, but perhaps it would give you ideas if it doesn't work(likely).

    Dim Nextrow As Long, r As Long
    Nextrow = wsCurBlog.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    
    For r = 5 To wsSAPBL.Cells(Rows.Count, 5).End(xlUp).Row
        With wsSAPBL.Range("H" & r)
            If .Value = "" Then
                .Value = WorksheetFunction.VLookup(.Offset(, 16), wsLu.Range("B3:D329"), 2, False)
                .Offset(, 1).Value = WorksheetFunction.VLookup(.Offset(, 16), wsLu.Range("B3:D329"), 3, False)
            End If
        End With
        
        'Move relevant SAPBLOG data to Current BackLog tab
        Set sdn = wsSAPBL.Range("N" & r)
        If IsNumeric(sdn) And sdn.Offset(, -1) <> 0 Then
            Nextrow = Nextrow + 1
            With wsCurBlog.Range("A" & Nextrow)
                .Resize(, 16).Value = sdn.Offset(, -11).Resize(, 16).Value
                .Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
            
                Select Case True
                    'for when date is blank (include in current backlog)
                    Case sdn.Offset(, -7) = ""
                        .Offset(, 4).Value = "Not Assigned"
                        
                    ' Check orders within prior 14 days of current Sunday
                    Case wsCurBlog.Range("D1").Value - sdn.Offset(, -7) <= 14 And wsCurBlog.Range("D1").Value - sdn.Offset(, -7) >= 0
                        On Error GoTo not_found4
                        include = WorksheetFunction.VLookup(sdn.Offset(, -11), wsBOI.Range("L:P"), 5, False)
                        On Error GoTo 0
                        .Offset(, 11).Value = sdn.Value
                        
                    'for when date is greater than D1 (include in current backlog)
                    Case sdn.Offset(, -7) > wsCurBlog.Range("D1").Value
                        .Offset(, 11).Value = sdn.Value
                        
                    Case Else: 'Do nothing
                    
                End Select
            End With
        End If
    Next r

**s4driver** · 06-20-2013, 08:56 AM

AlphaFrog -
thanks for the different approach, However even when I modified to get it to work properly it still takes the about the same amount of time to execute. I do like the fact that the code itself is more condensed.

Norie/AlphaFrog-
I appreciate your interests in my post but I have been unable to make the improvements I was hoping to. I am going to keep searching around for another solution. When and if I find a quicker way I will post the changes I made.

**Norie** · 06-20-2013, 09:02 AM

Did you try the other suggestions I made?

**s4driver** · 06-20-2013, 09:18 AM

yes, thanks. I haven't been able to solve how to combine the first loop effectively with each of the 3 scenarios of the 2nd. Still playing with it, I feel it will save the most time since I would only be looping thru each row once instead of twice.

**JosephP** · 06-20-2013, 09:06 AM

have you timed the sections of the code to see where the most time is actually taken?
it's not efficient to do two vlookups of the same data for instance-you can use match to find the position and then refer to that row directly

vMatch = worksheetfunction.match(.Offset(, 16), wsLu.Range("B3:B329"), 0)
               .Value = wsLu.Range("B3:D329").cells(vMatch, 2).value
                .Offset(, 1).Value = wsLu.Range("B3:D329").cells(vMatch, 3).value

rather than

                .Value = WorksheetFunction.VLookup(.Offset(, 16), wsLu.Range("B3:D329"), 2, False)
                .Offset(, 1).Value = WorksheetFunction.VLookup(.Offset(, 16), wsLu.Range("B3:D329"), 3, False)

**s4driver** · 06-20-2013, 09:23 AM

What I originally posted is one of 5 sections that I created individually then combined into one macro. That section of the code takes close to 20 minutes to execute with the data I have. thanks for the vlookup tip I will give that a go in a moment.

**JosephP** · 06-20-2013, 09:29 AM

I reckon you'd probably need to post your workbook(s) to get big improvements-it's probably more a process issue than a code one

**s4driver** · 06-20-2013, 09:40 AM

probably, but I was just looking for some quick suggestions. Combining the vlookup didn't save me much time with this file due to the fact there was less than a handful of cases where it was needed. But I have some other applications where it will come in more handy. thanks again.

**s4driver** · 06-27-2013, 01:48 PM

I figured out a way to decrease the time it takes to execute dramatically. In it's orignal state it took around 25-30 minutes to run, now it takes less than 3 minutes. I was able to successfully merge some of the steps in my original code and elminate the need of using some lookup functions on a chunk of the data. The part that I merged followed directly below the code I originally posted and look like this,

For Each ccell In wsCurBlog.Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
On Error GoTo not_found2
    sapcslook = WorksheetFunction.VLookup(ccell.Offset(, 4), wsML.Range("A3:B" & Cells(Rows.Count, "A").End(xlUp).Row), 2, False)
    On Error GoTo 0
    If ccell <> "" Then
    
    Set found5 = wsCurBlog.Cells(Rows.Count, 17).End(xlUp).Offset(1, 0)
    
    If Not found5 Is Nothing Then
            
        found5.Offset(, 0).Value = ccell.Offset(, 13).Value / ccell.Offset(, 11).Value
        found5.Offset(, 1).Value = ccell.Offset(, 15).Value / ccell.Offset(, 11).Value
        found5.Offset(, 2).Value = sapcslook
        If ccell.Offset(, 4).Value <> "Not Assigned" And ccell.Offset(, 4).Value < Range("D1").Value Then
        found5.Offset(, 3).Value = "Past Schedule"
        Else
        If ccell.Offset(, 4).Value = "Not Assigned" Then
        found5.Offset(, 3).Value = "Unscheduled"
        Else
        found5.Offset(, 3).Value = WorksheetFunction.IfError(Year(ccell.Offset(, 4).Value), "Unscheduled")
        End If
        End If
        Else
    End If
    End If

Next ccell

After merging it with the section from my original post it now looks like this,

Set b = wsSAPBL.Range("N5:N" & wsSAPBL.Cells(Rows.Count, 14).End(xlUp).Row)

For Each sdn In b

    If IsNumeric(sdn) And sdn.Offset(, -1) <> 0 Then
    
    Set found = wsCurBlog.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    
    If sdn.Offset(, -7) = "" Then 'for when date is blank (include in current backlog)
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = "Not Assigned"
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    found.Offset(, 16).Value = -sdn.Offset(, 2).Value / sdn.Value
    found.Offset(, 17).Value = -sdn.Offset(, 4).Value / sdn.Value
    found.Offset(, 18).Value = "#N/A"
    found.Offset(, 19).Value = "Unscheduled"
    
    Else
    
    If sdn.Offset(, -7) > wsCurBlog.Range("D1").Value Then 'for when date is greater than (include in current backlog)
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = sdn.Offset(, -7).Value
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    found.Offset(, 16).Value = -sdn.Offset(, 2).Value / sdn.Value
    found.Offset(, 17).Value = -sdn.Offset(, 4).Value / sdn.Value
    found.Offset(, 18).Value = WorksheetFunction.IfError(WorksheetFunction.VLookup(sdn.Offset(, -7), wsML.Range("A3:B" & Cells(Rows.Count, "A").End(xlUp).Row), 2, True), "#N/A")
    found.Offset(, 19).Value = WorksheetFunction.IfError(Year(sdn.Offset(, -7).Value), "Unscheduled")
    
    Else
    
    ' Check orders within prior 14 days of current Sunday
    If wsCurBlog.Range("D1").Value - sdn.Offset(, -7) <= 14 And wsCurBlog.Range("D1").Value - sdn.Offset(, -7) >= 0 Then
    
    On Error GoTo not_found4
    include = WorksheetFunction.VLookup(sdn.Offset(, -11), wsBOI.Range("L:P"), 5, False)
    On Error GoTo 0
    
    If include = "Keep" Then
    found.Value = sdn.Offset(, -11).Value
    found.Offset(, 1).Value = sdn.Offset(, -10).Value
    found.Offset(, 2).Value = sdn.Offset(, -9).Value
    found.Offset(, 3).Value = sdn.Offset(, -8).Value
    found.Offset(, 4).Value = sdn.Offset(, -7).Value
    found.Offset(, 5).Value = sdn.Offset(, -6).Value
    found.Offset(, 6).Value = sdn.Offset(, -5).Value
    found.Offset(, 7).Value = sdn.Offset(, -4).Value
    found.Offset(, 8).Value = sdn.Offset(, -3).Value
    found.Offset(, 9).Value = sdn.Offset(, -2).Value
    found.Offset(, 10).Value = sdn.Offset(, -1).Value
    found.Offset(, 11).Value = sdn.Value
    found.Offset(, 12).Value = sdn.Offset(, 1).Value
    found.Offset(, 13).Value = -sdn.Offset(, 2).Value - sdn.Offset(, 3).Value - sdn.Offset(, 4).Value
    found.Offset(, 14).Value = -sdn.Offset(, 2).Value
    found.Offset(, 15).Value = -sdn.Offset(, 4).Value
    found.Offset(, 16).Value = -sdn.Offset(, 2).Value / sdn.Value
    found.Offset(, 17).Value = -sdn.Offset(, 4).Value / sdn.Value
    found.Offset(, 18).Value = "#N/A"
    found.Offset(, 19).Value = "Past Schedule"


    
    Else
        'No match was found
    End If
    End If
    End If
    End If
    End If

Next sdn

Doing that I was now only looping through each row once and using fewer time consuming functions. Thanks for pointing that out Norie.

looking for a way to increase macro performance

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Re: looking for a way to increase macro performance

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions