Macro Summary and Renaming

M

mrerer

New Member
Joined
Mar 10, 2016
Messages
18
Hello all,
I have an odd scenario that has just happened to me for the first time with an existing file and macro I have used for some time now. This code was compiled over some versions of the file, but I am not able to reason out this piece and what it is resulting as.

Okay, here is my data below from my RLws tab:
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]Role Description for SOW[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]8.0[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]8.0[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]4.0[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]20.0[/TD]
[/TR]
[TR]
[TD]WIM Consultant[/TD]
[TD]26.0[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]24.0[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]4.0[/TD]
[/TR]
[TR]
[TD]WIM Consultant[/TD]
[TD]16.0[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]8.0[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]40.0[/TD]
[/TR]
[TR]
[TD]Practice Director[/TD]
[TD]20.0[/TD]
[/TR]
</tbody>[/TABLE]


And here is my VBA code to summarize this data...among some other actions, but the summary is my issue:
'Copy RL worksheet values to the Financial Summary Worksheet
'Begin Change
For Each resource In RLws.Range("B5:B" & LCell)
ResourceRow = resource.Row
NextRow = Fws.Cells(Fws.Rows.Count, "A").End(xlUp).Row + 1
RoleDescription = RLws.Range("B" & ResourceRow).Value


RoleDescription = RLws.Range("B" & ResourceRow).Value
If (RoleDescription Like "*Project*") Then
RoleDescription = "Project Management"
End If

Set ExistingService = Fws.Range("A9:A" & NextRow).Cells.Find(RoleDescription)

Set ExistingService = Fws.Range("A5:A" & NextRow).Cells.Find(RoleDescription)
If (ExistingService Is Nothing) Then
Fws.Range("A" & NextRow) = RoleDescription
Fws.Range("A" & NextRow).VerticalAlignment = xlCenter
Else
NextRow = ExistingService.Row
End If

Fws.Range("B" & NextRow).Value = Format(Fws.Range("B" & NextRow).Value + RLws.Range("DG" & ResourceRow).Value, "#,##0")
Next resource
'End Change

But, here is the catch, the following is the summation, which is incorrect. I believe my 'RoleDescription' is combining incorrect roles and resulting in bad data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Services[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]WIM Consultant[/TD]
[TD]118[/TD]
[/TR]
[TR]
[TD]Practice Director[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

The above VBA summary is incorrect as it should be:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Services[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]Consultant[/TD]
[TD]116[/TD]
[/TR]
[TR]
[TD]WIM Consultant[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]Practice Director[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

CAN ANYBODY SEE WHAT I HAVE GOING ON INCORRECTLY HERE THAT MY MACRO IS RE SUMMARIZING ROLES(ALBEIT SIMILAR ONES) AND APPLYING INCORRECT HOUR AMOUNTS ???

Thank you much!
Matt
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Why go through doing this with VBA when you can do it in seconds with a Pivot Table.

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[td="bgcolor:#ECF0F0, align:center"]F[/td]
[td="bgcolor:#ECF0F0, align:center"]G[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Role Description for SOW[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Total Hours[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Row Labels[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Sum of Total Hours[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:LEFT"]Consultant[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]116[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:LEFT"]Practice Director[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:LEFT"]WIM Consultant[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]42[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]20[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:LEFT"]Grand Total[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]178[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]WIM Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]26[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]24[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]8[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]9[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]WIM Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]16[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]10[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]11[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Consultant[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]40[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]12[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]Practice Director[/td]
[td="bgcolor:#FAFAFA, align:CENTER"]20[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Why no pivot?
Because it is a legacy report used by an audience that is more accepting of hard coded data summary versus pivots...this does that for us nicely.
Also, there is language to change certain roles naming to display as something else.

Just curios if there was an obvious thing there in the code is all.

Thanks much tho!
 
Upvote 0
Do you have more than the three categories shown in your example or are these the only three?

If only the Three, then here is alternate code. Yours was missing some clear definitions and I could not determine what your some of your variables were as you provided only partial code. This is a bit clunky, but it works.

Code: 
Option Explicit


Sub Ffoo()
'In this scenario, assumes your data is in cells A1:B12
    Dim lr As Long, lr2 As Long, i As Long
    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Set s1 = Sheets("RLws")
    Set s2 = Sheets("Fws")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Dim x As Long
    x = 0
    For i = 2 To lr
        If s1.Range("A" & i) = "Consultant" Then
            x = x + s1.Range("B" & i)
        End If
    Next i
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A" & lr2 + 1) = "Consultant"
    s2.Range("B" & lr2 + 1) = x
    x = 0
    For i = 2 To lr
        If InStr(1, s1.Range("A" & i), "WIM Consultant") > 0 Then
            x = x + s1.Range("B" & i)
        End If
    Next i
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A" & lr2 + 1) = "WIM Consultant"
    s2.Range("B" & lr2 + 1) = x
    x = 0
    For i = 2 To lr
        If InStr(1, s1.Range("A" & i), "Practice Director") > 0 Then
            x = x + s1.Range("B" & i)
        End If
    Next i
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    s2.Range("A" & lr2 + 1) = "Practice Director"
    s2.Range("B" & lr2 + 1) = x
End Sub
 
Upvote 0
Thank you Alan, much appreciated and it has worked great cleaning up my mess for now!

Best,
Matt
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ironman
  • Question Question
Two complex formulas returning incorrect values
2
Replies
11
Views
3K
Ironman
Ironman
Z
  • Question Question
Please help me correct some minor issues with a macro :)
2
Replies
16
Views
1K
zacuk
Z
S
  • Question Question
Help with complex macro; apply multiple functions to multiple files and make a summary workbook with multiple sheets
Replies
0
Views
410
sbzellison
S
N
  • Question Question
Search for text, copy and paste row with headers into new worksheet
Replies
1
Views
761
ninaking
N

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

Share this page

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
Back
Top