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

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.
--
Regards,
Ron


"Andrew B" wrote:

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

Hi,

Round(AA.PercentRank(Shd.Range("crng" & k), Shd.Cells(i + j, k)) *

HTH

"Andrew B" wrote:

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

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

Andrew B wrote:
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