Welcome to the Select Case structure. :-)

In the example below, if rngCell is either 14 or 7 your code will execute.
(The 2nd Case probably isn't necessary in this case, as long as you include
a "Case Else", but I include it as an example)

Once a "matching" case is found, the associated code is executed followed by
whatever follows EndSelect.
That means that if there is a possibilty that something might match 2 Case
statements, only the code for the first one is executed.

Select Case rngCell
Case 14, 7
'do the counting
(your code here)
Case <3, 10, 11, 15, "", "?", "na"
' skip
Case Else
' skip these too
End Select

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"davegb" wrote in message
oups.com...
I have a program, that with a lot of help here, works ok. The problem
is, that as I test it on addtional spreadsheets that it has to run on,
I'm finding more codes that I hadn't accounted for. I originally wrote
the program to eliminate non-counted codes. I realize now that I should
have originally written the code to only include the codes I want to
count, and just skip the others. This would also remove the need for
some of the other qualifiers, like "?" and other things that appear in
some of the sheets, that aren't counted.
Here's the code as is (all variables declared):

Const PWORD As String = "2005totals"
lEndRow = 1000
lTotNameRow = 4
Set wksSrc = ActiveSheet
Set wksTot = ActiveWorkbook.Sheets("TOTALS")
Set rngCode = wksSrc.Range("D8:D" & lEndRow)
wksTot.Unprotect Password:=PWORD

strMonWksht = wksSrc.Name & " - Monthly"
Set wksMon = Sheets(strMonWksht)

wksMon.Range("B4:K15").ClearContents

For Each rngCell In rngCode

dteColCode = 0

If rngCell < "na" Then
If rngCell < "?" Then
If Len(rngCell) < 3 Then
If rngCell < 0 Then
If rngCell < 10 Then
If rngCell < 11 Then
If rngCell < 15 Then
If rngCell < "" Then

'Counting the codes needed happens here

End If
End If
End If
End If
End If
End If
End If

Next rngCell

End Sub

If I change the series of tests to something like

If rngCell = 14 then
'do the counting
else
if rngCell = 7
'do the counting
else
Etc, etc.

I have a bunch of If statements that if true, go to the counting
routine. But I don't want to repeat the same code over and over. If I
call a routine to do the counting, when it returns, I want it to go to
the next cell in rngCode, not the next test, which is now unneccessary.
If rngCell is not equal to any of the tested values, I want it to go to
Next rngCell in rngCode. I'm not sure how to code all this without,
heaven forbid, branching!
Is it considered "branching" if the program goes to the counting
routine, and the counting routine sends it back to the beginning of the
testing routine, rather than back to the same place in the code it was
called from? It seems it would be very easy to end up in an endless
loop this way, although if I did it right, it wouldn't really happen.
But I think that part of the reason branching is "heresy" is because of
the possibility of endless looping.
Can someone show me how this is done? Thanks!