Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
tqeonline

tqeonline

MIS
Oct 5, 2009
304
US
I have the logic to loop through the Rows & Columns:
Code: 
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*]
Click to expand...

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)
Click to expand...

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:
Code: 
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
 
Sort by date Sort by votes


Merging is not at all conducive with certain kinds of summing. Actually, MERGE is a CURSE, that causes more problems than it helps.

Are you saying that:

1. A1:A3 is merged with a value of .33
2. B2:B3 is merged with a value of .5
3. B1 has a value of 1

So in actuality your data looks like this
[tt]
A | B |
+-----+------+
1| .33 | 1 |
| +------+
2| | .5 |
| | |
3| | |
+-----+------+
[/tt]
So what's the business case for this exercise?



Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
i wish I knew the business case... I am not "allowed" to see the data but was given that as the example. It relates to some HR sensitive data and I was given dummy data (disney characters).

your data is correct IF the .33 counts for rows 1 2 and 3... so if I did a sum of Col A it would be .99 (or 1) but if I did a sum of Row 1 it would be 1.33.

I would restate your quote though:

1. A1:A3 is merged with a value of .33 totaling 1
2. B2:B3 is merged with a value of .5 totaling 1
3. B1 has a value of 1

Since A1:A3 is 3 cells that gives each cell within it a .33 value.. B2:B3 is 2 cells giving it .5 --- the real data could have 10 cells all .10.

- 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
 
Upvote 0 Downvote


Why do the cells need to be merged?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote


and I would assume that these are not the only cells using this format, but many others, YES?

If so in what configuration: Vertical or horizontal and more specific detail regarding what needs to be done.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I don't have any control over the merging.. It will only be vertical within one column.

I need to run a macro that inserts a total column as the first column (column a) then it does a nested loop... where it will loop through each row and inside that loop each column.

So row 1... column b, column c, etc then row 2

If row 1 column b is merged with row 2 column b and that is it then row 1 column b = .5 then it moves to the rest of the columns and once it is done the column a is updated with the sum. Merges can be random throughout the data but it will always only be vertical.

you have a perfect example above but the real data would be larger. Where Row 1 = 1.33, 2 = .83, and 3 = .83



- 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
 
Upvote 0 Downvote


use this function on your sheet, just as you would any other function, or you can use it in any VBA procedure.
Code: 
Function SumMerged(rng As Range)
    Dim r As Range, rM As Range

    For Each r In rng
        Set rM = r.MergeArea

        If rM.Count > 1 Then
            SumMerged = SumMerged + rM.Cells(1, 1).Value
        Else
            SumMerged = SumMerged + r.Value
        End If

    Next
End Function


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I tried implementing your code and tried modifying it to fit what I need:
Code: 
Sub Merge_Count()
    'Determines the last row
    Dim LastRow As Integer
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    'Loop through the Rows then Columns
    Dim r As Range, rM As Range
    For y = 2 To LastRow 'ROWS
        For x = 2 To 4 'COLUMNS 4 = D
            If Cells(y, x).Value <> "" Then
                If Cells(y, x).MergeArea.Count > 1 Then
                    Total = Total + (Cells(y, x).Value / Cells(y, x).MergeArea.Count)
                Else
                    Total = Total + Cells(y, x).Value
                End If
            End If
        Next x
        Cells(y, 1).Value = Total
        Total = 0
    Next y
End Sub

I am getting a "Type Mismatch" here:
Code: 
Total = Total + (Cells(y, x).Value / Cells(y, x).MergeArea.Count)

What i'm trying to do is divide the value in the merged cell by the number of cells in the mergearea...

- 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
 
Upvote 0 Downvote
I'd replace this:
Total = Total + (Cells(y, x).Value / Cells(y, x).MergeArea.Count)
with this:
Total = Total + (Cells(y, x).MergeArea.Value / Cells(y, x).MergeArea.Count)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote

2.33 Happy Dopey Grumpy
1.33 merged^ Sleepy *blank
1.83 merged^ Bashful Sneezy
1.50 Doc merged^ *blank

So I figured out the issue (ID-10-T). I was trying to do totals on cells with text in them... The way the macro should work is IF there is text then the value is 1. If the text is merged then the value is 1/Merge Count. The sum is the total for that row.

So for row 1 the 2.33 = .33 (Happy) + 1 (Dopey) + 1 (Grumpy).

The issue is that the "merged^" cells show up as blanks.

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

Any ideas?

- 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
 
Upvote 0 Downvote



HUH???
The issue is that the "merged^" cells show up as blanks.
Click to expand...
That "issue" is what my function solves! That is what rMA is for!
Code: 
Sub Merge_Count()
    'Determines the last row
    Dim LastRow As Integer
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    Dim Total As Integer
    Total = 0
    
    'Loop through the Rows then Columns
    Dim r As Range, rM As Range
    For y = 2 To LastRow 'ROWS
       Total = Total + SumMerged(Range(Cells(y,"A"), Cells(y,"D")))
    Next y
