Working with 2 range but wont work with 3

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
 
G

GS

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
 
C

Cimjet

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
_________________
Regards
Cimjet
 
J

joeu2004

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

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.
 
J

joeu2004

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")
 
C

Cimjet

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, ",")

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")
 
C

Clif McIrvin

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.
 
C

Cimjet

Hi Garry
As you can see I'm no expert, your macro works fine in the Immediate window.
Thanks for your help
Cimjet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top