New approach

[davegb]

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" & 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]

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

 

[George Nicholson]

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,

 

[davegb]

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

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" & 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.

 

[davegb]

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,

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

 

[RB Smissaert]

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]

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

Thanks for your reply.