pauledwardian
asked on
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.
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.
Please provide a sample workbook.
ASKER
Please let me know if you would like to have more details.
ASKER
SANTABABY please let me know if you need additional details.
@pauledwardian
Santababy requested that you upload a workbook. You posted a screenshot.
Please post a workbook.
Santababy requested that you upload a workbook. You posted a screenshot.
Please post a workbook.
ASKER
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
ASKER
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
@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.
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.
ASKER
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
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
@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?
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?
ASKER
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 (https://www.experts-exchange.com/questions/27432225/vba.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 (https://www.experts-exchange.com/questions/27432283/vba-macro.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
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 (https://www.experts-exchange.com/questions/27432225/vba.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 (https://www.experts-exchange.com/questions/27432283/vba-macro.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
ASKER
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.
Because I know most of the experts are doing this only to help out others and it is totally voluntarily.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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 :)
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.
Did you look at the output in sheet6? It copies whatever headers it finds in Sheet1.
ASKER
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
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
@Paul
Do you place the "Sample1" text into Sheet6?
Do you place the "Sample1" text into Sheet6?
ASKER
Yes sir
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.
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.
ASKER
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 ( https://www.experts-exchange.com/questions/27432225/vba.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
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 ( https://www.experts-exchange.com/questions/27432225/vba.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
@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.
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.
ASKER
Ok thanks for your help anyways
ASKER
Thanks to aikimark.