Populating a Named Range...

[debartsa]

Hi Everybody,

Using Excel 97, I have a Range object that is Set to a Named Range on a
WorkSheet
e.g.

Dim Rng as Range

Set Rng = ThisWorkbook.Worksheets("MySheet").Range("Incidents")

"Incidents" is a named range that represents the first 10 rows of my
Worksheet and includes only Columns "A","B" and "D" of those 10 rows
(Columns C is excluded)
My problem is when I dynamically populate Rng with values the data ends up
in Columns "A", "B" and "C" (Column D should have contained data instead
Column C holds it)

Rng(j, i).Value = "SomeData"

Where j and i iteratively represent 10 Rows across 3 Columns (Columns "A",
"B" and "D")

Am I allowed to define a Range that skips a column?

Thanks for any help!
Sam

 

[Bob Phillips]

You are, but Rng(j,i) referes to a cell j columns and i rows from the first
cell in Rng. This is not necessarily a cell within Rng.

Here is an example to populate Rng

Set rng = Range("A1:A10,C1:C10")
i = 1: j = 1
For Each cell In rng
cell = i * j
i = i + 1
If i > 10 Then
i = 1
j = j + 1
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[debartsa]

Thanks Bob!

You are, but Rng(j,i) referes to a cell j columns and i rows from the first
cell in Rng. This is not necessarily a cell within Rng.

Here is an example to populate Rng

Set rng = Range("A1:A10,C1:C10")
i = 1: j = 1
For Each cell In rng
cell = i * j
i = i + 1
If i > 10 Then
i = 1
j = j + 1
End If
Next cell

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)