I pull in an array of data and format it into a report.
Here's my problem:

In column H I need to insert the word "TOTAL" in the
second blank row below the last entry in the array.

In the same row, column J, I need to insert"=sum(j4:j
[whatever])" to sum the values in column J.

The number of rows can vary from 2 or 3 to several
thousand. Sometimes there are entries that take more than
one row for the data in the other columns, creatng empty
cells in H and J.

I've got everything else worked out except getting the
total in. Anyone have an idea or suggest a place to look?

Hi
you may post your existing code. Makes it easier to add the missing
parts

--
Regards
Frank Kabel
Frankfurt, Germany


Fred Butterfield wrote:
I pull in an array of data and format it into a report.
Here's my problem:

In column H I need to insert the word "TOTAL" in the
second blank row below the last entry in the array.

In the same row, column J, I need to insert"=sum(j4:j
[whatever])" to sum the values in column J.

The number of rows can vary from 2 or 3 to several
thousand. Sometimes there are entries that take more than
one row for the data in the other columns, creatng empty
cells in H and J.

I've got everything else worked out except getting the
total in. Anyone have an idea or suggest a place to look?

The following code insert TOTAL and the SUM function:

'======================
Dim r As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
r = ws.Cells(Rows.Count, 10).End(xlUp).Row + 2
ws.Cells(r, 8).Value = "TOTAL"
ws.Cells(r, 10).Formula = "=SUM(J4:J" & r - 2 & ")"
'=========================

Fred Butterfield wrote:
I pull in an array of data and format it into a report.
Here's my problem:

In column H I need to insert the word "TOTAL" in the
second blank row below the last entry in the array.

In the same row, column J, I need to insert"=sum(j4:j
[whatever])" to sum the values in column J.

The number of rows can vary from 2 or 3 to several
thousand. Sometimes there are entries that take more than
one row for the data in the other columns, creatng empty
cells in H and J.

I've got everything else worked out except getting the
total in. Anyone have an idea or suggest a place to look?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Fred

Try thnis

Code gets row number of last used cell in Column H


Dim LastRow As Long

LastRow = Cells(Rows.Count, "h") _
.End(xlUp).Row

Cells(LastRow + 2, "h").Value = "TOTAL"

Cells(LastRow + 2, "j").Value = _
"=sum(j4:j" & LastRow & ")

--
Message posted from http://www.ExcelForum.com