vba variable range in a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

fnwcanada@gmail.com - 08 Jun 2007 14:21 GMT

I am trying to enter a formula into a range of cells and in the
formula the row range also has to change. My error seems to be with
"range1" and it is not returning a cell but says "range1". Any
ideas???

Dim rw As Integer, range1 As Range, range2 As Range
For rw = 2 To 8
Set range1 = ActiveSheet.Cells(rw, 4)
Set range2 = ActiveSheet.Cells(rw, 6)
range2.formula = "=SUMPRODUCT(('271500.xls'!
category_271500= " & range1 & ")*('271500.xls'!amount_271500))"
Next rw

Reply to this Message

Barb Reinhardt - 08 Jun 2007 15:02 GMT

try using range1.address

> I am trying to enter a formula into a range of cells and in the
> formula the row range also has to change. My error seems to be with
[quoted text clipped - 8 lines]
> category_271500= " & range1 & ")*('271500.xls'!amount_271500))"
> Next rw

Reply to this Message

fnwcanada@gmail.com - 08 Jun 2007 15:10 GMT

On Jun 8, 11:02 am, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:

> try using range1.address
>
[quoted text clipped - 13 lines]
>
> - Show quoted text -

Worked perfect Barb.
Cheers,
Nathan Walsh

Reply to this Message