if for merged cells
 

Hi all.
I want to make an if function to look for merged cells, and then add the
contents of another cell related to the merged cells.
I.e.,
if( c2:c_n is merged)
sum(d2:d_n)

where _n is the last cell of the merged/summed group.
How can I perform this function?
part of me is thinking a UDF, but since I'm not entirely familiar with them,
I would appreciate some direction.
Hope everyone has a great T-day weekend.
Thanks.

if for merged cells
 

' This sub may be a start.
' It checks each cell in UsedRange.
' If C2:C4 are merged, it gets a hit
' on the merged cells 3 times.
' But it answers your example,
' giving =SUM($D$2:$D$4).

Sub Sub1()
Dim zCell As Range, iRowA&, iRowZ&, iCol%
For Each zCell In ActiveSheet.UsedRange
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowA = zCell.Row
iRowZ = iRowA + zCell.MergeArea.Rows.Count - 1
iCol = zCell.Column + 1
zCell.Formula = "=sum(" & _
Cells(iRowA, iCol).Address & _
":" & _
Cells(iRowZ, iCol).Address & _
")"
End If
Next zCell
End Sub ' D-C Dave

SteveDB1 wrote:
Hi all.
I want to make an if function to look for merged cells, and then add the
contents of another cell related to the merged cells.
I.e.,
if( c2:c_n is merged)
sum(d2:d_n)

where _n is the last cell of the merged/summed group.
How can I perform this function?
part of me is thinking a UDF, but since I'm not entirely familiar with them,
I would appreciate some direction.
Hope everyone has a great T-day weekend.
Thanks.

if for merged cells
 

Dave,
It appears to work. Thank you.
Next question. I noticed that it places the result in the merged area.
I would like to have the value placed where where I choose....
As previously stated, I'm looking at column C to determine if it's merged or
not (it will always have text in it.), and if so, to have values located in
column D to be summed over in another column/cell.

I want the sum valued results in column e. So, how would I place it there?

The UsedRange element, can I present that to look in a specific
column/range? if so, how would I write that statement? I ask that because my
worksheet will have 10 to 12 columns, and I only want it looking at the
merged rows of one specific column.

Again, thank you for your assistance.





"Dave D-C" wrote:

' This sub may be a start.
' It checks each cell in UsedRange.
' If C2:C4 are merged, it gets a hit
' on the merged cells 3 times.
' But it answers your example,
' giving =SUM($D$2:$D$4).

Sub Sub1()
Dim zCell As Range, iRowA&, iRowZ&, iCol%
For Each zCell In ActiveSheet.UsedRange
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowA = zCell.Row
iRowZ = iRowA + zCell.MergeArea.Rows.Count - 1
iCol = zCell.Column + 1
zCell.Formula = "=sum(" & _
Cells(iRowA, iCol).Address & _
":" & _
Cells(iRowZ, iCol).Address & _
")"
End If
Next zCell
End Sub ' D-C Dave

SteveDB1 wrote:
Hi all.
I want to make an if function to look for merged cells, and then add the
contents of another cell related to the merged cells.
I.e.,
if( c2:c_n is merged)
sum(d2:d_n)

where _n is the last cell of the merged/summed group.
How can I perform this function?
part of me is thinking a UDF, but since I'm not entirely familiar with them,
I would appreciate some direction.
Hope everyone has a great T-day weekend.
Thanks.

if for merged cells
 

Thanks Dave.
Bingo, got it!!!!
Thank you very much.
Have a great T-day/weekend.
Best.

"Dave D-C" wrote:

The major change here is the iRowZ = statement.

Sub Sub1()
' I only want it looking at the merged rows of column C.
Const iColMerge = 3
' values located in column D to be summed
Const iColFm = 4
' I want the sum valued results in column e
Const iColTo = 5
Dim zCell As Range, iRowV&, iRowZ&, iRowN&
' to find last used cell in column
iRowZ = Cells(Rows.Count, iColMerge).End(xlUp).Row
iRowV = 1
Do While iRowV <= iRowZ
Set zCell = Cells(iRowV, iColMerge)
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowN = iRowV + zCell.MergeArea.Rows.Count - 1
Cells(iRowV, iColTo).Formula = "=sum(" & _
Cells(iRowV, iColFm).Address & _
":" & _
Cells(iRowN, iColFm).Address & _
")"
iRowV = iRowN + 1 '
Else
iRowV = iRowV + 1
End If
Loop
End Sub ' D-C Dave

SteveDB1 wrote:
Dave,
It appears to work. Thank you.
Next question. I noticed that it places the result in the merged area.
I would like to have the value placed where where I choose....
As previously stated, I'm looking at column C to determine if it's merged or
not (it will always have text in it.), and if so, to have values located in
column D to be summed over in another column/cell.

I want the sum valued results in column e. So, how would I place it there?

The UsedRange element, can I present that to look in a specific
column/range? if so, how would I write that statement? I ask that because my
worksheet will have 10 to 12 columns, and I only want it looking at the
merged rows of one specific column.

Again, thank you for your assistance.

"Dave D-C" wrote:
' This sub may be a start.
' It checks each cell in UsedRange.
' If C2:C4 are merged, it gets a hit
' on the merged cells 3 times.
' But it answers your example,
' giving =SUM($D$2:$D$4).

Sub Sub1()
Dim zCell As Range, iRowA&, iRowZ&, iCol%
For Each zCell In ActiveSheet.UsedRange
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowA = zCell.Row
iRowZ = iRowA + zCell.MergeArea.Rows.Count - 1
iCol = zCell.Column + 1
zCell.Formula = "=sum(" & _
Cells(iRowA, iCol).Address & _
":" & _
Cells(iRowZ, iCol).Address & _
")"
End If
Next zCell
End Sub ' D-C Dave

SteveDB1 wrote:
Hi all.
I want to make an if function to look for merged cells, and then add the
contents of another cell related to the merged cells.
I.e.,
if( c2:c_n is merged)
sum(d2:d_n)

where _n is the last cell of the merged/summed group.
How can I perform this function?
part of me is thinking a UDF, but since I'm not entirely familiar with them,
I would appreciate some direction.
Hope everyone has a great T-day weekend.
Thanks