I am working on a macro that finds the last cell in the worksheet and enters
a specific number. The next column needs to be totaled at the last cell.
How can I incorporate a formula in the macro that will put the sum in the
cell for me?
--
Thanks a bunch!
rojobrown

Last cell in a worksheet is bottom right cell of data range.

Could be in Column A if that is the only column in use or column IV if you go
that far

What do you consider as the "next column"?

Adjacent to the right?

Or is this a typo and you want to sum the column in which you just placed the
number?

Did you mean "last cell in a specific column"?

Please provide more info and maybe post the code you already have.


Gord Dibben MS Excel MVP


On Thu, 28 Sep 2006 11:51:01 -0700, rojobrown
wrote:

I am working on a macro that finds the last cell in the worksheet and enters
a specific number. The next column needs to be totaled at the last cell.
How can I incorporate a formula in the macro that will put the sum in the
cell for me?

Example: In column A there are account numbers in rows 1-538. I would like
to put a specific account number in A539.

In column B there are amounts in rows 1-538. I would like to sum these and
place the total in B539.

The number of rows changes everytime...
--
Thanks a bunch!
rojobrown


"Gord Dibben" wrote:

Last cell in a worksheet is bottom right cell of data range.

Could be in Column A if that is the only column in use or column IV if you go
that far

What do you consider as the "next column"?

Adjacent to the right?

Or is this a typo and you want to sum the column in which you just placed the
number?

Did you mean "last cell in a specific column"?

Please provide more info and maybe post the code you already have.


Gord Dibben MS Excel MVP


On Thu, 28 Sep 2006 11:51:01 -0700, rojobrown
wrote:

I am working on a macro that finds the last cell in the worksheet and enters
a specific number. The next column needs to be totaled at the last cell.
How can I incorporate a formula in the macro that will put the sum in the
cell for me?

Maybe this for starters.

Sub test()
Dim acctnum As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
acctnum = InputBox("enter a number")
Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng.Value = acctnum
Set rng1 = Range("B1", Range("B" & Rows.Count). _
End(xlUp).Address)
Set rng2 = rng1.Offset(rng1.Rows.Count, 0).Resize(1, 1)
rng2.Formula = "=Sum(" & rng1.Address & ")"
End Sub


Gord Dibben MS Excel MVP

On Thu, 28 Sep 2006 13:15:02 -0700, rojobrown
wrote:

Example: In column A there are account numbers in rows 1-538. I would like
to put a specific account number in A539.

In column B there are amounts in rows 1-538. I would like to sum these and
place the total in B539.

The number of rows changes everytime...

This is an example of how I would like the file to look:

Column A Column B
123456 5.00
123456 5.00
123456 5.00
ABCDEFG 15.00

The ABCDEFG is the account I would like to add at the bottom of the file.
The 15.00 is the column that I would like to sum. The issue I am running
into is that the row changes every time. This is what I was trying to use
but it placed the account number in every row under column A. I didn't know
where to even begin for the sum formula..

Range("A2").Select
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("A2").Resize(LastRow - 1).FormulaR1C1 = "4651020098"

--
Thanks a bunch!
rojobrown


"Gord Dibben" wrote:

Maybe this for starters.

Sub test()
Dim acctnum As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
acctnum = InputBox("enter a number")
Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng.Value = acctnum
Set rng1 = Range("B1", Range("B" & Rows.Count). _
End(xlUp).Address)
Set rng2 = rng1.Offset(rng1.Rows.Count, 0).Resize(1, 1)
rng2.Formula = "=Sum(" & rng1.Address & ")"
End Sub


Gord Dibben MS Excel MVP

On Thu, 28 Sep 2006 13:15:02 -0700, rojobrown
wrote:

Example: In column A there are account numbers in rows 1-538. I would like
to put a specific account number in A539.

In column B there are amounts in rows 1-538. I would like to sum these and
place the total in B539.

The number of rows changes everytime...

Any suggestions for the below?
--
Thanks a bunch!
rojobrown


"rojobrown" wrote:

This is an example of how I would like the file to look:

Column A Column B
123456 5.00
123456 5.00
123456 5.00
ABCDEFG 15.00

The ABCDEFG is the account I would like to add at the bottom of the file.
The 15.00 is the column that I would like to sum. The issue I am running
into is that the row changes every time. This is what I was trying to use
but it placed the account number in every row under column A. I didn't know
where to even begin for the sum formula..

Range("A2").Select
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("A2").Resize(LastRow - 1).FormulaR1C1 = "4651020098"

--
Thanks a bunch!
rojobrown


"Gord Dibben" wrote:

Maybe this for starters.

Sub test()
Dim acctnum As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
acctnum = InputBox("enter a number")
Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng.Value = acctnum
Set rng1 = Range("B1", Range("B" & Rows.Count). _
End(xlUp).Address)
Set rng2 = rng1.Offset(rng1.Rows.Count, 0).Resize(1, 1)
rng2.Formula = "=Sum(" & rng1.Address & ")"
End Sub


Gord Dibben MS Excel MVP

On Thu, 28 Sep 2006 13:15:02 -0700, rojobrown
wrote:

Example: In column A there are account numbers in rows 1-538. I would like
to put a specific account number in A539.

In column B there are amounts in rows 1-538. I would like to sum these and
place the total in B539.

The number of rows changes everytime...