Solved: list of exception for loop

[samohtwerdna]

Hello again,

I have created I nice little Addin (with matt's help) that calculates the sqr ft of a job multiplies the total by a finish factor and calculates the interior based on a default formula if the name starts with "WG" My code looks like this:

[VBA]Private Sub cmdQuote_Click()

Dim CLL As Range, Totl As Double, Qty As Range, Wdth As Range, Hght As Range
Dim Nm As Range, Totl2 As Double, vWdth As Double, vHght As Double

Set Qty = Columns("C")
Set Wdth = Columns("F")
Set Hght = Columns("G")
Set Nm = Columns("D")

For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WO" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
Next
With lblTotl
.Caption = Totl
End With
With lblTotl2
.Caption = Totl2
End With
' MsgBox "Totl: " & Totl & vbCrLf & "Totl2: " & Totl2 & vbCrLf & "txtFinish: " & txtFinish.Value
txtQuoteTotal.Value = (Totl / 144 + Totl2 / 144) * txtFinish.Value
txtQuoteTotal.Value = Format(txtQuoteTotal.Value, "$###,##0.00")

End Sub[/VBA]

Now I would like to create a list of names that would not be considered in my loop through the items or would have a zero total if included in the loop.

Can I write a function and call it exclude() and then simply write a list of strings to check for and then if they loop finds this name Totl = 0 for that item??

Or is it more complex than this??

[mvidas]

Hi again,

You can do it that way, using a function like[vba]Function Exclude(ByVal TheValue, ByVal TheList)
On Error Resume Next
Dim RetVal As Long
RetVal = Application.Match(TheValue, TheList, 0)
Exclude = RetVal > 0
End Function[/vba]You can test it with code like:[vba]Sub ExcludeTest()
MsgBox Exclude("a", Array("a", "b", "c", "d", "e", "g"))
MsgBox Exclude("c", Array("a", "b", "c", "d", "e", "g"))
MsgBox Exclude("f", Array("a", "b", "c", "d", "e", "g"))
MsgBox Exclude("D", Array("a", "b", "c", "d", "e", "g"))
End Sub[/vba]You'll see it really just uses application.match to test it with an array. I also included that last one in there to show that it is case-insensitive. You could loop through the array as well if you wanted it to be case sensitive, but I figured using match would be an easier route. Using your existing code, the following will include the numbers in Totl unless the name is 'apples','oranges', or 'bananas'[vba] For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
If Not Exclude(Intersect(CLL.EntireRow, Nm), Array("Apples", "Oranges", _
"Bananas")) Then
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WO" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
End If
Next[/vba]Just as an example
Matt

[samohtwerdna]

Thanks again Matt!

The array works great!

I have only one last bit to complete the Addin. That is I want to treat names that start with "WO"; "TO" and "BO" the same as well as names "WG"; "TG" and "BG"

Can I just use the Or in my conditional statement?

[VBA] If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WO" Or "TO" Or "BO" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else[/VBA]

porbably not I know - but I'm not sure why not.

[samohtwerdna]

Matt,

One other question. If I type cast the if statement before the array can I check the first 2 letters of the Name like in the Totl condition or do I have to write out the whole name in the array??

I'm thinking:
[VBA] If Not Exclude(Left(Intersect(CLL.EntireRow, Nm),2)) Array("PSD", "PSDP", _
"FVDM", "WM")) Then[/VBA]
???

[samohtwerdna]

Matt or anyone interested,

I solved my last question. My typecast was not complete - Here is what I came up with that works:

[VBA] If Not Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("M-", "PS", _
"FVDM", "WM", "AS", "PC", "KN", "NC", "DW","GO", "WP")) Then[/VBA]

I needed the UCase in the type cast

[mvidas]

After reading your last post, I should probably remind you that the first 2 letters of the cell could not be "FVDM"

As for[vba]If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WO" Or "TO" Or "BO" Then[/vba]As you've probably noticed you can't use that syntax. You have a couple options as to what you could do, the last (select case) would be the easiest probably:[vba]
'option 1
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WO" Or _
UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "TO" Or _
UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "BO" Then
'code
Else
'else code
End if

'option 2
Dim vNm As String
vNm = UCase(Left(Intersect(CLL.EntireRow, Nm), 2))
If vNm = "WO" Or vNm = "TO" Or vNm = "BO" Then
'code
Else
'else code
End If

'option 3
Select Case UCase(Left(Intersect(CLL.EntireRow, Nm), 2))
Case "WO", "TO", "BO"
'your code
Case Else
'else code
End Select[/vba]

[mvidas]

You could also use a reverse of your new Exclude function. Rather than use "If Not Exclude(...", you could even just use "If Exclude(...", and it will return true if the name is in the list.

[samohtwerdna]

You could also use a reverse of your new Exclude function. Rather than use "If Not Exclude(...", you could even just use "If Exclude(...", and it will return true if the name is in the list.

Does that mean just take the "Not" out and leave my array the same - or will this effect my array? - I'm guessing not

