Help with named ranges

A

Andrew B

Hi
I am trying to perform calculations on named ranges in a data sheet.
Each range is non contiguous and named as follows:
crng4, crng5, crng6, ... , crng24

I would like to access them through a loop as shown below.
Is this possible ?
Everything I have tried so far has not worked.
e.g.

For k = 4 To 24

If Shd.Cells(i + j, k) <> "" Then
Set rng.Name = "crng" & k
Shpro.Cells(3 + k, 12 * j + 7) =
Round(AA.PercentRank(Shd.Range(rng), Shd.Cells(i + j, k)) *
100, "0") '% rank
End If

Next k

I have also tried Ar1 = Array("Crng4", "crng5", ... , "crng24")
and then
Round(AA.PercentRank(Shd.Range(Ar1(k-3)), Shd.Cells(i + j, k)) * 100,
"0") '% rank

but that didn't work either.

Regards

Andrew B
 
R

Ron Coderre

Using your example, the way to create a new named range is more like this:

ThisWorkbook.Names.Add Name:= "crng" & k, RefersTo:=Shd.Cells(i + j, k)

See if you can work with that.
 
A

Andrew B

Hi
Thanks for you help. I found that one of the ranges I was referring to
wasn't labelled correctly and so wasn't reerring to numbers.

Range("crng" & k) does work.

Thanks
 

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