Macro to sum mixed ranges using xldown

Sarah333

New Member
Joined
May 30, 2012
Messages
10
I am trying to create a security report, and each user has multiple entity security and may have different role security which is the far right column "Security Codes", this needs to be matched to a role so the sum of the security codes = Role. therefore I need a macro to go down and sum the rows which apply to each users:
user 1 = (sum Row 1:5)
user 2 = (sum Row 6:9) etc

user1Security code resultSecurity codes
row1user16361
row2
row3 2
row4
row5
row6user243
row7
row8 1
row9
row10user364
row11
row12 2

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>


I thougth i could utilise some existing code that removes repeated members so the user only shows once, but I'm rusty with VBA and not sure how to change the Active cell select range to use the correct column offset when using the xldown on the username column

thanks very much in advance
Sarah

existing script
-----------------
Application.Calculation = xlCalculationManual
Range("AE5").Select
Selectme = ActiveCell.Value
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value = ""


If ActiveCell.Value = Selectme Then
Selection.Clear

Else: Selectme = ActiveCell.Value

End If
ActiveCell.Offset(1, 0).Select
Loop

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

hurgadion

Active Member
Joined
Mar 19, 2010
Messages
426
Hi,
try to use the macro:
Code:
Sub SumSum()
Dim tbl(), a&, i&, x&

a = Cells(Rows.Count, 3).End(xlUp).Row

For i = 1 To a
  If Cells(i, 1).Value <> "" Then
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = i
  End If
Next i

x = x + 1
ReDim Preserve tbl(1 To x)
tbl(x) = a + 1

For i = 1 To UBound(tbl) - 1
   Cells(tbl(i), 5).Value = Application.Sum(Range("C" & tbl(i) & ":C" & tbl(i + 1) - 1))
Next i

End Sub
Best regards.
 

Sarah333

New Member
Joined
May 30, 2012
Messages
10
thanks I will give that a go


Hi,
try to use the macro:
Code:
Sub SumSum()
Dim tbl(), a&, i&, x&

a = Cells(Rows.Count, 3).End(xlUp).Row

For i = 1 To a
  If Cells(i, 1).Value <> "" Then
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = i
  End If
Next i

x = x + 1
ReDim Preserve tbl(1 To x)
tbl(x) = a + 1

For i = 1 To UBound(tbl) - 1
   Cells(tbl(i), 5).Value = Application.Sum(Range("C" & tbl(i) & ":C" & tbl(i + 1) - 1))
Next i

End Sub
Best regards.
 

Sarah333

New Member
Joined
May 30, 2012
Messages
10
thanks I will give that a go


This doesnt quite work, the number of rows between users is different each time so the xldown need to first determine the number of rows to sum.

so to clarify the sum needs to be in column security code result. the sum is adding column security codes and the macro needs to determin the number of rows to sum by using the xldown feature on the username column as this is different for every user, the code in the far right column only shows a value for each change in security group and if they have more than one group this affects the overall security role detrmined by a lookup (not in this macro)

so it needs to somehow store the row with the first username for the start of the sum (to be in column security result which adds Security code column), do an xldown to the next username and the row above would be the end of the sum.

I hope this explains my requirement more clearly, appreciate your help
Sarah
 

hurgadion

Active Member
Joined
Mar 19, 2010
Messages
426

ADVERTISEMENT

I need an example with Data, my English is good not enough now, best regards.
 

hurgadion

Active Member
Joined
Mar 19, 2010
Messages
426
I think that my macro works good for Your Example from the first post... However, I try to understand Your requirement... I need maybe another Example for which my macro works not good... It will for me a fine Hint to understand Your English requirement... :) Besides, You can change Column 5 to Column 2 to obtain results in the proper places...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,154
Messages
5,600,024
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top