Working with 2 range but wont work with 3
 

Hi Everyone
This is working ok but if I add one more range, it wont work.

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrge = Worksheets("Calendar").Range("b6:af17", "b21:af32")
Range("R50") = Application.WorksheetFunction.CountIf(myrge, "V")
End Sub

This wont work:
Private Sub Worksheet_Change(ByVal Target As Range)
Set myrge = Worksheets("Calendar").Range("b6:af17", "b21:af32", "B36:af47")
Range("R50") = Application.WorksheetFunction.CountIf(myrge, "V")
End Sub
Regards
Cimjet

Working with 2 range but wont work with 3
 

It happens that Cimjet formulated :
Hi Everyone
This is working ok but if I add one more range, it wont work.

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrge = Worksheets("Calendar").Range("b6:af17", "b21:af32")
Range("R50") = Application.WorksheetFunction.CountIf(myrge, "V")
End Sub

This wont work:
Private Sub Worksheet_Change(ByVal Target As Range)
Set myrge = Worksheets("Calendar").Range("b6:af17", "b21:af32", "B36:af47")
Range("R50") = Application.WorksheetFunction.CountIf(myrge, "V")
End Sub
Regards
Cimjet

try...

Sub CountVs()
Dim sz As Variant, i As Integer, j As Long
Const sRngList As String = "$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"

For Each sz In Split(sRngList, ",")
j = j + Application.WorksheetFunction.CountIf(Range(sz), "V")
Next
Debug.Print j
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Working with 2 range but wont work with 3
 

Hi Garry
It's not working for me.
No error message just nothing !!!
Would you explane the last part of your macro.
For Each sz In Split(sRngList, ",")
j = j + Application.WorksheetFunction.CountIf(Range(sz), "V")
Next
Debug.Print j
_________________
Regards
Cimjet

"GS" wrote in message ...
It happens that Cimjet formulated :
Hi Everyone
This is working ok but if I add one more range, it wont work.

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrge = Worksheets("Calendar").Range("b6:af17", "b21:af32")
Range("R50") = Application.WorksheetFunction.CountIf(myrge, "V")
End Sub

This wont work:
Private Sub Worksheet_Change(ByVal Target As Range)
Set myrge = Worksheets("Calendar").Range("b6:af17", "b21:af32", "B36:af47")
Range("R50") = Application.WorksheetFunction.CountIf(myrge, "V")
End Sub
Regards
Cimjet

try...

Sub CountVs()
Dim sz As Variant, i As Integer, j As Long
Const sRngList As String = "$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"

For Each sz In Split(sRngList, ",")
j = j + Application.WorksheetFunction.CountIf(Range(sz), "V")
Next
Debug.Print j
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Working with 2 range but wont work with 3
 

On May 10, 3:33 pm, GS wrote:
Const sRngList As String = "$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"
For Each sz In Split(sRngList, ",")
j = j + Application.WorksheetFunction.CountIf(Range(sz), "V")
Next
Debug.Print j

On May 10, 3:56*pm, "Cimjet" wrote:
It's not working for me. No error message just nothing !!!

Well, for starters, you would have press ctrl+G in the VBE in order to
see the result of the Debug.Print statement in the Immediate Window.

But also, Range(sz) might not reference exactly the same ranges as you
intended with
Worksheets("Calendar").Range("b6:af17","b21:af32", "B36:af47").

Try the following (beware of unintended line wrapping):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sz As Variant, t As Long
Const sRngList As String = _
"$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"
For Each sz In Split(sRngList, ",")
t = t + _

Application.WorksheetFunction.CountIf(Worksheets(" Calendar").Range(sz),"V")
Next
Range("R50") = t
End Sub

Caveat: I suspect you want to do this only if Target matches
something. Hard to tell from the context.

Working with 2 range but wont work with 3
 

On May 10, 4:18*pm, I wrote:
Const sRngList As String = _
* * *"$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"
For Each sz In Split(sRngList, ",")

Seems just an easy and perhaps more efficient to forget about Const
sRngList and write simply:

For Each sz In _
Array("$B$6:$AF$17", "$B$21:$AF$32", "$B$36:$AF$47")

Working with 2 range but wont work with 3
 

Hi Joeu2004
Thank you very much, it works perfectly.
See the result of the Debug.Print statement in the Immediate Window<<. That is
new for me, I just learned something new.
Now hopefully I wont forget it.
Thanks again
Cimjet

"joeu2004" wrote in message
...
On May 10, 4:18 pm, I wrote:
Const sRngList As String = _
"$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"
For Each sz In Split(sRngList, ",")

Seems just an easy and perhaps more efficient to forget about Const
sRngList and write simply:

For Each sz In _
Array("$B$6:$AF$17", "$B$21:$AF$32", "$B$36:$AF$47")

Working with 2 range but wont work with 3
 

"joeu2004" wrote in message
...
On May 10, 4:18 pm, I wrote:
Const sRngList As String = _
"$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"
For Each sz In Split(sRngList, ",")

Seems just an easy and perhaps more efficient to forget about Const
sRngList and write simply:

For Each sz In _
Array("$B$6:$AF$17", "$B$21:$AF$32", "$B$36:$AF$47")


-------

I've often wondered about that. The argument I have used with myself
runs along the lines of, "using the hard-coded array function is using
'magic numbers'; but using the CONST in the declarations section to
avoid use of magic numbers results in separating the actual constant
from it's area of use which can make it harder for me to follow what I'm
doing."

I tend to use CONST more often than not for this reason.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Working with 2 range but wont work with 3
 

Hi Garry
As you can see I'm no expert, your macro works fine in the Immediate window.
Thanks for your help
Cimjet
"GS" wrote in message ...
It happens that Cimjet formulated :
Hi Everyone
This is working ok but if I add one more range, it wont work.

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrge = Worksheets("Calendar").Range("b6:af17", "b21:af32")
Range("R50") = Application.WorksheetFunction.CountIf(myrge, "V")
End Sub

This wont work:
Private Sub Worksheet_Change(ByVal Target As Range)
Set myrge = Worksheets("Calendar").Range("b6:af17", "b21:af32", "B36:af47")
Range("R50") = Application.WorksheetFunction.CountIf(myrge, "V")
End Sub
Regards
Cimjet

try...

Sub CountVs()
Dim sz As Variant, i As Integer, j As Long
Const sRngList As String = "$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"

For Each sz In Split(sRngList, ",")
j = j + Application.WorksheetFunction.CountIf(Range(sz), "V")
Next
Debug.Print j
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc