VBA Case or If statement conditions

My code was written when I was using one Summary sheet as reference, now I have three separate: Dom Ex, Ground, & Intl
The following needs to have case or If statements to reference the correct sheets, but I am not sure how to begin and afraid to mess it up.

The difference between the conditions is really only with the following since there is no more Summary being referenced.
lngLastRowS = Sheets("Summary").Range("A80000").End(xlUp).Row

'    Set varDataS = Sheets("Summary").Range("A3:E" & lngLastRowS)
varDataS = Sheets("Summary").Range("A3:AQ" & lngLastRowS).Value

FO, PO, SO, E2, EA, EP, F1, F2, F3 reference the Dom Ex sheet
GR, HD, PR reference the Ground
IP, IE, IPF, IEF reference the Intl

Other than that there is no difference in the code.
Can anyone help?



Sub GetSCS()
Dim lngLastRowS As Long
Dim lngLastRowD As Long
Dim lngRowS As Long
Dim lngRowD As Long
Dim lngE As Long
Dim strParts() As String
Dim intLowWt As Integer
Dim intHighWt As Integer
Dim varDataS As Variant
Dim varDataD As Variant
Dim MyVar As String
Dim rgBD As Variant



If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

lngLastRowD = Sheets("Detail").Range("A80000").End(xlUp).Row


lngLastRowS = Sheets("Summary").Range("A80000").End(xlUp).Row
' Note that these are set up to be the VALUES in the sheet rather than
' this which would refer to the sheet itself
'    Set varDataS = Sheets("Summary").Range("A3:E" & lngLastRowS)
varDataS = Sheets("Summary").Range("A3:AQ" & lngLastRowS).Value
varDataD = Sheets("Detail").Range("A2:BG" & lngLastRowD).Value
With ActiveSheet
Set rgBM = Range(.Cells(2, "BG"), .Cells(.Rows.Count, "BG").End(xlUp))
rgBM.ClearContents
End With

For lngRowD = 1 To UBound(varDataD)
    For lngRowS = 1 To UBound(varDataS)
        If varDataS(lngRowS, 2) <> "" Then
            intLowWt = varDataS(lngRowS, 2)
        Else
            intLowWt = 0
        End If
        If varDataS(lngRowS, 3) <> "" Then
            intHighWt = varDataS(lngRowS, 3)
        Else
            intHighWt = 32767
        End If
        strParts = Split(varDataS(lngRowS, 5), ",")
        If (varDataD(lngRowD, 6) = varDataS(lngRowS, 37)) And _
           varDataD(lngRowD, 46) = varDataS(lngRowS, 39) And _
           varDataD(lngRowD, 48) = varDataS(lngRowS, 38) And _
           Val(varDataD(lngRowD, 24)) >= intLowWt And _
           Val(varDataD(lngRowD, 24)) <= intHighWt And _
           UCase(varDataD(lngRowD, 54)) = UCase(varDataS(lngRowS, 4)) Then
           If UBound(strParts) <> -1 Then
                For lngE = 0 To UBound(strParts)
                    If varDataD(lngRowD, 39) = strParts(lngE) Then
                        varDataD(lngRowD, 59) = varDataS(lngRowS, 1)
                        Exit For
                    End If
                Next
            Else
                varDataD(lngRowD, 59) = varDataS(lngRowS, 1)
            End If

        End If
    Next
Next
Sheets("Detail").Range("A2:BG" & lngLastRowD) = varDataD


End Sub

Open in new window

What-if-scenario-v4.xlsm
Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Where do you get these values from?

FO, PO, SO, E2, EA, EP, F1, F2, F3 reference the Dom Ex sheet
GR, HD, PR reference the Ground
IP, IE, IPF, IEF reference the Intl
Euro5Author Commented:
@Roy, Sorry - In Detail sheet, they are column F.
Roy CoxGroup Finance ManagerCommented:
I still can't see what the actual code is doing. I presume the Select Case will depend on the value of varDataD
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Euro5Author Commented:
No, it will only depend on the service type
Case FO, PO, SO, E2, EA, ES
ALL THE CODE

Case GR, HD, PRP
All the code

Case IP, IE, IPF, IEF
All the code

Changed from Summary to 1. Dom Ex 2, Ground 3. Intl
The only difference in eachcode is the sheet
lngLastRowS = Sheets("Dom Ex").Range("A80000").End(xlUp).Row

