Loop through Rows & Columns for SUM() - Need help with Merged 2

[tqeonline]

I have the logic to loop through the Rows & Columns:

Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
'Loops through COLUMNS, then ROWS doing the math
    For y = 2 To LastRow 'ROWS
        For x = 2 To 4 'COLUMNS 4 = D

        Next x
    Next y

The data is pretty simple:

Row 1:[*merged1*][ 2 ]
Row 2:[*merged1*][*merged7*]
Row 3:[*merged1*][*merged7*]

So 1 is merged for three rows, then you have 2, and you have 7 merged with two rows.

The ask is that each cell count as 1 and if it is merged that the 1 is divided by the number of rows it is merged... i.e. 1 is merged 3 rows so it is .33 7 is .5.

I am needing to create a total:

Row 1: 1.33 (1+.33)
Row 2: .88 (.33+.5)
Row 3: .88 (.33+.5)

I understand how to do the summing of columns, the hard part for me is how to determine how something is merged and for how much.

I have seen:

range("A1").mergearea.address

But I can't figure out how to make that work for me.

Thoughts?


- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

DING DING DING!!!!

Sub Merge_Count()
    'Determines the last row
    Dim LastRow As Integer
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    'Loop through the Rows then Columns
    For y = 2 To LastRow  'ROWS
        For x = 2 To 4  'COLUMNS 4 = D
            If Cells(y, x).MergeArea.Count > 1 Then
                Total = Total + (1 / Cells(y, x).MergeArea.Count)
            ElseIf Trim(Cells(y, x).Value) <> "" Then
                Total = Total + 1
            End If
        Next x
        Cells(y, 1).Value = Total
        Total = 0
    Next y
End Sub

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

Also Skip you would be a good BA (Business Analyst) all about the details.

I stand corrected yet again as it should have been VERTICAL and not HORIZONTAL.

Are you in need of a job?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[SkipVought]

Also Skip you would be a good BA (Business Analyst) all about the details.

That is EXACTLY where I started and much of what I still do. The devil is in the details. That is why I "press toward the mark," in more than one way, if you get my drift.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}