End Sub



Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Code: 
Sub Merge_Count()
    'Determines the last row
    Dim LastRow As Integer
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    Dim Total As Integer
    Total = 0
    
    'Loop through the Rows then Columns
    Dim r As Range, rM As Range
    For y = 2 To LastRow 'ROWS
       Total = Total + SumMerged(Range(Cells(y, "A"), Cells(y, "D")))
    Next y
End Sub
Function SumMerged(rng As Range)
    Dim r As Range, rM As Range

    For Each r In rng
        Set rM = r.MergeArea

        If rM.Count > 1 Then
            SumMerged = SumMerged + rM.Cells(1, 1).Value
        Else
            SumMerged = SumMerged + r.Value
        End If

    Next
End Function

It is throwing a type mismatch because the cells contain text not values...

Each cell counts as 1, if it is merged it counts as 1/range.

What am i missing?

- 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
 
Upvote 0 Downvote


Why do your cells contain TEXT? Is that what the DOPY thing was about? Why did you not reveal this DOPY thing originally?

You originlly implied an ARITHMETIC SUM of values in a range including merged cells, that "inherit" the NUMERIC VALUE thru the merge range: YES???

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Yes, hince the ID-10-T thing I was talking about in one of my posts. My mistake, if this is unsolveable I can live with that.

- 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
 
Upvote 0 Downvote


What does ID-10-T mean???

You jsut threw that out is a previous post out-of-the-blue.

You know that we cannot see into your head or what's on your screen. It is your job to CLEARLY, CONCISELY & COMPLETELY specify your requirements, if you want a cogent response that is anywhere approching your needs.

Please FOCUS like a laser.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
ID-10-T... = Idiot. It was a mistake of mine that I didn't include that "1" thing in the description.


The only difference in my original request and the perfect solution you provided is that I need it to just assume 1 instead of the value within the cell.



- 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
 
Upvote 0 Downvote

You need to be "perfectly clear!"
The only difference in my original request and the perfect solution you provided is that I need it to just assume 1 instead of the value within the cell.
Click to expand...
What does that mean?

Let's start from "In the beginning."

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Ok lets try this again, thanks for working with me.

I have a spreadsheet that has columns B to D with TEXT values in them. The rows are going to be 2 to LastRow (dimmed as the last used row).

If the Cell has a value in it, that cell is considered a value of 1, the only exception is if that cell is merged (horizontally). Then the value is 1 / the number of cells in the merge.

Ex. B2,B3, and B4 are merged then the value = 1/3 which is .33.

Column A is the Total column which we are trying to populate. Given the example below:
2.33 Happy Dopey Grumpy
1.33 merged^ Sleepy *blank
1.83 merged^ Bashful Sneezy
1.50 Doc merged^ *blank
Click to expand...

AS you can see I have summed Row 1 = 2.33. That is made up of Happy (1/3)+Dopey (1) + Grumpy (1) = 2.33.

If the cell is blank, ignore it.

Here is the logic I currently have. The only alteration is that it doesn't need to sum the cell values, it needs to think of the values as 1 and then the division of the merged cells (1/# of cells in merge). One issue I ran into was that if the cell is merged then only the first cellin the merge contains teh value, all others are sen as blanks.

Code: 
Sub Merge_Count()
    'Determines the last row
    Dim LastRow As Integer
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    Dim Total As Integer
    Total = 0
    
    'Loop through the Rows then Columns
    Dim r As Range, rM As Range
    For y = 2 To LastRow 'ROWS
       Total = Total + SumMerged(Range(Cells(y, "A"), Cells(y, "D")))
    Next y
End Sub
Function SumMerged(rng As Range)
    Dim r As Range, rM As Range

    For Each r In rng
        Set rM = r.MergeArea

        If rM.Count > 1 Then
            SumMerged = SumMerged + rM.Cells(1, 1).Value
        Else
            SumMerged = SumMerged + r.Value
        End If

    Next
End Function

- 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
 
Upvote 0 Downvote


Right off, you are not consistent!
the only exception is if that cell is merged (horizontally). Then the value is 1 / the number of cells in the merge.
Click to expand...
Ex. B2,B3, and B4 are merged then the value = 1/3 which is .33.
Click to expand...
B2:B4 is a VERTICAL range, not a HORIZONTAL range!!!

So, as a result, I DOUBT EVERYTHING that follows!!!

Let's start again, and PLEASE, PLEASE, PLEASE carefully consider every single statement that you make and be CLEAR, CONCISE and COMPLETE.



Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
What about this ?
Code: 
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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
404
remeng
remeng
sxschech
  • Locked
  • Question
For Each Loop works once then gives error 91 2
Replies
19
Views
680
sxschech
sxschech
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
423
Andrzejek
Andrzejek
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
480
combo
combo
davefish
  • Locked
  • Question
Looping through a word doc to delete rows
Replies
7
Views
298
macropod
macropod

Part and Inventory Search

Sponsor

Back
Top