'    Set varDataS = Sheets("Dom Ex").Range("A3:E" & lngLastRowS)
varDataS = Sheets("Summary").Range("A3:AQ" & lngLastRowS).Value
Martin LissOlder than dirtCommented:
Try this.

Add this sub which is the one you should execute rather than directly executing GetSCS.
Sub GetSCSDriver()
With Sheets("Detail")
    .UsedRange.AutoFilter
    
    .UsedRange.AutoFilter Field:=6, Criteria1:=Array("FO", "PO", "SO", "E2", "EA", "EP", "F1", "F2", "F3"), Operator:=xlFilterValues
    GetSCS "Dom Ex"
    
    .UsedRange.AutoFilter Field:=6, Criteria1:=Array("GR", "HD", "PR"), Operator:=xlFilterValues
    GetSCS "Ground"
    
    .UsedRange.AutoFilter Field:=6, Criteria1:=Array("IP", "IE", "IPF", "IEF"), Operator:=xlFilterValues
    GetSCS "Intl"
    
End With
End Sub

Open in new window

Then replace GetSCS with this version. I'm a little concerned about lines 27 to 29 since I assume the Detail sheet is the active sheet but there's no data in column BM to begin with. Note that in any macro if you are going to refer to the ActiveSheet you should specifically do something like Sheets("Detail").Activate, otherwise there could be unexpected results.
Sub GetSCS(strSheet As String)
Dim lngLastRowS As Long
Dim lngLastRowD As Long
Dim lngRowS As Long
Dim lngRowD As Long
Dim lngE As Long
Dim strParts() As String
Dim intLowWt As Integer
Dim intHighWt As Integer
Dim varDataS As Variant
Dim varDataD As Variant
Dim MyVar As String
Dim rgBD As Variant
Dim rgBM As Range

lngLastRowD = Sheets("Detail").Range("A80000").End(xlUp).Row


lngLastRowS = Sheets(strSheet).Range("A80000").End(xlUp).Row
' Note that these are set up to be the VALUES in the sheet rather than
' this which would refer to the sheet itself
'    Set varDataS = Sheets("Summary").Range("A3:E" & lngLastRowS)
varDataS = Sheets(strSheet).Range("A3:AQ" & lngLastRowS).Value
varDataD = Sheets("Detail").Range("A2:BG" & lngLastRowD).Value

With ActiveSheet
Set rgBM = Range(.Cells(2, "BG"), .Cells(.Rows.Count, "BG").End(xlUp))
rgBM.ClearContents
End With

For lngRowD = 1 To UBound(varDataD)
    For lngRowS = 1 To UBound(varDataS)
    If Sheets("Detail").Rows(lngRowD + 1).Hidden = False Then
            If varDataS(lngRowS, 2) <> "" Then
                intLowWt = varDataS(lngRowS, 2)
            Else
                intLowWt = 0
            End If
            If varDataS(lngRowS, 3) <> "" Then
                intHighWt = varDataS(lngRowS, 3)
            Else
                intHighWt = 32767
            End If
            strParts = Split(varDataS(lngRowS, 5), ",")
            If (varDataD(lngRowD, 6) = varDataS(lngRowS, 37)) And _
               varDataD(lngRowD, 46) = varDataS(lngRowS, 39) And _
               varDataD(lngRowD, 48) = varDataS(lngRowS, 38) And _
               Val(varDataD(lngRowD, 24)) >= intLowWt And _
               Val(varDataD(lngRowD, 24)) <= intHighWt And _
               UCase(varDataD(lngRowD, 54)) = UCase(varDataS(lngRowS, 4)) Then
               If UBound(strParts) <> -1 Then
                    For lngE = 0 To UBound(strParts)
                        If varDataD(lngRowD, 39) = strParts(lngE) Then
                            varDataD(lngRowD, 59) = varDataS(lngRowS, 1)
                            Exit For
                        End If
                    Next
                Else
                    varDataD(lngRowD, 59) = varDataS(lngRowS, 1)
                End If
    
            End If
        End If
    Next
Next

Sheets("Detail").UsedRange.AutoFilter
Sheets("Detail").Range("A2:BG" & lngLastRowD) = varDataD

End Sub

Open in new window

