Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need assistance with a Macro

Posted on 2014-08-27
9
Medium Priority
?
162 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

0
Comment
Question by:Bright01
  • 4
  • 4
8 Comments
 
LVL 3

Expert Comment

by:AndrewSwingewood
ID: 40289737
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"
0
 

Author Comment

by:Bright01
ID: 40289754
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.
0
 
LVL 3

Accepted Solution

by:
AndrewSwingewood earned 2000 total points
ID: 40289764
I would just add the sheet reference into the output Cells() lines.  Assuming your sheet output worksheet is called Output_dest then the code would be modified to read:
sheets("Output_Dest").Cells(O_R, Output_CS).Value = .Offset(0, 2).Value
sheets("Output_Dest").Cells(O_R, Output_CE).Formula = "=" & .Offset(0, 5).Address
sheets("Output_Dest").Cells(O_R, Output_CE).NumberFormatLocal = _
                        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"

Open in new window


Does this clarify things?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bright01
ID: 40289806
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.
0
 
LVL 3

Expert Comment

by:AndrewSwingewood
ID: 40289822
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?
0
 

Author Comment

by:Bright01
ID: 40289838
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.
0
 
LVL 3

Expert Comment

by:AndrewSwingewood
ID: 40289841
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.
0
 

Author Closing Comment

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

B.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

578 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