Also I figured out that "FVDM" cannot be the first two letters, the bummer is that in this case I want to allow "FVDW" but not "FVDM" so I need the fourth letter - but this is the only one where I need the fourth letter - I know this will sound dumb but can I use the * for letters I don't care about like:
[VBA]
If Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 4)), Array("PE**", "PS**", _
"FVDM", "WM**", "AS**", "PC**", "KN**", "NC**", "DW**", "GO**", "WP**")) Then[/VBA]

Wouldn't this be nice??

[mvidas]

Coding in wildcards could work, but I think it would be too much work for not enough reward.. you'd be better off using two Exclude calls, one with 2 characters and then OR then one with 4 characters.

As for your first question, the two of these should be the same:[vba]'This:
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WO" Or _
UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "TO" Or _
UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "BO" Then
'code
Else
'else code
End If
'Should be the same as:
If Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("WO", "TO", "BO")) Then[/vba]Matt

[samohtwerdna]

Thanks Matt,

Very helpful... I am just a little confused about the double call for the excludes. I tried putting an ElseIf at the end of my first call but that didn't seem to exclude anything - not even the first exclude condition which was working fine before I inserted the ElseIf?? So then I just put an Else and moved everything down a line (Which is the same thing as an ElseIf I think) and that too did not work.

I assume that the second call has to be inside my "For" and "Next" but I'm not sure why it's not working. When I would debug - it would move through the second call exclude but still add the item - So maybe my syntax is wrong some where?

[VBA]For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
If Not Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("PE", "PS", _
"WM", "AS", "PC", "KN", "NC", "DW", "GO", "WP")) Then
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("WO", "TO", "BO")) Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
ElseIf Not Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("FVDM", "FHDM", _
"MDDI")) Then
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("WO", "TO", "BO")) Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
End If
Next[/VBA]

[mvidas]

On quick glance, it looks like your ElseIf is still only taking the left 2 letters, instead of 4. It should be:[vba]ElseIf Not Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 4)), Array("FVDM", "FHDM", _
"MDDI")) Then [/vba]Matt

[samohtwerdna]

Matt, I actualy did have the "4" in there I just for got to post it that way - Anyway, the ElseIf in that spot seems to make the first call to the exclude not work either?

[mvidas]

Hmmm... doesnt seem wrong to me, looks like everything should work fine. Since your calculations are the same in the If and ElseIf, what if you just did:[vba]For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
'if cell starts with PE, PS, WM, AS, PC, KN, NC, DW, GO, WP, FVDM, FHDM, MDDI
If Not Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("PE", "PS", _
"WM", "AS", "PC", "KN", "NC", "DW", "GO", "WP")) Or Not Exclude(UCase(Left _
(Intersect(CLL.EntireRow, Nm), 4)), Array("FVDM", "FHDM", "MDDI")) Then
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("WO", "TO", "BO")) Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
End If
Next[/vba]

[samohtwerdna]

I don't understand why - but If you have item 1 as "WG-3036 14" width = 30; height = 35 .875 and a second item of "PSD" width = 22; height = 15 you should get a Quote total of $2,196.27 - using a $50.00 finish cost

with:
[VBA]For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells

If Not Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("PE", "PS", _
"WM", "AS", "PC", "KN", "NC", "DW", "GO", "WP")) Then
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("WO", "TO", "BO")) Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
End If
Next[/VBA]

That's what you get - but with this:
[VBA]For Each CLL In Intersect(Qty, ActiveSheet.UsedRange).Cells
'if cell starts with PE, PS, WM, AS, PC, KN, NC, DW, GO, WP, FVDM, FHDM, MDDI
If Not Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("PE", "PS", _
"WM", "AS", "PC", "KN", "NC", "DW", "GO", "WP")) Or Not Exclude(UCase(Left _
(Intersect(CLL.EntireRow, Nm), 4)), Array("FVDM", "FHDM", "MDDI")) Then
If IsNumeric(CLL) Then
vWdth = Intersect(CLL.EntireRow, Wdth).Value
vHght = Intersect(CLL.EntireRow, Hght).Value
If Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("WO", "TO", "BO")) Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
Else
If UCase(Left(Intersect(CLL.EntireRow, Nm), 2)) = "WG" Then
Totl2 = Totl2 + CLL.Value * (26 * vHght + 108 * vWdth + vWdth * vHght)
End If
Totl = Totl + CLL.Value * vWdth * vHght
End If
End If
End If
Next[/VBA]
You get $2,310.85
?? not sure where the code adds/excludes incorrectly - but it must be in the second pass. I assume that it includes what it formaly excluded because the Name is not in the second array - Is that correct??

[mvidas]

Wow, that just got real confusing real quick for me The problem was in my syntax, instead of an "OR" between those two Exclude calls, it should have been an "AND", as the number shouldn't be in either list. Try changing it to:[vba] If Not Exclude(UCase(Left(Intersect(CLL.EntireRow, Nm), 2)), Array("PE", "PS", _
"WM", "AS", "PC", "KN", "NC", "DW", "GO", "WP")) And Not Exclude(UCase(Left _
(Intersect(CLL.EntireRow, Nm), 4)), Array("FVDM", "FHDM", "MDDI")) Then[/vba]Matt

[samohtwerdna]

Hi Matt,

Thanks for all the help! The "And" worked and now I'm up and running