Martin LissOlder than dirtCommented:
Edit: I change line 33.
Euro5Author Commented:
@Martin, I need to complete this three times.
Once for current, once for proposed #1, once for proposed #2.

Columns BM, BN, BO

How would you recommend that I accomplish that?
Martin LissOlder than dirtCommented:
????

You don't say anything about those columns in your original question and the code you posted only clears column BM and never adds anything to it so what do you want to do with those columns?
Euro5Author Commented:
I thought I would just use the modified code for the two other columns.
In the existing project it has a separate GetSCS & SCS2 & SCS3.

I run a similar code that only differs in the reference cells.
So, it is looking at existing cost in the first column, then proposed cost in the second and third.
So it just looks up different cells from the Ground, Ex & Intl sheets.
Euro5Author Commented:
So, I could just use both of what you wrote...but use them both for each row! Right?
Martin LissOlder than dirtCommented:
You say "the two other columns" but as far as I can tell you still haven't told me what you want to do with column BM.
Euro5Author Commented:
Sorry, it doesn't do anything with column BM..
rgBM is a range set to column BG. It allows me to select and clear the column.

Set rgBM = Range(.Cells(2, "BG"), .Cells(.Rows.Count, "BG").End(xlUp))
Martin LissOlder than dirtCommented:
In post ID: 40905366 you say
Columns BM, BN, BO
but in post D: 40905430 you say
Sorry, it doesn't do anything with column BM..
which as I interpret it is contradictory so I'm still not understanding you.
Euro5Author Commented:
Oh my, your right.

There are three columns BG, BH, BI that I needed the code for.
The code you wrote for me works perfectly in BG.
What I did was used it again, with the slight variations, to apply to columns GetSCS2 for BH & 
GetSCS3 for BI.

I get an odd stop at the following line in GetSCS2 for BH. All it does is say to copy to BH what is in row on Dom Ex. The same code works on the GetSCS. Can you tell what the problem is?

                varDataD(lngRowD, 60) = varDataS(lngRowS, 1)
What-if-scenario-v4.xlsm
Martin LissOlder than dirtCommented:
Instead of GetSCS2 and 3 try just these two subs

Sub GetSCSDriver()
Dim arr As Variant
arr = Array("BG", "BH", "BI")
Dim lngIndex As Long

With Sheets("Detail")
    .UsedRange.AutoFilter
    
    .UsedRange.AutoFilter Field:=6, Criteria1:=Array("FO", "PO", "SO", "E2", "EA", "EP", "F1", "F2", "F3"), Operator:=xlFilterValues
    For lngIndex = 0 To 2
        GetSCS "Dom Ex", arr(lngIndex)
    Next
    
    .UsedRange.AutoFilter Field:=6, Criteria1:=Array("GR", "HD", "PR"), Operator:=xlFilterValues
    For lngIndex = 0 To 2
        GetSCS "Ground", arr(lngIndex)
    Next

    .UsedRange.AutoFilter Field:=6, Criteria1:=Array("IP", "IE", "IPF", "IEF"), Operator:=xlFilterValues
    For lngIndex = 0 To 2
        GetSCS "Intl", arr(lngIndex)
    Next
    
End With
End Sub
Sub GetSCS(strSheet As String, varCol As Variant)
Dim lngLastRowS As Long
Dim lngLastRowD As Long
Dim lngRowS As Long
Dim lngRowD As Long
Dim lngE As Long
Dim strParts() As String
Dim intLowWt As Integer
Dim intHighWt As Integer
Dim varDataS As Variant
Dim varDataD As Variant
Dim MyVar As String
Dim rgBD As Variant
Dim rgBM As Range

lngLastRowD = Sheets("Detail").Range("A80000").End(xlUp).Row
lngLastRowS = Sheets(strSheet).Range("A80000").End(xlUp).Row
' Note that these are set up to be the VALUES in the sheet rather than
' this which would refer to the sheet itself
'    Set varDataS = Sheets("Summary").Range("A3:E" & lngLastRowS)
varDataS = Sheets(strSheet).Range("A3:AQ" & lngLastRowS).Value
varDataD = Sheets("Detail").Range("A2:BI" & lngLastRowD).Value

