Macro help
 

I am trying to create a macro that will add the dollar values in a specific
column. The problem I am having is that the number of vaules in that column
vary depending on the worksheet. Ex. Worksheet 1 has 20 dollar values while
worksheet 2 has 60 dollar values.

How can I create a macro that will, autosense if you will, the number of
values in a specifc column and adjust the formula to meet the needs of
different spreadsheets?

Macro help
 

Since you don't fully explain, I'm doing some guessing here. This will go
thru all worksheets after sheet1 and total c2:c last row and put the total
in c1

Sub totalvariablecolumn()
For i = 2 To Worksheets.Count
With Sheets(i)
lr = .Cells(rows.Count, "c").End(xlUp).Row
mysum = Application.Sum(.Range("c2:c" & lr))
'MsgBox mysum
..Cells(1, "c") = mysum
End With
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"igotquestions" wrote in message
...
I am trying to create a macro that will add the dollar values in a specific
column. The problem I am having is that the number of vaules in that
column
vary depending on the worksheet. Ex. Worksheet 1 has 20 dollar values
while
worksheet 2 has 60 dollar values.

How can I create a macro that will, autosense if you will, the number of
values in a specifc column and adjust the formula to meet the needs of
different spreadsheets?

Macro help
 

Forgive me I am new to this. My situation is:
I use Access to keep job info. One of my forms gives me my parts with dollar
amounts for each job. I analyze this form in Excel. The dollar amounts always
start at cell O4 and proceed down until all of the parts are covered. This
length varies depending on the amount of parts that are in each job. I am
trying to create a macro that I can use to give the the total dollar amount
for all of the parts starting at cell O4 and outputting the total to cell D2.
The variable is the ending cell for the parts dollars. How would I tell the
macro to add everything from O4 down to last dollar amount?

This may be way above my head to comprehend so any help would greatly be
appreciated

"Don Guillett" wrote:

Since you don't fully explain, I'm doing some guessing here. This will go
thru all worksheets after sheet1 and total c2:c last row and put the total
in c1

Sub totalvariablecolumn()
For i = 2 To Worksheets.Count
With Sheets(i)
lr = .Cells(rows.Count, "c").End(xlUp).Row
mysum = Application.Sum(.Range("c2:c" & lr))
'MsgBox mysum
..Cells(1, "c") = mysum
End With
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"igotquestions" wrote in message
...
I am trying to create a macro that will add the dollar values in a specific
column. The problem I am having is that the number of vaules in that
column
vary depending on the worksheet. Ex. Worksheet 1 has 20 dollar values
while
worksheet 2 has 60 dollar values.

How can I create a macro that will, autosense if you will, the number of
values in a specifc column and adjust the formula to meet the needs of
different spreadsheets?

Macro help
 

Hi,

If Don will excuse me for altering his macro try this. Right click your
sheet tab, view code and paste this in and run it

Sub totalvariablecolumn()
With Sheets("Sheet1") 'Change to suit
lr = .Cells(Rows.Count, "O").End(xlUp).Row
mysum = Application.Sum(.Range("O4:O" & lr))
End With
Range("D2").Value = mysum
End Sub

Mike

"igotquestions" wrote:

Forgive me I am new to this. My situation is:
I use Access to keep job info. One of my forms gives me my parts with dollar
amounts for each job. I analyze this form in Excel. The dollar amounts always
start at cell O4 and proceed down until all of the parts are covered. This
length varies depending on the amount of parts that are in each job. I am
trying to create a macro that I can use to give the the total dollar amount
for all of the parts starting at cell O4 and outputting the total to cell D2.
The variable is the ending cell for the parts dollars. How would I tell the
macro to add everything from O4 down to last dollar amount?

This may be way above my head to comprehend so any help would greatly be
appreciated

"Don Guillett" wrote:

Since you don't fully explain, I'm doing some guessing here. This will go
thru all worksheets after sheet1 and total c2:c last row and put the total
in c1

Sub totalvariablecolumn()
For i = 2 To Worksheets.Count
With Sheets(i)
lr = .Cells(rows.Count, "c").End(xlUp).Row
mysum = Application.Sum(.Range("c2:c" & lr))
'MsgBox mysum
..Cells(1, "c") = mysum
End With
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"igotquestions" wrote in message
...
I am trying to create a macro that will add the dollar values in a specific
column. The problem I am having is that the number of vaules in that
column
vary depending on the worksheet. Ex. Worksheet 1 has 20 dollar values
while
worksheet 2 has 60 dollar values.

How can I create a macro that will, autosense if you will, the number of
values in a specifc column and adjust the formula to meet the needs of
different spreadsheets?

Macro help
 

Thank You so much Mike and Don for your help.
Mike it worked perfectly!

"Mike H" wrote:

Hi,

If Don will excuse me for altering his macro try this. Right click your
sheet tab, view code and paste this in and run it

Sub totalvariablecolumn()
With Sheets("Sheet1") 'Change to suit
lr = .Cells(Rows.Count, "O").End(xlUp).Row
mysum = Application.Sum(.Range("O4:O" & lr))
End With
Range("D2").Value = mysum
End Sub

Mike

"igotquestions" wrote:

Forgive me I am new to this. My situation is:
I use Access to keep job info. One of my forms gives me my parts with dollar
amounts for each job. I analyze this form in Excel. The dollar amounts always
start at cell O4 and proceed down until all of the parts are covered. This
length varies depending on the amount of parts that are in each job. I am
trying to create a macro that I can use to give the the total dollar amount
for all of the parts starting at cell O4 and outputting the total to cell D2.
The variable is the ending cell for the parts dollars. How would I tell the
macro to add everything from O4 down to last dollar amount?

This may be way above my head to comprehend so any help would greatly be
appreciated

"Don Guillett" wrote:

Since you don't fully explain, I'm doing some guessing here. This will go
thru all worksheets after sheet1 and total c2:c last row and put the total
in c1

Sub totalvariablecolumn()
For i = 2 To Worksheets.Count
With Sheets(i)
lr = .Cells(rows.Count, "c").End(xlUp).Row
mysum = Application.Sum(.Range("c2:c" & lr))
'MsgBox mysum
..Cells(1, "c") = mysum
End With
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"igotquestions" wrote in message
...
I am trying to create a macro that will add the dollar values in a specific
column. The problem I am having is that the number of vaules in that
column
vary depending on the worksheet. Ex. Worksheet 1 has 20 dollar values
while
worksheet 2 has 60 dollar values.

How can I create a macro that will, autosense if you will, the number of
values in a specifc column and adjust the formula to meet the needs of
different spreadsheets?