I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!

hi David,

Range("f1").Select
Range(Selection, Selection.End(xlDown)).Select


hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"David B" escreveu:

I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!

Hello David,

This should give you the sum of the column you are needing.

Let A1 = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))

then

= G1 - A1 + G2

Feel free to change the cell to which you allocate the sum.

Good Luck
Richard

"David B" wrote:

I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!

Richard,

Thanks for your reply. When I put in this code (I've changed cell A1 to G2
in my spreadsheet):

Let G2 = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))

I get a compile error: Variable not defined.

sorry, but I'm a rookie at this..

"Richard M Burton" wrote:

Hello David,

This should give you the sum of the column you are needing.

Let A1 = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))

then

= G1 - A1 + G2

Feel free to change the cell to which you allocate the sum.

Good Luck
Richard

"David B" wrote:

I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!

Try this macro which places a formula at the bottom of used range in column F

Sub SumupF()
Dim rng As Range
Set rng = Range("F11", Range("F" & Rows.Count). _
End(xlUp).Address)
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Formula = "=G1 - Sum(" & rng.Address & ") + G2"
End Sub

rng1 can be any cell you want.

Could be Set rng1 = Range("A1")


Gord Dibben MS Excel MVP

On Wed, 20 Sep 2006 06:47:02 -0700, David B
wrote:

I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!

Gord,

can you help me tweak this code so that it puts the sum of the column F
range into cell G3? I'll then have a formula in G5 that will be =G2-G3+G4

Thanks....

"Gord Dibben" wrote:

Try this macro which places a formula at the bottom of used range in column F

Sub SumupF()
Dim rng As Range
Set rng = Range("F11", Range("F" & Rows.Count). _
End(xlUp).Address)
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Formula = "=G1 - Sum(" & rng.Address & ") + G2"
End Sub

rng1 can be any cell you want.

Could be Set rng1 = Range("A1")


Gord Dibben MS Excel MVP

On Wed, 20 Sep 2006 06:47:02 -0700, David B
wrote:

I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!

Sub SumupF()
Dim rng As Range
Set rng = Range("F11", Range("F" & Rows.Count). _
End(xlUp).Address)
Set rng1 = Range("G3")
rng1.Formula = "=Sum(" & rng.Address & ")"
End Sub


Gord

On Fri, 22 Sep 2006 07:51:03 -0700, David B
wrote:

Gord,

can you help me tweak this code so that it puts the sum of the column F
range into cell G3? I'll then have a formula in G5 that will be =G2-G3+G4

Thanks....

"Gord Dibben" wrote:

Try this macro which places a formula at the bottom of used range in column F

Sub SumupF()
Dim rng As Range
Set rng = Range("F11", Range("F" & Rows.Count). _
End(xlUp).Address)
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Formula = "=G1 - Sum(" & rng.Address & ") + G2"
End Sub

rng1 can be any cell you want.

Could be Set rng1 = Range("A1")


Gord Dibben MS Excel MVP

On Wed, 20 Sep 2006 06:47:02 -0700, David B
wrote:

I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!

Sorry David,

I have been off line for a while.

I tyhink you probably have this licked by now but just in case:

Sum = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))
Let Range("g2").Value = Sum

This would work.
Good luck
"David B" wrote:

Richard,

Thanks for your reply. When I put in this code (I've changed cell A1 to G2
in my spreadsheet):

Let G2 = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))

I get a compile error: Variable not defined.

sorry, but I'm a rookie at this..

"Richard M Burton" wrote:

Hello David,

This should give you the sum of the column you are needing.

Let A1 = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))

then

= G1 - A1 + G2

Feel free to change the cell to which you allocate the sum.

Good Luck
Richard

"David B" wrote:

I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!

Richard,

Thanks for your help. I've been playing with lot's of code, so this is
helpful!

"Richard M Burton" wrote:

Sorry David,

I have been off line for a while.

I tyhink you probably have this licked by now but just in case:

Sum = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))
Let Range("g2").Value = Sum

This would work.
Good luck
"David B" wrote:

Richard,

Thanks for your reply. When I put in this code (I've changed cell A1 to G2
in my spreadsheet):

Let G2 = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))

I get a compile error: Variable not defined.

sorry, but I'm a rookie at this..

"Richard M Burton" wrote:

Hello David,

This should give you the sum of the column you are needing.

Let A1 = Application.WorksheetFunction.Sum(Range("F11",
Range("F11").End(xlDown)))

then

= G1 - A1 + G2

Feel free to change the cell to which you allocate the sum.

Good Luck
Richard

"David B" wrote:

I'm new to using VBA, and am trying to create some code to run a daily macro.
The element of the code I'm stuck on is this: I import a daily excel sheet
where I need to have a simple formula that is:

=G1-Sum of "Range"+G2

The "Range" I need to define always starts with cell F11, and runs down the
F Column a variable number of cells. If I do this manually, I choose F11,
then hit Ctrl+shift+down arrow to select the range to the end of the data,
then apply the SUM function to that range:

So 2 questions:

1.what is the code the define the Range from F11 to the end of the data,
2. What is the construct of the formula (above) to include the SUM of that
Range?

Thanks for any help you can provide!