New approach

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!

It would be much better if you just explained clearly what the precise task
is you
have to do and asked for suggestions how to solve that task.
If you are just trying to count a defined number of particular cell values
in a workbook
(or worksheet?) then that shouldn't be that difficult.

RBS


"davegb" <[email protected]> wrote in message
news:[email protected]...
>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!
>

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" <[email protected]> wrote in message
news:[email protected]...
>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!
>

RB Smissaert wrote:
> It would be much better if you just explained clearly what the precise task
> is you
> have to do and asked for suggestions how to solve that task.
> If you are just trying to count a defined number of particular cell values
> in a workbook
> (or worksheet?) then that shouldn't be that difficult.
>
> RBS
>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> >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!
> >

Thanks for your reply.
The program checks rngCell for one of 7 values. If it's one of those,
it goes through a counting routine to put the counts in another sheet
with the same name as the source sheet, but with " - Monthly" added. If
rngCell doesn't contain one of those 7 values, the next cell is
processed.
I've been working on it since I last posted. Here's the complete code:

Sub CountMonth1()

Dim lngRsnCode As Long
Dim wksSrc As Worksheet
Dim wksMon As Worksheet
Dim wksTot As Worksheet
Dim rngCode As Range
Dim lEndRow As Long
Dim strMonWksht As String
Dim dteColCode As Date
Dim lngCntctMo As Long
Dim lngMoRow As Long
Dim rngCell As Range
Dim varColCode As Variant
Dim strColCode As String
Dim rReason As Range
Dim lCt As Long
Dim lTotNameRow As Long
Dim rng16Code As Range


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 = 1 Then GoTo Countcodes
If rngCell = 14 Then GoTo Countcodes
If rngCell = 4 Then GoTo Countcodes
If rngCell = 13 Then GoTo Countcodes
If rngCell = 3 Then GoTo Countcodes
If rngCell = 16 Then GoTo Countcodes
If rngCell = 7 Then GoTo Countcodes

GoTo nextcell


Countcodes:
Set varColCode = rngCell.Offset(0, 5)
If InStr(1, varColCode, ",") = 0 Then
If Trim(varColCode.Value) <> "" Then
On Error Resume Next
dteColCode = DateValue(varColCode.Value)
On Error GoTo 0
If dteColCode <> Empty Then

lngCntctMo = Month(dteColCode)
lngMoRow = lngCntctMo + 3
lngRsnCode = rngCell.Value
wksTot.Range("AC1") = lngRsnCode
strColCode = wksTot.Range("AC2")
Set rng16Code = wksMon.Cells(lngMoRow, strColCode)
wksMon.Cells(lngMoRow, strColCode) = _
wksMon.Cells(lngMoRow, strColCode) + 1


If rngCell = "16" Then
Set rng16Code = wksMon.Cells(lngMoRow,
strColCode)

lCt = InStr(1, UCase(rngCell.Offset(0,
2).Value), "R")
If lCt > 0 Then
rng16Code.Offset(0, 1) = _
rng16Code.Offset(0, 1) + 1

lCt = 0
End If

lCt = InStr(1, UCase(rngCell.Offset(0,
2).Value), "A")
If lCt > 0 Then
rng16Code.Offset(0, 2) = _
rng16Code.Offset(0, 2) + 1
lCt = 0
End If

lCt = InStr(1, UCase(rngCell.Offset(0,
2).Value), "B")
If lCt > 0 Then
rng16Code.Offset(0, 3) = _
rng16Code.Offset(0, 3) + 1
Else
lCt = InStr(1, UCase(rngCell.Offset(0,
2).Value), "G")
If lCt > 0 Then
rng16Code.Offset(0, 3) = _
rng16Code.Offset(0, 3) + 1
lCt = 0
End If
End If
End If
End If
End If
End If

nextcell:
Next rngCell

End Sub

It works fine. But I'm afraid I've violated the "no branching" rule by
using GoTo statements.Is this the case? If so, how could I do the same
thing without branching? Obviously, I could replace the GoTo's with
Calls and make CountCodes a separate macro, but that's really the same
thing, isn't it?
Thanks.

George Nicholson wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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!
> >

Thanks, George, that did the trick! Didn't even need the "skipped"
ones, just the ones that had to be counted.

Sounds you have solved your problem with Select Case.
It looks your amount of data is not that big, but you could speed it up by
assigning the range to check to an array and run your checking code on that
array.
Another option would be to use SQL. See my reply to the post:
Count duplicates in an array of 2 Dec.

RBS


