Working with 2 range but wont work with 3

  • Thread starter Thread starter Cimjet
  • Start date Start date
C

Cimjet

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
 
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
Click to expand...

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
 
Hi Garry
It's not working for me.
No error message just nothing !!!
Would you explane the last part of your macro.
j = j + Application.WorksheetFunction.CountIf(Range(sz), "V")
Next
Debug.Print j
Click to expand...
_________________
Regards
Cimjet
 
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
Click to expand...

It's not working for me. No error message just nothing !!!
Click to expand...

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.
 
Const sRngList As String = _
     "$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"
For Each sz In Split(sRngList, ",")
Click to expand...

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")
 
Hi Joeu2004
Thank you very much, it works perfectly.Now hopefully I wont forget it.
Thanks again
Cimjet

Const sRngList As String = _
"$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"
For Each sz In Split(sRngList, ",")
Click to expand...

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")
 
Const sRngList As String = _
"$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$47"
For Each sz In Split(sRngList, ",")
Click to expand...

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.
 
Hi Garry
As you can see I'm no expert, your macro works fine in the Immediate window.
Thanks for your help
Cimjet
 
Back
Top