We help IT Professionals succeed at work.

Need assistance with a Macro

Bright01
Bright01 asked
on
174 Views
Last Modified: 2014-08-28
EE Pros,

I have a macro called sumup.  It places data identified in the top of the WS to be placed on line 800 down in the WS. Every time I add a row, it screws up the alignment.  I need the data to be placed on a different worksheet so I need an additional line (maybe Const Output_  new WS name.........), so to put the data output on another WS.

Thank you in advance.

B.

Sub sumup()
Const Input_Range = 5
'3
Const Output_Range = 800
'17
Const Output_CS = 7
'18
Const Output_CE = 10
Const Input_CM = 5

Dim O_R As Long
'Output Row
Dim I_R As Long
'Input Row
Dim Total As Double

'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, 2).Value
                Cells(O_R, Output_CE).Formula = "=" & .Offset(0, 5).Address
                Cells(O_R, Output_CE).NumberFormatLocal = _
                        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
                
                If Not IsError(.Offset(0, 5).Value) Then 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)
        .FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
        .NumberFormatLocal = _
                "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
    End With
    'Else
    
    'MsgBox "Please select what you wanted! Thanks!"
End If

End Sub

Open in new window

Comment
Watch Question

Can you not just reference the destination sheet in the Cells() function or am I missing something:
Sheets("sheet2").Cells(1,1) = "Whatever value you require"

Author

Commented:
Andrew,

Thanks for jumping in.  I think it may be that easy.  I need to simply reference the other destination sheet where I want the output.  But I don't know how to do that.  Can you show me where that goes in the code I sent?  Don't need all the lines just the place where I should put it.  and do I include "Whatever value you require"............?

B.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Almost..... I followed your directions and it did exactly what you thought.  It outputted the data selected to the new WS.  However, where in the code does it give me the ability to specify the Output Column (right now it's G and I'm trying to get it in "A") and the Output Row (right now it is 800 and I want it to be 3).  When I change the

Const Output_Range = 800 to 3

and

Const Output_CS = 7 to 1

It deletes all the records on the original WS.

Any ideas on how to control the output Column and Row?

Thanks again for the help here.

B.
That will be due to the code later on in the VBA:
 Cells(Output_Range - 1, Output_CS).Value = "Total"
    With Cells(Output_Range - 1, Output_CE)
        .FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
        .NumberFormatLocal = _
                "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
    End With

Open in new window

You will need to add the output sheet to this code too:
 sheets("Output_Dest").Cells(Output_Range - 1, Output_CS).Value = "Total"
    With sheets("Output_Dest").Cells(Output_Range - 1, Output_CE)
        .FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
        .NumberFormatLocal = _
                "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
    End With

Open in new window

You can then specify whatever output const you want.
Your sheet was cleared because of the code:
'**** Clear the previous output ****
O_R = sheets("Output_Dest").Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
    'Column and Row Output
    Range(sheets("Output_Dest").Cells(Output_Range, Output_CS), sheets("Output_Dest").Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation O_R = Output_Range

Open in new window

If this is to clear the output sheet, update the code to read:
'**** 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

Open in new window

Does this help?

Author

Commented:
Andrew,

I was with you up until the last point on "clearing the output sheet".  I think the two are reversed in the example above (i.e. what I had before and what I needed to change it to).  When I did change it, it  actually prevented me from using the check boxes on the original input WS so I reverted back to the original way I had it.  Also, it's confusing how you actually target the output.  The code you advised me on on the first round of discussion accurately sent the selections to the new WS...but still down on line 800 in Column G.  I can make that work... not as elegant as I'd like...but it does work.

B.
If you want to post the actual sheet and vba, I can modify the code and send it back to you to better explain the changes.

The post was reversed, sorry about that.

Author

Commented:
Andrew,  I'm good with what you originally sent me.  It works great.... much appreciate your efforts and talents here.

B.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.