Solved

Adding some additional Rows to great macro

Posted on 2013-12-02
8
188 Views
Last Modified: 2013-12-02
I have a nice Macro created by Andrew Man from Hong Kong that allows you to select a particular "row" and have it total for you on the right side of the WS.  I need some additional rows created at the top (+5) where the totals take place.

That's it!

Thanks in advance.

B.
Compling-the-Value-List-v2.xlsm
0
Comment
Question by:Bright01
  • 5
  • 2
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39689584
Try this macro after inserting the rows manually.

Sub sumup()
    Dim O_R As Integer
    'Output Row
    Dim I_R As Integer
    'Input Row
    O_R = Cells(Rows.Count, 17).End(xlUp).Row
    'Targeted Output "Total"
    If O_R > 8 Then
        'Column and Row Output
        Range(Cells(3, 17), Cells(O_R, 18)).ClearContents
    End If
    'Column Explanation
    O_R = 3
    
    'Where the checkmark is
    I_R = Cells(Rows.Count, 6).End(xlUp).Row
    If I_R > 13 Then
        'Input row
        For i = 3 To I_R
            With Cells(i, 6)
                If .Value = "P" Then
                    'Output results here
                    Cells(O_R, 17).Value = .Offset(0, 1).Value
                    Cells(O_R, 18).Value = .Offset(0, 5).Value
                    O_R = O_R + 1
                End If
            End With
        Next
    Else
        MsgBox "Please select what you wanted! Thanks!"
    End If
    
End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39689591
Hi all,

I am Andrew Man from Hong Kong!  Better to Teach a Man Fishing Than to Give Him a Fish!

Sub sumup()
Dim O_R As Integer
Dim I_R As Integer

''**** Clear the previous output ****
O_R = Cells(Rows.Count, 17).End(xlUp).Row  ''Search the last row for output
If O_R > 2 Then    ''' Clear if greater than row 2
Range(Cells(3, 17), Cells(O_R, 18)).ClearContents
End If
O_R = 3  ''Fix the output to row 3

I_R = Cells(Rows.Count, 6).End(xlUp).Row  ''Find the last row for input range
If I_R > 8 Then
For i = 9 To I_R
With Cells(i, 6)
If .Value = "P" Then
Cells(O_R, 17).Value = .Offset(0, 1).Value
Cells(O_R, 18).Value = .Offset(0, 5).Value
O_R = O_R + 1
End If
End With
Next
Else
MsgBox "Please select what you wanted! Thanks!"
End If

End Sub
Compling-the-Value-List-v3.xlsm
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39689596
O_R > 8 is incorrected!
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 4

Expert Comment

by:andrew_man
ID: 39689608
Dear Bright01,

Please dun close this thread!

I want to tell you more!

Thanks!

Andrew Man
0
 
LVL 4

Accepted Solution

by:
andrew_man earned 500 total points
ID: 39689664
Dear Bright01,

Thousand Thanks for ssaqibh.

In my real life case, I will tell my subordinates using more constant rather than hard code to the program.

So, please dun remember to give some marks to ssaqibh.  Thanks!  Keep in touch!

You can close the thread now.  Thanks!

Andrew from Hong Kong

Sub sumup()
Const Input_Range = 9
Const Output_Range = 3
Const Output_CS = 17
Const Output_CE = 18
Const Input_CM = 6

Dim O_R As Integer
'Output Row
Dim I_R As Integer
'Input Row
Dim Total As Integer
'Total

'**** Clear the previous output ****
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
    If O_R >= Output_Range Then
    'Column and Row Output
    Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
    End If
'Column Explanation
O_R = Output_Range


'Where the checkmark is
I_R = Cells(Rows.Count, Input_CM).End(xlUp).Row
If I_R >= Input_Range Then
'Input row
For i = Input_Range To I_R
With Cells(i, Input_CM)
If .Value = "P" Then
'Output results here
Cells(O_R, Output_CS).Value = .Offset(0, 1).Value
Cells(O_R, Output_CE).Value = .Offset(0, 5).Value
Cells(O_R, Output_CE).NumberFormatLocal = _
        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"

Total = Total + .Offset(0, 5).Value
O_R = O_R + 1
End If
End With
Next
Cells(Output_Range - 1, Output_CS).Value = "Total"
With Cells(Output_Range - 1, Output_CE)
.Value = Total
.NumberFormatLocal = _
        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
End With
Else

MsgBox "Please select what you wanted! Thanks!"
End If

End Sub

Open in new window

Compling-the-Value-List-v4.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39689700
Hi, Andrew Man from Hong Kong, how about a detailed intro in your profile?
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39689710
Hi Ssaqibh,

Updated my profile, if you want to know me more?  You can send me an email.

Andrew Man from Hong Kong
0
 

Author Closing Comment

by:Bright01
ID: 39689939
Andrew and Ssaqibh,

Great Teamwork!  And Andrew, your code is very clean and works very well!  

I will be Posting another question about how to "automatically" provide/drive the updates instead of using the button.  Hopefully you guys will pick it up.

Again, much thanks.......

B.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
New Criteria Prototype 3 16
Excel Spacing Anomaly 4 21
Excel IF formula 3 18
Excel 2010:  Creating a "Yes" or "No" Pop-Up 20 31
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now