"davegb" <[email protected]> wrote in message
news:[email protected]...
>
> RB Smissaert wrote:
>> It would be much better if you just explained clearly what the precise
>> task
>> is you
>> have to do and asked for suggestions how to solve that task.
>> If you are just trying to count a defined number of particular cell
>> values
>> in a workbook
>> (or worksheet?) then that shouldn't be that difficult.
>>
>> RBS
>>
>>
>> "davegb" <[email protected]> wrote in message
>> news:[email protected]...
>> >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!
>> >
>
> Thanks for your reply.
> The program checks rngCell for one of 7 values. If it's one of those,
> it goes through a counting routine to put the counts in another sheet
> with the same name as the source sheet, but with " - Monthly" added. If
> rngCell doesn't contain one of those 7 values, the next cell is
> processed.
> I've been working on it since I last posted. Here's the complete code:
>
> Sub CountMonth1()
>
> Dim lngRsnCode As Long
> Dim wksSrc As Worksheet
> Dim wksMon As Worksheet
> Dim wksTot As Worksheet
> Dim rngCode As Range
> Dim lEndRow As Long
> Dim strMonWksht As String
> Dim dteColCode As Date
> Dim lngCntctMo As Long
> Dim lngMoRow As Long
> Dim rngCell As Range
> Dim varColCode As Variant
> Dim strColCode As String
> Dim rReason As Range
> Dim lCt As Long
> Dim lTotNameRow As Long
> Dim rng16Code As Range
>
>
> 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 = 1 Then GoTo Countcodes
> If rngCell = 14 Then GoTo Countcodes
> If rngCell = 4 Then GoTo Countcodes
> If rngCell = 13 Then GoTo Countcodes
> If rngCell = 3 Then GoTo Countcodes
> If rngCell = 16 Then GoTo Countcodes
> If rngCell = 7 Then GoTo Countcodes
>
> GoTo nextcell
>
>
> Countcodes:
> Set varColCode = rngCell.Offset(0, 5)
> If InStr(1, varColCode, ",") = 0 Then
> If Trim(varColCode.Value) <> "" Then
> On Error Resume Next
> dteColCode = DateValue(varColCode.Value)
> On Error GoTo 0
> If dteColCode <> Empty Then
>
> lngCntctMo = Month(dteColCode)
> lngMoRow = lngCntctMo + 3
> lngRsnCode = rngCell.Value
> wksTot.Range("AC1") = lngRsnCode
> strColCode = wksTot.Range("AC2")
> Set rng16Code = wksMon.Cells(lngMoRow, strColCode)
> wksMon.Cells(lngMoRow, strColCode) = _
> wksMon.Cells(lngMoRow, strColCode) + 1
>
>
> If rngCell = "16" Then
> Set rng16Code = wksMon.Cells(lngMoRow,
> strColCode)
>
> lCt = InStr(1, UCase(rngCell.Offset(0,
> 2).Value), "R")
> If lCt > 0 Then
> rng16Code.Offset(0, 1) = _
> rng16Code.Offset(0, 1) + 1
>
> lCt = 0
> End If
>
> lCt = InStr(1, UCase(rngCell.Offset(0,
> 2).Value), "A")
> If lCt > 0 Then
> rng16Code.Offset(0, 2) = _
> rng16Code.Offset(0, 2) + 1
> lCt = 0
> End If
>
> lCt = InStr(1, UCase(rngCell.Offset(0,
> 2).Value), "B")
> If lCt > 0 Then
> rng16Code.Offset(0, 3) = _
> rng16Code.Offset(0, 3) + 1
> Else
> lCt = InStr(1, UCase(rngCell.Offset(0,
> 2).Value), "G")
> If lCt > 0 Then
> rng16Code.Offset(0, 3) = _
> rng16Code.Offset(0, 3) + 1
> lCt = 0
> End If
> End If
> End If
> End If
> End If
> End If
>
> nextcell:
> Next rngCell
>
> End Sub
>
> It works fine. But I'm afraid I've violated the "no branching" rule by
> using GoTo statements.Is this the case? If so, how could I do the same
> thing without branching? Obviously, I could replace the GoTo's with
> Calls and make CountCodes a separate macro, but that's really the same
> thing, isn't it?
> Thanks.
>

