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("A
' Set varDataS = Sheets("Summary").Range("A
varDataS = Sheets("Summary").Range("A
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?
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("A
' Set varDataS = Sheets("Summary").Range("A
varDataS = Sheets("Summary").Range("A
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 SubASKER
@Roy, Sorry - In Detail sheet, they are column F.
I still can't see what the actual code is doing. I presume the Select Case will depend on the value of varDataD
ASKER
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(x
' Set varDataS = Sheets("Dom Ex").Range("A3:E" & lngLastRowS)
varDataS = Sheets("Summary").Range("A
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(x
' Set varDataS = Sheets("Dom Ex").Range("A3:E" & lngLastRowS)
varDataS = Sheets("Summary").Range("A
Try this.
Add this sub which is the one you should execute rather than directly executing GetSCS.
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 SubSub 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
Edit: I change line 33.
ASKER
@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?
Once for current, once for proposed #1, once for proposed #2.
Columns BM, BN, BO
How would you recommend that I accomplish that?
????
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?
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?
ASKER
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.
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.
ASKER
So, I could just use both of what you wrote...but use them both for each row! Right?
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.
ASKER
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))
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))
In post ID: 40905366 you say
Columns BM, BN, BObut 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.
ASKER
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
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
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 SubASKER
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.)
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.)
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.
ASKER
Attached.
What-if-scenario-v4.xlsm
What-if-scenario-v4.xlsm
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.
ASKER
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)
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)
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
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
Do you still need help with this question?
ASKER
Yes, I really need the help. I am trying it out with the other code - want to be clear about any issues.
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?
ASKER
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
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
What do you mean by "Lines 28354 & 28602"? I don't find those values any place in the workbook.
ASKER
Rows 28354 & 2862
ASKER
BG28354 should be "IP (1-500)"
BG28602 should be "IP (1-500)"
BG28602 should be "IP (1-500)"
There's no row 28354 in any sheet and the only row 2862 is in the 'List' sheet which I assume isn't involved.
Did you post the right workbook?
ASKER
On the Detail sheet...
The last row on the Detail sheet is 200 and as I said there is no "28354" anyplace in the workbook.
ASKER
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
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
OK that workbook has the rows you were talking about. Let me look at it and I'll get back to you.
ASKER
Oh good!! Thank you so much!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
28701117a.xlsb
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