With Sheets("Detail")
    Set rgBM = Range(.Cells(2, varCol), .Cells(.Rows.Count, varCol).End(xlUp))
    rgBM.Offset(1, 0).ClearContents
End With

For lngRowD = 1 To UBound(varDataD)
    For lngRowS = 1 To UBound(varDataS)
        If Sheets("Detail").Rows(lngRowD + 1).Hidden = True Then
            If varDataS(lngRowS, 2) <> "" Then
                intLowWt = varDataS(lngRowS, 2)
            Else
                intLowWt = 0
            End If
            If varDataS(lngRowS, 3) <> "" Then
                intHighWt = varDataS(lngRowS, 3)
            Else
                intHighWt = 32767
            End If
            strParts = Split(varDataS(lngRowS, 5), ",")
            If (varDataD(lngRowD, 6) = varDataS(lngRowS, 37)) And _
               varDataD(lngRowD, 46) = varDataS(lngRowS, 39) And _
               varDataD(lngRowD, 48) = varDataS(lngRowS, 38) And _
               Val(varDataD(lngRowD, 24)) >= intLowWt And _
               Val(varDataD(lngRowD, 24)) <= intHighWt And _
               UCase(varDataD(lngRowD, 54)) = UCase(varDataS(lngRowS, 4)) Then
               If UBound(strParts) <> -1 Then
                    For lngE = 0 To UBound(strParts)
                        If varDataD(lngRowD, 39) = strParts(lngE) Then
                            varDataD(lngRowD, varCol) = varDataS(lngRowS, 1)
                            Exit For
                        End If
                    Next
                Else
                    varDataD(lngRowD, varCol) = varDataS(lngRowS, 1)
                End If
            End If
        End If
    Next
Next

Sheets("Detail").UsedRange.AutoFilter
Sheets("Detail").Range("A2:BI" & lngLastRowD) = varDataD

End Sub

Open in new window

Euro5Author Commented:
I see! I was wondering if something like that was an option.

I tried the above and it runs for Dom Ex, but not for Ground or Intl.
I checked to make sure that there were "matches" - something to find and there are.

But also interesting - I switched the order (having Ground filter come first) and it never goes to the Express. In other words, if Dom Ex filter is first, it finds them, but when it is listed second, it doesn't.
Neither the Ground or Intl work regardless of what the order.

Which is so strange, because they used to work just fine before I started the overhaul.
(not your code here, just since I started reworking.)
Martin LissOlder than dirtCommented:
I put a breakpoint on line 26 and as expected the code got there 3 times for each sheet. Please attach the workbook that you say only does the first of the three sheets.
Euro5Author Commented:
Martin LissOlder than dirtCommented:
I checked and it does execute the code for each of the 3 sheets. Above you said "I checked to make sure that there were "matches" - something to find and there are". Please give me some examples and I'll try to find out why they are not showing up.
Euro5Author Commented:
The code compares cells from Detail sheet and each of the other sheets, same cells.
Detail (column 6) Service  = (column 37) Service from Dom Ex
Detail (column 46) PR = (column 39) PR from Dom Ex
Detail (column 48) Export/Import = (column 38) from Dom Ex
Detail (column 24)  = High/Low weights variables
Detail (column 54) Box/Letter/Pak = (column 4) Box/letter/Pak from Dom Ex

Rows 10 & 11
IP IE 4 lb, Export should show
IP (1-150)   Export
IE (1-150)   Export

Row 1
PO (1-150)    
PO 4 lb box

Row 6
Ground 4lb box
GR (1-150)
Martin LissOlder than dirtCommented:
I made two changes in the code and now it shows results. I don't know if they are the correct results because I don't fully understand your examples. If they are not correct then please give me a few examples by cell of what the values should be. Two examples like this would be enough.

BG2:  PO (1-150)  
BH2:  PO (1-150)  
BI2:  PO (1-150)      

BG15: blah  
BH15:  yada  
BI15:  stuff

Sub GetSCS(strSheet As String, varCol As Variant)
Dim lngLastRowS As Long
Dim lngLastRowD As Long
Dim lngRowS As Long
Dim lngRowD As Long
Dim lngE As Long
Dim strParts() As String
Dim intLowWt As Integer
Dim intHighWt As Integer
Dim varDataS As Variant
Dim varDataD As Variant
Dim MyVar As String
Dim rgBD As Variant
Dim rgBM As Range

lngLastRowD = Sheets("Detail").Range("A80000").End(xlUp).Row
lngLastRowS = Sheets(strSheet).Range("A80000").End(xlUp).Row
' Note that these are set up to be the VALUES in the sheet rather than
' this which would refer to the sheet itself
'    Set varDataS = Sheets("Summary").Range("A3:E" & lngLastRowS)
varDataS = Sheets(strSheet).Range("A3:AQ" & lngLastRowS).Value
varDataD = Sheets("Detail").Range("A2:BI" & lngLastRowD).Value

With Sheets("Detail")
    Set rgBM = Range(.Cells(2, varCol), .Cells(.Rows.Count, varCol).End(xlUp))
    rgBM.Offset(1, 0).ClearContents
End With

For lngRowD = 1 To UBound(varDataD)
    For lngRowS = 1 To UBound(varDataS)
        If Sheets("Detail").Rows(lngRowD + 1).Hidden = False Then
            If varDataS(lngRowS, 2) <> "" Then
                intLowWt = varDataS(lngRowS, 2)
            Else
                intLowWt = 0
            End If
            If varDataS(lngRowS, 3) <> "" Then
                intHighWt = varDataS(lngRowS, 3)
            Else
                intHighWt = 32767
            End If
            strParts = Split(varDataS(lngRowS, 5), ",")
            If (varDataD(lngRowD, 6) = varDataS(lngRowS, 37)) And _
               varDataD(lngRowD, 46) = varDataS(lngRowS, 39) And _
               varDataD(lngRowD, 48) = varDataS(lngRowS, 38) And _
               Val(varDataD(lngRowD, 24)) >= intLowWt And _
               Val(varDataD(lngRowD, 24)) <= intHighWt And _
               UCase(varDataD(lngRowD, 54)) = UCase(varDataS(lngRowS, 4)) Then
               If UBound(strParts) <> -1 Then
                    For lngE = 0 To UBound(strParts)
                        If varDataD(lngRowD, 39) = strParts(lngE) Then
                            varDataD(lngRowD, Columns(varCol).Column) = varDataS(lngRowS, 1)
                            Exit For
                        End If
                    Next
                Else
                    varDataD(lngRowD, Columns(varCol).Column) = varDataS(lngRowS, 1)
                End If
            End If
        End If
    Next
Next

Sheets("Detail").UsedRange.AutoFilter
Sheets("Detail").Range("A2:BI" & lngLastRowD) = varDataD

End Sub

Open in new window

Martin LissOlder than dirtCommented:
Do you still need help with this question?
Euro5Author Commented:
Yes, I really need the help. I am trying it out with the other code - want to be clear about any issues.
Martin LissOlder than dirtCommented:
Can you please try the code in post ID: 40909703 and if needed give me examples of what's wrong as I described in the same post?
Euro5Author Commented:
Sorry for the delay - it wasn't intentional.

The Express & Ground are fine but the Intl shows nothing.
Lines 28354 & 28602 should return IP (1-500) in Column BG but they are blank.
All the Intl is blank.

I attached the file.
What-if-scenario-v4.xlsm
Martin LissOlder than dirtCommented:
What do you mean by "Lines 28354 & 28602"? I don't find those values any place in the workbook.
Euro5Author Commented:
Rows 28354 & 2862
Euro5Author Commented:
BG28354 should be "IP (1-500)"
BG28602 should be "IP (1-500)"
Martin LissOlder than dirtCommented:
There's no row 28354 in any sheet and the only row 2862 is in the 'List' sheet which I assume isn't involved.
Martin LissOlder than dirtCommented:
Did you post the right workbook?
Euro5Author Commented:
On the Detail sheet...
Martin LissOlder than dirtCommented:
The last row on the Detail sheet is 200 and as I said there is no "28354" anyplace in the workbook.
Euro5Author Commented:
I'm confused...I just opened the upload from this page and it is there.

I am saving it and attaching again.
There are 67614 rows of data on Detail

BG28354 should be "IP (1-500)"
BG28602 should be "IP (1-500)"

