Vlookup formula vs. Index, Match ??

dmcnab
3StarLounger
Posts: 200
Joined: 24 Aug 2011, 13:13

Re: Vlookup formula vs. Index, Match ??

Post by dmcnab »

OK..it is quite big (10MB) so I will remove non-essential sheets etc and post part (to stay within 256kb) of the relevant sheets here...thanks.

dmcnab
3StarLounger
Posts: 200
Joined: 24 Aug 2011, 13:13

Re: Vlookup formula vs. Index, Match ??

Post by dmcnab »

Back again...hi Hans...here is a reduced workbook sample that shows that the code to replace zz with "" doesn't seem to do it....you can see multiple examples....
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Vlookup formula vs. Index, Match ??

Post by HansV »

The letters "zz" don't occur in strVal. They occur in strStaff instead. So instead of

Code: Select all

            strVal = Replace(strVal, "zz", "")
you should use

Code: Select all

            strStaff = Replace(strStaff, "zz", "")
Best wishes,
Hans

dmcnab
3StarLounger
Posts: 200
Joined: 24 Aug 2011, 13:13

Re: Vlookup formula vs. Index, Match ??

Post by dmcnab »

Thank you Hans.....I'm embarrassed that I couldn't figure that out..it now seems so obvious.....I know that I have said that this code takes a very long time to run....it sometimes seems as if it has stopped running, and in any event, it is hard to figure out what kind of progress its making...could you help me make/install a progress bar that works on the Location worksheet when the Fill_Location code is called (and running)? Do you think it is worth adding to the worksheet? The reason I ask is b/c some of my internet research on this suggests that the task of updating the progress bar could slow down the macro even more...what do you think? Thanks.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Vlookup formula vs. Index, Match ??

Post by HansV »

In the version below, information about the progress of the macro is displayed in Excel's status bar.

Code: Select all

Sub Fill_Location()
    Const lngDatC = 2 ' column with dates (B) on Schedule sheet
    Const lngStaffR = 14 ' row with staff names on Schedule sheet
    Const lngStaffC = 6 ' first staff name column (F) on Schedule sheet
    Const lngStaffLC = 55 'last column with staff names on Schedule sheet
    Const lngDatR = 6 ' date row on Location sheet
    Const lngCodeC = 7 ' column with sites on Location sheet
    Const lngFullCodeC = 8 ' column with sites+AM/PM on Location sheet
    Const lngAMC = 9 ' column with AM/PM on Location sheet
    Dim wshS As Worksheet ' Schedule sheet
    Dim wshL As Worksheet ' Location sheet
    Dim lngSR As Long ' Schedule row
    Dim lngLastSR As Long
    Dim lngSC As Long ' Schedule column
    Dim lngLastSC As Long
    Dim lngLR As Long ' Location row
    Dim lngLastLR As Long
    Dim lngLC As Long ' Location column
    Dim lngLastLC As Long
    Dim rngFound As Range
    Dim rngLoc As Range
    Dim strVal As String
    Dim strStaff As String
    Application.ScreenUpdating = False
    Set wshS = Worksheets("Schedule")
    lngLastSR = wshS.Cells(wshS.Rows.Count, lngDatC).End(xlUp).row
    lngLastSC = lngStaffLC
    Set wshL = Worksheets("Location")
    lngLastLR = wshL.Cells(wshL.Rows.Count, lngAMC).End(xlUp).row
    lngLastLC = wshL.Cells(lngDatR, wshL.Columns.Count).End(xlToLeft).Column
    ' Clear location cells
    With wshL.Range(wshL.Cells(lngDatR + 1, lngAMC + 1), wshL.Cells(lngLastLR, lngLastLC))
        .ClearContents
        .Interior.ColorIndex = xlColorIndexNone
        .WrapText = True
    End With
    ' Loop through dates
    For lngSR = lngStaffR + 1 To lngLastSR
        lngLC = lngSR + lngAMC - lngStaffR
        ' Loop through staff
        For lngSC = lngStaffC To lngLastSC
            Application.StatusBar = "Processing row " & lngSR - lngStaffR & " of " & lngLastSR - lngStaffR & _
                ", column " & lngSC - lngStaffC + 1 & " of " & lngLastSC - lngStaffC + 1
            DoEvents
            strStaff = wshS.Cells(lngStaffR, lngSC).Value
            strStaff = Replace(strStaff, "zz", "")
            strVal = wshS.Cells(lngSR, lngSC).Value
            strVal = Replace(strVal, "*", "")
            strVal = Replace(strVal, "^", "")
            strVal = Replace(strVal, "?", "")
            strVal = Trim(strVal)
            If strVal <> "" Then
                Set rngFound = wshL.Range(wshL.Cells(lngDatR + 1, lngCodeC), wshL.Cells(lngLastLR, lngCodeC)).Find(What:=strVal, LookAt:=xlWhole, LookIn:=xlValues)
                If rngFound Is Nothing Then
                    Set rngFound = wshL.Range(wshL.Cells(lngDatR + 1, lngFullCodeC), wshL.Cells(lngLastLR, lngFullCodeC)).Find(What:=strVal, LookAt:=xlWhole, LookIn:=xlValues)
                    If rngFound Is Nothing Then
                        ' Code not found - should not occur
                    Else
                        Set rngLoc = wshL.Cells(rngFound.row, lngLC)
                        If rngLoc.Value = "" Then
                            rngLoc.Value = strStaff
                        Else
                            rngLoc.Value = rngLoc.Value & vbLf & strStaff
                        End If
                    End If
                Else
                    Set rngLoc = wshL.Cells(rngFound.row, lngLC)
                    If rngLoc.Value = "" Then
                        rngLoc.Value = strStaff
                    Else
                        rngLoc.Value = rngLoc.Value & vbLf & strStaff
                    End If
                    Set rngLoc = rngLoc.Offset(1)
                    If rngLoc.Value = "" Then
                        rngLoc.Value = strStaff
                    Else
                        rngLoc.Value = rngLoc.Value & vbLf & strStaff
                    End If
                End If
            End If
        Next lngSC
    Next lngSR
    wshL.Select
    Application.ScreenUpdating = True
    Application.StatusBar = False
