We help IT Professionals succeed at work.

macro

I need a macro that selects columns ae and af comapres their values. If a cell in ae is greater than af then do notjing else copied that cell along with its entire row and paste that data into sheet6 after the row that is called sample1.
For example if row b1 and d3 have values such values in columns ae and af then copies those two rows individually and paste them into the sheet6 after the row that is called sample1.
Comment
Watch Question

SANTABABYSoftware Professional
CERTIFIED EXPERT

Commented:
Please provide a sample workbook.

Author

Commented:
Here you go. Thanks for your comment. Help

Author

Commented:
Please let me know if you would like to have more details.

Author

Commented:
SANTABABY please let me know if you need additional details.
CERTIFIED EXPERT
Top Expert 2014

Commented:
@pauledwardian

Santababy requested that you upload a workbook.  You posted a screenshot.

Please post a workbook.

Author

Commented:
Here you go. Please look at the Sheet1 and Sheet6 for my explanations.

Sample.xlsx

Paul
CERTIFIED EXPERT
Top Expert 2014

Commented:
This should come close.

Option Explicit

Public Sub CostAnomaly()
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim rngCell As Range
    Set rngSrc = Worksheets("Sheet1").Range(Worksheets("Sheet1").Range("AE2"), Worksheets("Sheet1").Range("AE2").End(xlDown))
    Set rngTgt = Worksheets("Sheet6").Range("A:A").Find("Sample1")
    If rngTgt Is Nothing Then
        Stop
    Else
        Set rngTgt = rngTgt.Offset(1)
    End If
    Application.ScreenUpdating = False
    For Each rngCell In rngSrc
        If rngCell.Value > rngCell.Offset(0, 1).Value Then
            rngCell.EntireRow.Copy rngTgt
            Set rngTgt = rngTgt.Offset(1)
        End If
    Next
    rngTgt.Value = "Sample2"
    Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
Thank you so much aikimark. It worked with my sample I provided. Would you please look at this new workbook. The code did not work with this workbook. Maybe because of the spaces that are on Sheet1 between the lines of data. Please advice. I marked the spaces with green. Main-Sample.xls
CERTIFIED EXPERT
Top Expert 2014

Commented:
@pauledwardian

Thank you for the repost.  When the EE experts ask you to upload a file, they want to see a representative example of the data.  Please remember this for your next question.

Author

Commented:
aikimark,

I apologize for this inconvenience. I am fairly new to EE. I will remembered this for my next question. :)
Would you please assist on this?

Paul
CERTIFIED EXPERT
Top Expert 2014

Commented:
@pauledwardian

Please explain the following:

1. What is the significance of the "Sample1" placement in Sheet6?

2. In you problem description, you want "Sample2" to be placed immediately after the selected rows have been copied.  Now that I see the data, it looks like you want each section 'header' to be used.  Please describe this requirement in more detail.

3. Is there a reason why there are space characters in the column A cells that aren't section headers?

Author

Commented:
Sure.

1. What is the significance of the "Sample1" placement in Sheet6?
Basically I will need to copy couple if information from Sheet1 into sheet6. So, I asked in my other requests to have a code that would copy a couple of rows in sheet1 which are sample1 and sample3 rows and then insert them to sheet6.
So, in this question I asked for a code that would do a different operation on sheet1 and compare two columns and then paste the appropriate rows on sheet6 under sample1.
I just brought up sample1 as an example so it would be like a title for that specific operation that is being done on the AE and AF columns.
Also, if you noticed in my other question in (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27432225.html) that I will need the code to copy two different sections of sample1 & sample3 in sheet1 and insert them under sample1 and then inserts a new row called sample4. So, here I am trying to have a code that would paste the information under a row called sample1(which technically should be sample4) .

2. In you problem description, you want "Sample2" to be placed immediately after the selected rows have been copied.  Now that I see the data, it looks like you want each section 'header' to be used.  Please describe this requirement in more detail.
Again I apologize for this misunderstanding. I asked for this requirement because I posted another question in (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27432283.html)
that I need a code that would compare the dates and paste the Past Current date into sheet6.
So, sample2 would be a title for the next set of data that will be copied according to my other question regarding the dates.

3. Is there a reason why there are space characters in the column A cells that aren't section headers?

This report gets generated from an ERP system which was designed with crystal reports.
So, here in the report there will always be 2 rows of space after the last row of data and the next title. So, for instance there will be 2 spaces in after the last row of sample1 and sample2 title.

Paul

Author

Commented:
The reason I divided my questions into multiple posts was that I found it rude to other experts and inappropriate to submit a long question in a single post.
Because I know most of the experts are doing this only to help out others and it is totally voluntarily.
CERTIFIED EXPERT
Top Expert 2014
Commented:
Wouldn't it be easier to limit the CR output to those lines that meet your value comparison criteria?  That way, you wouldn't need to do any post-processing.

