Finding the last cell to enter a string and/or a sum formula
 

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

Finding the last cell to enter a string and/or a sum formula
 

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?

Finding the last cell to enter a string and/or a sum formula
 

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?

Finding the last cell to enter a string and/or a sum formula
 

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...

Finding the last cell to enter a string and/or a sum formula
 

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...

Finding the last cell to enter a string and/or a sum formula
 

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...