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
<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
user 1 = (sum Row 1:5)
user 2 = (sum Row 6:9) etc
user1 | Security code result | Security codes | |
row1 | user1 | 63 | 61 |
row2 | |||
row3 | 2 | ||
row4 | |||
row5 | |||
row6 | user2 | 4 | 3 |
row7 | |||
row8 | 1 | ||
row9 | |||
row10 | user3 | 6 | 4 |
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