Vlookup formula vs. Index, Match ??
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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.
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
The letters "zz" don't occur in strVal. They occur in strStaff instead. So instead of
you should use
Code: Select all
strVal = Replace(strVal, "zz", "")
Code: Select all
strStaff = Replace(strStaff, "zz", "")
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
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
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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.
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.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Vlookup formula vs. Index, Match ??
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...
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
Hi Rudi...thank you for your help....I have done as you suggested, and everything is working like a charm, so far.....