SUM Macro

[arun.darra]

Hi,

I am a novice to the Macro world and need your help.
Can some one help me write a macro of the type:

=SUM("A" & =FIND_FIRST(column_name, value), "A" &
=FIND_LAST(column_name, value))

any help is appreatiated.

Thanks

 

[Guest]

If I interpret you request correctly, you want to find the first and last
values in a column of numbers and sum them. Following is 2 macros. First one
does not take into account column headers. 2nd amendment assumes column
headers and starts at row 2 to find the first value.

Sub SumColumnNoHeaders()

Dim firstRow As Long
Dim lastRow As Long
Dim column_Name As String
Dim sumValue As Double

column_Name = "A"

With ActiveSheet
'Assuming no column headers
If .Cells(1, column_Name) = "" Then
firstRow = .Cells(1, column_Name).End(xlDown).Row
Else
firstRow = 1
End If


lastRow = .Cells(.Rows.Count, column_Name).End(xlUp).Row
End With

sumValue = WorksheetFunction.Sum(Range(Cells(firstRow, column_Name), _
Cells(lastRow, column_Name)))

'alternative code method
'sumValue = WorksheetFunction.Sum(Range(column_Name & firstRow & _
":" & column_Name & lastRow))



End Sub


If you have column headers then replace the If/Else/End If lines with the
following:-

'With column headers present
If .Cells(2, column_Name) = "" Then
firstRow = .Cells(2, column_Name).End(xlDown).Row
Else
firstRow = 2
End If


Regards,

OssieMac

 

[arun.darra]

If I interpret you request correctly, you want to find the first and last
values in a column of numbers and sum them. Following is 2 macros. First one
does not take into account column headers. 2nd amendment assumes column
headers and starts at row 2 to find the first value.

Sub SumColumnNoHeaders()

Dim firstRow As Long
Dim lastRow As Long
Dim column_Name As String
Dim sumValue As Double

column_Name = "A"

With ActiveSheet
'Assuming no column headers
If .Cells(1, column_Name) = "" Then
firstRow = .Cells(1, column_Name).End(xlDown).Row
Else
firstRow = 1
End If

lastRow = .Cells(.Rows.Count, column_Name).End(xlUp).Row
End With

sumValue = WorksheetFunction.Sum(Range(Cells(firstRow, column_Name), _
Cells(lastRow, column_Name)))

'alternative code method
'sumValue = WorksheetFunction.Sum(Range(column_Name & firstRow & _
":" & column_Name & lastRow))

End Sub

If you have column headers then replace the If/Else/End If lines with the
following:-

'With column headers present
If .Cells(2, column_Name) = "" Then
firstRow = .Cells(2, column_Name).End(xlDown).Row
Else
firstRow = 2
End If

Regards,

OssieMac

Thank a lot for your response.... but requirement is something like
this:

The original table
ID value
1 10
1 10
2 10
2 10
2 10
3 10
3 10

I want to write a macro that will help me acrive this

ID value
1 10
1 10
20

2 10
2 10
2 10
30

3 10
3 10
20

I want a macro of type
=SUM(.....) that can be put in a cell... because the number of rows
cal increase or decrese dynamically.

Thanks

 

[Bill Renaud]

<<I want to write a macro that will help me derive this

ID value
1 10
1 10
20

2 10
2 10
2 10
30

3 10
3 10
20

I want a macro of type
=SUM(.....) that can be put in a cell... because the number of rows can
increase or decrease dynamically.>>

You might look at the DSUM worksheet function, but you will have to use
some cells to define the criteria you want. If you are trying to produce
the SUM for ALL groups of data rows, then I would suggest using a pivot
table.

 

[Bill Renaud]

Actually, I think the SUMIF worksheet function would work best in this
situation. Assume your data is in the following form:

ID value
1 10
1 10
2 10
2 10
2 10
3 10
3 10

=SUMIF(A2:A8,2,B2:B8)

....will sum all values in column $B where the cells in column $A equal 2.