The code compares cells from Detail sheet and each of the other sheets, same cells.
Detail (column 6) Service  = (column 37) Service
Detail (column 46) PR = (column 39) PR
Detail (column 48) Export/Import = (column 38)
Detail (column 24)  = High/Low weights variables
Detail (column 54) Box/Letter/Pak = (column 4) Box/letter/Pak
What-if-scenario-v4.xlsm
Martin LissOlder than dirtCommented:
OK that workbook has the rows you were talking about. Let me look at it and I'll get back to you.
Euro5Author Commented:
Oh good!! Thank you so much!
Martin LissOlder than dirtCommented:
OK I found three problems.
1. The code needs to know the last row number in the Detail sheet. It was looking in column A for the last cell with data but column A is blank except for the heading so it came up with 1 for the last row. I've fixed that.

2. The code compares what's in column AV on the detail sheet with column AL on the Intl sheet but "EXPORT" isn't equal to "Export" so it found no data. I fixed that by comparing the upper case values of both fields.

3. The above fixed the problem with BG28354, but AV28602 is "IMPORT" and there are no "Import or IMPORT" values in col AL on the Intl sheet so I don't agree with you about BG28602.

As an aside, the GetSCS process is long and you badly need some indicator of progress. I'll see what I can do.

In any case here's the updated code.
Sub GetSCS(strSheet As String, varCol As Variant)
Dim lngLastRowS As Long
Dim lngLastRowD As Long
Dim lngRowS As Long
Dim lngRowD As Long
Dim lngE As Long
Dim strParts() As String
Dim intLowWt As Integer
Dim intHighWt As Integer
Dim varDataS As Variant
Dim varDataD As Variant
Dim MyVar As String
Dim rgBD As Variant
Dim rgBM As Range

lngLastRowD = Sheets("Detail").UsedRange.Rows.Count

lngLastRowS = Sheets(strSheet).Range("A80000").End(xlUp).Row
' Note that these are set up to be the VALUES in the sheet rather than
' this which would refer to the sheet itself
'    Set varDataS = Sheets("Summary").Range("A3:E" & lngLastRowS)
varDataS = Sheets(strSheet).Range("A3:AQ" & lngLastRowS).Value
varDataD = Sheets("Detail").Range("A2:BI" & lngLastRowD).Value

With Sheets("Detail")
    Set rgBM = Range(.Cells(2, varCol), .Cells(.Rows.Count, varCol).End(xlUp))
    rgBM.Offset(1, 0).ClearContents
End With

For lngRowD = 1 To UBound(varDataD)
    For lngRowS = 1 To UBound(varDataS)
        If Sheets("Detail").Rows(lngRowD + 1).Hidden = False Then
            If varDataS(lngRowS, 2) <> "" Then
                intLowWt = varDataS(lngRowS, 2)
            Else
                intLowWt = 0
            End If
            If varDataS(lngRowS, 3) <> "" Then
                intHighWt = varDataS(lngRowS, 3)
            Else
                intHighWt = 32767
            End If
            strParts = Split(varDataS(lngRowS, 5), ",")
            If (varDataD(lngRowD, 6) = varDataS(lngRowS, 37)) And _
               varDataD(lngRowD, 46) = varDataS(lngRowS, 39) And _
               UCase(varDataD(lngRowD, 48)) = UCase(varDataS(lngRowS, 38)) And _
               Val(varDataD(lngRowD, 24)) >= intLowWt And _
               Val(varDataD(lngRowD, 24)) <= intHighWt And _
               UCase(varDataD(lngRowD, 54)) = UCase(varDataS(lngRowS, 4)) Then
               If UBound(strParts) <> -1 Then
                    For lngE = 0 To UBound(strParts)
                        If varDataD(lngRowD, 39) = strParts(lngE) Then
                            varDataD(lngRowD, Columns(varCol).Column) = varDataS(lngRowS, 1)
                            Exit For
                        End If
                    Next
                Else
                    varDataD(lngRowD, Columns(varCol).Column) = varDataS(lngRowS, 1)
                End If
            End If
        End If
    Next
Next

Sheets("Detail").UsedRange.AutoFilter
Sheets("Detail").Range("A2:BI" & lngLastRowD) = varDataD

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Here's a workbook where the GetSCSDriver sub shows progress in a userform. If you like it just export frmProgress from the workbook and import it into your workbook. Then replace your GetSCSDriver sub with the one in this workbook. Note that this is an xlsb workbook. I find that larger workbooks work better in xlsb format.
28701117a.xlsb
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.