End Sub
Best wishes,
Hans

dmcnab
3StarLounger
Posts: 200
Joined: 24 Aug 2011, 13:13

Re: Vlookup formula vs. Index, Match ??

Post by dmcnab »

Wow..this is very nice, Hans...thank you so much...it seems to be working perfectly and will really be handy so that users can see the progress...and, as you said, letting you know how much time you have to get a coffee :):) Thanks again

dmcnab
3StarLounger
Posts: 200
Joined: 24 Aug 2011, 13:13

Re: Vlookup formula vs. Index, Match ??

Post by dmcnab »

Good morning, Hans.....I am back from vacation and have been playing around with this latest version of code you gave me on Nov 9th (see above)......as I have mentioned before, it takes a very, very long time to run this code (maybe, 15 minutes)....you said that the length of time is due to the looping back and forth, and the automatic calculating that goes on as part of that. As an experiment, I went to FILE-OPTIONS-FORMULAS and switched from Automatic to Manual (and checked the 'Recalculate workbook before saving' button)......when I ran the Fill_location code it was lightning fast...unbelievable......so, I then made 2 new macros called "Switch_to_Manual" and "Switch_to_Auto".....the VBA code is Application.Calculation=xlManual and Application.Calculation=xlAutomatic respectively.

My question is this: do you think it is better to put these 2 macros into my workbook, and then combine Call Switch_to_Manual, Call Fill_location, Call Switch_to_Auto...........or simply insert the lines Application.Calculation=xlManual and Application.Calculation=xlAutomatic into the Fill_location code? If so, where would I insert each of these lines?.....thanks for your help.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Vlookup formula vs. Index, Match ??

Post by Rudi »

It is not necessary to have extra macros to disable/enable this process. It's probably easier just to add them to the actual macro that relies on the process to be off.
Do the following:
Place Application.Calculation = xlCalculationManual after Application.ScreenUpdating = False, and
place Application.Calculation =xlCalculationAutomatic before Application.ScreenUpdating = True

There are a few options one can use to speed up code, but not all are needed all the time.
Application.ScreenUpdating
The most common option is ScreenUpdating, which freezes the screen to prevent flicker and movement on the screen while the macro runs. It also dramatically speeds up the macro as it does not need to wait for the screen to update with each action it does. Of course its great for loops and ScreenUpdating has the greatest effect on these.
Application.Calculation
As you have discovered, this switches off the calculation process of Excel. If a sheet has 100's of calculations, one can avoid the recalculation each time you change a value and then activate it once at the end of all your edits. It makes a difference in a macro too if the macro updates values or applies calculations on a sheet containing many calculations.
Application.EnableEvents
Disabling events is possible if the book contains event code, and you want to prevent the event from firing (and running additional code) each time the macro affects that event.
Lastly, but not as important or effective as the other three above, there are:
Application.DisplayStatusBar
ActiveSheet.DisplayPageBreaks

Which can be disables and enabled during a macro process too...
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

dmcnab
3StarLounger
Posts: 200
Joined: 24 Aug 2011, 13:13

Re: Vlookup formula vs. Index, Match ??

Post by dmcnab »

Hi Rudi...thank you for your help....I have done as you suggested, and everything is working like a charm, so far.....