.cells help

[Carl Brehm]

Why does the following return an error 1004?

..Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount) & ":" & .Cells(200, columncount) & ")"""

With Range("weekending")(1, 1)
.Offset(0, (columncount)).Value = monthdate
'assigns weekending date to column heading on Worksheet("vendor_totals")
.Offset(1, (columncount)).Value = Sheets("sheet1").Range("h1").Value
'assigns sq footage value for week
.Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount) & ":" & .Cells(200, columncount) & ")"""
'assign formula SUM(current column row6: current column row 200)
End With

--
Carl Brehm
Lake Lafourche Bird House
Hebert, LA

Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Lovebirds, Cherry Head Conures
Prince of Whales

Wholesale Cages to Breeders & Pet Stores

 

[Bob Phillips]

Haven't tested it, but probably because you are using the default value property. Try this

Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount).Address & ":" & .Cells(200, columncount).Address & ")"""

and adjust the other one similarly

--

HTH

RP
(remove nothere from the email address if mailing direct)


Why does the following return an error 1004?

.Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount) & ":" & .Cells(200, columncount) & ")"""

With Range("weekending")(1, 1)
.Offset(0, (columncount)).Value = monthdate
'assigns weekending date to column heading on Worksheet("vendor_totals")
.Offset(1, (columncount)).Value = Sheets("sheet1").Range("h1").Value
'assigns sq footage value for week
.Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount) & ":" & .Cells(200, columncount) & ")"""
'assign formula SUM(current column row6: current column row 200)
End With

--
Carl Brehm
Lake Lafourche Bird House
Hebert, LA

Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Lovebirds, Cherry Head Conures
Prince of Whales

Wholesale Cages to Breeders & Pet Stores

 

[Carl Brehm]

Returns same 1004 not defined error
The other 2 .offset lines work fine.
Is there another way to go about this?

Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount) & ":" & .Cells(200, columncount) & ")"""

With Range("weekending")(1, 1)
.Offset(0, (columncount)).Value = monthdate
'assigns weekending date to column heading on Worksheet("vendor_totals")
.Offset(1, (columncount)).Value = Sheets("sheet1").Range("h1").Value
'assigns sq footage value for week
.Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount) & ":" & .Cells(200, columncount) & ")"""
'assign formula SUM(current column row6: current column row 200)
End With

--
Carl Brehm
Lake Lafourche Bird House
Hebert, LA

Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Lovebirds, Cherry Head Conures
Prince of Whales

Wholesale Cages to Breeders & Pet Stores
Haven't tested it, but probably because you are using the default value property. Try this

Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount).Address & ":" & .Cells(200, columncount).Address & ")"""

and adjust the other one similarly

 

[Bob Phillips]

I think you have too many quotes. Try this

.Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount).Address & ":" & .Cells(200, columncount).Address & ")"


--

HTH

RP
(remove nothere from the email address if mailing direct)


Why does the following return an error 1004?

.Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount) & ":" & .Cells(200, columncount) & ")"""

With Range("weekending")(1, 1)
.Offset(0, (columncount)).Value = monthdate
'assigns weekending date to column heading on Worksheet("vendor_totals")
.Offset(1, (columncount)).Value = Sheets("sheet1").Range("h1").Value
'assigns sq footage value for week
.Offset(2, (columncount)).Formula = "=SUM(" & .Cells(6, columncount) & ":" & .Cells(200, columncount) & ")"""
'assign formula SUM(current column row6: current column row 200)
End With

--
Carl Brehm
Lake Lafourche Bird House
Hebert, LA

Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Lovebirds, Cherry Head Conures
Prince of Whales

Wholesale Cages to Breeders & Pet Stores