RB Smissaert wrote:
> Sounds you have solved your problem with Select Case.
> It looks your amount of data is not that big, but you could speed it up by
> assigning the range to check to an array and run your checking code on that
> array.
> Another option would be to use SQL. See my reply to the post:
> Count duplicates in an array of 2 Dec.
>
> RBS
>
>
> "davegb" <[email protected]> wrote in message
> news:[email protected]...
> >
> > RB Smissaert wrote:
> >> It would be much better if you just explained clearly what the precise
> >> task
> >> is you
> >> have to do and asked for suggestions how to solve that task.
> >> If you are just trying to count a defined number of particular cell
> >> values
> >> in a workbook
> >> (or worksheet?) then that shouldn't be that difficult.
> >>
> >> RBS
> >>
> >>
> >> "davegb" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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!
> >> >
> >
> > Thanks for your reply.
> > The program checks rngCell for one of 7 values. If it's one of those,
> > it goes through a counting routine to put the counts in another sheet
> > with the same name as the source sheet, but with " - Monthly" added. If
> > rngCell doesn't contain one of those 7 values, the next cell is
> > processed.
> > I've been working on it since I last posted. Here's the complete code:
> >
> > Sub CountMonth1()
> >
> > Dim lngRsnCode As Long
> > Dim wksSrc As Worksheet
> > Dim wksMon As Worksheet
> > Dim wksTot As Worksheet
> > Dim rngCode As Range
> > Dim lEndRow As Long
> > Dim strMonWksht As String
> > Dim dteColCode As Date
> > Dim lngCntctMo As Long
> > Dim lngMoRow As Long
> > Dim rngCell As Range
> > Dim varColCode As Variant
> > Dim strColCode As String
> > Dim rReason As Range
> > Dim lCt As Long
> > Dim lTotNameRow As Long
> > Dim rng16Code As Range
> >
> >
> > 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 = 1 Then GoTo Countcodes
> > If rngCell = 14 Then GoTo Countcodes
> > If rngCell = 4 Then GoTo Countcodes
> > If rngCell = 13 Then GoTo Countcodes
> > If rngCell = 3 Then GoTo Countcodes
> > If rngCell = 16 Then GoTo Countcodes
> > If rngCell = 7 Then GoTo Countcodes
> >
> > GoTo nextcell
> >
> >
> > Countcodes:
> > Set varColCode = rngCell.Offset(0, 5)
> > If InStr(1, varColCode, ",") = 0 Then
> > If Trim(varColCode.Value) <> "" Then
> > On Error Resume Next
> > dteColCode = DateValue(varColCode.Value)
> > On Error GoTo 0
> > If dteColCode <> Empty Then
> >
> > lngCntctMo = Month(dteColCode)
> > lngMoRow = lngCntctMo + 3
> > lngRsnCode = rngCell.Value
> > wksTot.Range("AC1") = lngRsnCode
> > strColCode = wksTot.Range("AC2")
> > Set rng16Code = wksMon.Cells(lngMoRow, strColCode)
> > wksMon.Cells(lngMoRow, strColCode) = _
> > wksMon.Cells(lngMoRow, strColCode) + 1
> >
> >
> > If rngCell = "16" Then
> > Set rng16Code = wksMon.Cells(lngMoRow,
> > strColCode)
> >
> > lCt = InStr(1, UCase(rngCell.Offset(0,
> > 2).Value), "R")
> > If lCt > 0 Then
> > rng16Code.Offset(0, 1) = _
> > rng16Code.Offset(0, 1) + 1
> >
> > lCt = 0
> > End If
> >
> > lCt = InStr(1, UCase(rngCell.Offset(0,
> > 2).Value), "A")
> > If lCt > 0 Then
> > rng16Code.Offset(0, 2) = _
> > rng16Code.Offset(0, 2) + 1
> > lCt = 0
> > End If
> >
> > lCt = InStr(1, UCase(rngCell.Offset(0,
> > 2).Value), "B")
> > If lCt > 0 Then
> > rng16Code.Offset(0, 3) = _
> > rng16Code.Offset(0, 3) + 1
> > Else
> > lCt = InStr(1, UCase(rngCell.Offset(0,
> > 2).Value), "G")
> > If lCt > 0 Then
> > rng16Code.Offset(0, 3) = _
> > rng16Code.Offset(0, 3) + 1
> > lCt = 0
> > End If
> > End If
> > End If
> > End If
> > End If
> > End If
> >
> > nextcell:
> > Next rngCell
> >
> > End Sub
> >
> > It works fine. But I'm afraid I've violated the "no branching" rule by
> > using GoTo statements.Is this the case? If so, how could I do the same
> > thing without branching? Obviously, I could replace the GoTo's with
> > Calls and make CountCodes a separate macro, but that's really the same
> > thing, isn't it?
> > Thanks.
> >

Thanks for your reply.