Add sheet name into a variable.
 

Hi

I am wanting to amend a range defined in my db to a named range.
I have a named range on different worksheets such as:

Sheet1Week1
Sheet2Week2

My formula is:

Application.WorksheetFunction. _CountIf(ws.Range("$G$4:$H$44"),
"Banana")

I want to change $G$4:$H:44 as a named range Sheet1Week1 or whatever
the worksheet is so "wsWeek1". (taking I have defined ws as the
ActiveSheet)

1. How do get the range defined, im not sure how to make a variable
take text as a value?

Dim Week1Range, Week2Range
Dim ws
ws = ActiveSheet
Week1range= ws & "Week1"
Week2range= ws & "Week2"

2. Once I have defined Week1range as Sheet1Week1 how do I put this
into my formula? So replace:

Application.WorksheetFunction. _CountIf(ws.Range("$G$4:$H$44"),
"Banana")

with

Application.WorksheetFunction. _CountIf(ws.Range(Week1range),
"Banana")

Cheers

Rich

Add sheet name into a variable.
 

Hi Rick

See if this is what you need:

Dim Week1Range As String, Week2Range As String
Dim ws As Worksheet
Set ws = ActiveSheet
Week1Range = ws.Name & "Week1"
Week2Range = ws.Name & "Week2"
MyResult = WorksheetFunction.CountIf(Range(Week1Range), "Banana")


Regards,
Per

"Richhall" skrev i meddelelsen
...
Hi

I am wanting to amend a range defined in my db to a named range.
I have a named range on different worksheets such as:

Sheet1Week1
Sheet2Week2

My formula is:

Application.WorksheetFunction. _CountIf(ws.Range("$G$4:$H$44"),
"Banana")

I want to change $G$4:$H:44 as a named range Sheet1Week1 or whatever
the worksheet is so "wsWeek1". (taking I have defined ws as the
ActiveSheet)

1. How do get the range defined, im not sure how to make a variable
take text as a value?

Dim Week1Range, Week2Range
Dim ws
ws = ActiveSheet
Week1range= ws & "Week1"
Week2range= ws & "Week2"

2. Once I have defined Week1range as Sheet1Week1 how do I put this
into my formula? So replace:

Application.WorksheetFunction. _CountIf(ws.Range("$G$4:$H$44"),
"Banana")

with

Application.WorksheetFunction. _CountIf(ws.Range(Week1range),
"Banana")

Cheers

Rich