Problems with Countif and named range

[thisisnotluisa]

So this is what I have for my named range:

Public Sub Change_Range()


ActiveWorkbook.Names.Add Name:="range_test", _
RefersTo:="'UK WWTWs'!$H$3:$H$" & Sheets("BusinessModel").Range("C47")


End Sub

And this allows me to change the end range of the range_test. And in C47 I have the value of 10.

When I put =range_test in a cell this shows: 'UK WWTWs'!$H$3:$H$10"


But I'm trying to find a value between two numbers and have put this:

=COUNTIF(range_test,">="&K55)-COUNTIF(range_test,">="&M55)

And when I press enter I keep getting #VALUE!

I tried putting range_test into something similar like SUM(range_test) and I get #VALUE! again.

How can I fix this?

 

[Colorations]

Excel might not like the space in UK WWTWs so maybe '[UK WWTWs]'

 

[thisisnotluisa]

I'm still getting the #VALUE! error. :/

 

[steve the fish]

Change your refers to bit to:

RefersTo:=Range("'UK WWTWs'!$H$3:$H$" & Sheets("BusinessModel").Range("C47"))

 

[Jonmo1]

Try

ActiveWorkbook.Names.Add Name:="range_test", _
 RefersTo:="[B][COLOR="#FF0000"]=[/COLOR][/B]'UK WWTWs'!$H$3:$H$" & Sheets("BusinessModel").Range("C47")

 

[thisisnotluisa]

Thank you! That was it.

 

[Jonmo1]

You're welcome.