1. My question had to do with why is there "Sample1" text somewhere in column A?  Why would it move around?  Why wouldn't it start in the same cell as the "Sample1" on sheet1?

=============
This code will prompt you for the target cell to start copying.  There is no need to use  "Sample1" in the destination sheet.

Option Explicit

Public Sub CostAnomaly()
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim rngCell As Range
    Dim strHeader As String
    Worksheets("Sheet1").Columns("A").Replace what:=" ", replacement:=vbNullString
    Worksheets("Sheet1").Columns("AE").Replace what:=" ", replacement:=vbNullString
    Set rngSrc = Worksheets("Sheet1").Range("A1").End(xlDown)
    Set rngTgt = Application.InputBox("Please select the starting cell", "Target cell selection", , , , , , 8)
    Application.ScreenUpdating = False
    Do While Len(rngSrc.Value) <> 0
        strHeader = rngSrc.Value
        Set rngSrc = rngSrc.Offset(1)
        Set rngSrc = Intersect(rngSrc.EntireRow, Worksheets("Sheet1").Range("AE:AE"))
        Set rngSrc = Worksheets("Sheet1").Range(rngSrc, rngSrc.End(xlDown))
        rngTgt.Value = strHeader
        Set rngTgt = rngTgt.Offset(1)
        For Each rngCell In rngSrc
            If rngCell.Value > rngCell.Offset(0, 1).Value Then
                rngCell.EntireRow.Copy rngTgt
                Set rngTgt = rngTgt.Offset(1)
            End If
        Next
        Set rngSrc = Worksheets("Sheet1").Cells(rngSrc.Row, 1) 'rngSrc.End(xlToLeft).End(xlDown)
        Set rngSrc = rngSrc.End(xlToLeft).End(xlDown)
    Loop
    Application.ScreenUpdating = True
End Sub

Open in new window

Author

Commented:
Thanks aikimark for promping for the output. Is there anyway we can have this code combined with the previous code so it finds the sample1 on sheet6 and inserts sample2 at the end when it is done executing the code? So, it wouldn't ask for output? But, again I appreciate you did that extra option to help me out with it :)
CERTIFIED EXPERT
Top Expert 2014

Commented:
That goes to my previous question...How does the "Sample1" get placed into sheet6?

Did you look at the output in sheet6?  It copies whatever headers it finds in Sheet1.

Author

Commented:
Sample1 with always be on sheet6 as default. But, each time in a different location in column A.
And that is great that the code had all the titles in sheet1 copied in sheet6 and I really appreciate you did that.
But, it would be still better if it looks for sample1 in sheet6 and copies those information below sample1 and also generates a row called sample2 below the last row of data that was copied from sheet1.


Paul
CERTIFIED EXPERT
Top Expert 2014

Commented:
@Paul

Do you place the "Sample1" text into Sheet6?

Author

Commented:
Yes sir
CERTIFIED EXPERT
Top Expert 2014

Commented:
Then what difference does it make whether you type "Sample1" or you click on the cell?

What happens if your first header in Sheet1 is something other than Sample1?

My code copies the headers and rows no matter what the headers are.

Author

Commented:
aikimark,

Because this would be only a single part of the code and it will combine with another macro. The other macro will generate sample1 where-ever it finished copying the rows. Lets say it finished copying the data at row 34 and then it will insert a row called "sample1".
So, im trying to have your code to be continued after sample1. Basically I'm trying to group them. So, sample1 would only separate the previous information with the ones that your code will define.
This is my other post that I asked an expert to help me out for a macro. His code will generate a row called sample1 after the last row of data. And then your code can execute and take care of the two columns of AE and AF and execute their rows under sample1.
If you look at my other post at ( http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27432225.html ) which you actually added a comment ; I have asked the experts to provide me a code that would copy the information for specific rows. So, in your code if it adds a row called "sample2" then the other code will continue after "sample2" and have the same regulation. Basically Sample1, 2, 3, etc is a way for me to group each execution and separate each part.
I hope this makes sense.

Thanks again for your time,
Paul
CERTIFIED EXPERT
Top Expert 2014

Commented:
@Paul

You lost me.  First, you had a problem with blank cell behavior.  Then, after seeing the workbook, you have code that processes all the sections.  Now we're back to processing only one section?

I think your quest to keep your questions simple has backfired.

Since your multiple questions exist in some context that I don't understand, I'm going to walk away from this thread.  Maybe one of the other experts will be able to run with one of the versions of my code or create their own and give you the solution you seek.  I'm off to bed.

Author

Commented:
Ok thanks for your help anyways

Author

Commented:
Thanks to aikimark.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.