I am writing some code, and I need to use an array formula. I am
getting the strangest error, however, based on how I define the range
object that the FormulaR1C1 acts on. Here is an example: (Note: rst(0)
is a recordset object. PERIOD is a constant int that tells what columns
the PERIOD measurement is in)


---Example #1---

Sheets(i).Range("m2:m" & rst(0).RecordCount + 1). _
FormulaR1C1 = "=countif(periodRange,RC[" & PERIOD - _
rst(0).Fields.Count & "])"

'This is what I want to do, however, I DON'T want to refer to the
column by "m", 'since I have a variable with "13" in it that I want to
use instead. Note, the above code works PERFECTLY. The problem is the
"m" references.

---Example #2---

Sheets(i).Range("m2:m200"). _
FormulaR1C1 = "=countif(periodRange,RC[" & PERIOD _
- rst(0).Fields.Count & "])"

This code works fine also. Strangely enough, it only fills to 192ish (+
or - five records) for each sheet. I don't know why that is -- and its
not the problem -- but I thought I'd mention it just in case.

---Example #3---

Sheets(i).Range(Cells(2, 13), Cells(500, 13)). _
FormulaR1C1 = "=countif(periodRange,RC[" & PERIOD _
- rst(0).Fields.Count & "])"

This code DOES NOT work. It *should* be the same as #2, but I get an
"application-defined or object defined error". '

---Example #4---

Sheets(i).Range(Cells(2,rst(0).Fields.Count),_
Cells(rst(0).RecordCount + 1,rst(0).Fields.Count)) _
.FormulaR1C1 = "=countif(periodRange,RC[" & _
PERIOD - rst(0).Fields.Count & "])"

This is the code that I would LIKE to have. This should perfectly
replace Example #1. Again, however, I get application/object defined
error.
-----

Thanks in advance for your help. This is driving me nuts.

With Sheets(i)
.Range(.Cells(2, 13), .Cells(500, 13)). _
FormulaR1C1 = "=countif(periodRange,RC[" & PERIOD _
- rst(0).Fields.Count & "])"
End With

You need to qualify Cells as well.

--
Regards,
Tom Ogilvy


"aspenbordr" wrote in message
oups.com...
I am writing some code, and I need to use an array formula. I am
getting the strangest error, however, based on how I define the range
object that the FormulaR1C1 acts on. Here is an example: (Note: rst(0)
is a recordset object. PERIOD is a constant int that tells what columns
the PERIOD measurement is in)


---Example #1---

Sheets(i).Range("m2:m" & rst(0).RecordCount + 1). _
FormulaR1C1 = "=countif(periodRange,RC[" & PERIOD - _
rst(0).Fields.Count & "])"

'This is what I want to do, however, I DON'T want to refer to the
column by "m", 'since I have a variable with "13" in it that I want to
use instead. Note, the above code works PERFECTLY. The problem is the
"m" references.

---Example #2---

Sheets(i).Range("m2:m200"). _
FormulaR1C1 = "=countif(periodRange,RC[" & PERIOD _
- rst(0).Fields.Count & "])"

This code works fine also. Strangely enough, it only fills to 192ish (+
or - five records) for each sheet. I don't know why that is -- and its
not the problem -- but I thought I'd mention it just in case.

---Example #3---

Sheets(i).Range(Cells(2, 13), Cells(500, 13)). _
FormulaR1C1 = "=countif(periodRange,RC[" & PERIOD _
- rst(0).Fields.Count & "])"

This code DOES NOT work. It *should* be the same as #2, but I get an
"application-defined or object defined error". '

---Example #4---

Sheets(i).Range(Cells(2,rst(0).Fields.Count),_
Cells(rst(0).RecordCount + 1,rst(0).Fields.Count)) _
.FormulaR1C1 = "=countif(periodRange,RC[" & _
PERIOD - rst(0).Fields.Count & "])"

This is the code that I would LIKE to have. This should perfectly
replace Example #1. Again, however, I get application/object defined
error.
-----

Thanks in advance for your help. This is driving me nuts.

Perfect! Thanks for the help. Hard to believe I missed that one.

-Andrew