Also which Tab are you refering to?

I think you need to give a little bit more data to come up with a solution.

Solved

Posted on 2014-08-24

Attached I have a scrubbed version of a spreadsheet I use. What I need is the following when I click a button:

1. Find last month's date in Column Q and where found, when the value in Column J = x, copy the value of Col i and paste that value in Col Y after the last populated row. (So it will end up with a list of rows with different values from col i pasted in Col Y)

2. Find last month's date in Column Q and where found, when the value in Column K = x, copy the value of Col i and paste that value in Col Z starting in the same row as above.

3. Find last month's date in Column Q and where found, when the value in Column L = x, copy the value of Col i and paste that value in Col AA starting in the same row as above.

4. Find last month's date in Column Q and where found, when the value in Column L = gift card, copy the value of Col i and paste that value in Col AB starting in the same row as above.

When a blank value or declined or cancelled or any value other than an 'x' is found in any column (j, k, L) just ignore.

Thank you. Feel free to ask any questions where I'm unclear.

28504371Scrubbed-APPLICANT-STATUS.xls

1. Find last month's date in Column Q and where found, when the value in Column J = x, copy the value of Col i and paste that value in Col Y after the last populated row. (So it will end up with a list of rows with different values from col i pasted in Col Y)

2. Find last month's date in Column Q and where found, when the value in Column K = x, copy the value of Col i and paste that value in Col Z starting in the same row as above.

3. Find last month's date in Column Q and where found, when the value in Column L = x, copy the value of Col i and paste that value in Col AA starting in the same row as above.

4. Find last month's date in Column Q and where found, when the value in Column L = gift card, copy the value of Col i and paste that value in Col AB starting in the same row as above.

When a blank value or declined or cancelled or any value other than an 'x' is found in any column (j, k, L) just ignore.

Thank you. Feel free to ask any questions where I'm unclear.

28504371Scrubbed-APPLICANT-STATUS.xls

52 Comments

Also which Tab are you refering to?

I think you need to give a little bit more data to come up with a solution.

It is the Applicants tab.

For Col Q it is WHEN there is last month's date in Col Q.

There is values in Col K. A bunch of x's.

Never mentioned Col X but there was the value 'x' in columns.

Does that help?

use this one instead please.Scrubbed-APPLICANT-STATUS.xls

If you are trying to find the End of The Month in Q, but there is none it is hard to even start working on a formula.

And Q gets dates from this month, and past months.

I input dates in this format for Q: 07/31/14

So you could just put the values that I mention above and work from that, can't you?

So the idea is that when an applicant is approved I put the date approved in Col Q. And the value I'm looking for is last months date, so if I were to run the function today it would calculate July 2014 dates and only July 2014 dates.

here is the formula.

=IF(MONTH(EOMONTH($Q2,-1))

Scrubbed-APPLICANT-STATUS.xls

scan Column Q for last month's date and when found, perform the processes described.

It would be best to have a button to click on to initiate the macro.

```
Private Sub ScanColumn()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
' LastCol = .Cells(.Columns.Count,).End(xlToLeft).Column
'MsgBox (LastRow & vbNewLine & LastCol)
End With
For i = 2 To LastRow
If (Cells(i, "Q").Value) > 0 Then
Cells(i, "Q").Select
With Selection
If (DateDiff("M", Now(), ActiveCell.Value)) = -1 Then
Debug.Print (DateDiff("M", Now(), ActiveCell.Value))
If Cells(i, "J") = "x" Then
Cells(i, "Y").Value = Cells(i, "I").Value
ElseIf Cells(i, "K") = "x" Then
Cells(i, "Z").Value = Cells(i, "I").Value
Else: Cells(i, "L") = "x"
Cells(i, "AA").Value = Cells(i, "I").Value
End If
End If
End With
End If
Next
End Sub
```

And just to be straight you are only looking to find date values that are from the previous month.

ie. 8/25/2014 is today and the value in the field is 7/28/2014 this would be true where as 8/25/2014 is today and the value in the field is 6/30/2014 this is false.

Using today as an example, any date in July 2014 would be true and all other dates before July 1, 2014 and after July 31, 2014 would be false.

The issue was you had other values other than a date value in the Q Cell.

I added some addition IF Then Statements.

Line 19: Was modified to address whether the cell is empty or not

Line 24: Was added to check whether the value is a Date format or not.

```
Sub ScanColumn()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
' LastCol = .Cells(.Columns.Count,).End(xlToLeft).Column
'MsgBox (LastRow & vbNewLine & LastCol)
End With
For i = 2 To LastRow
If Not IsEmpty(Cells(i, "Q").Value) Then ' <-- I Changed this to check first for Cell Empty state
Cells(i, "Q").Select
With Selection
If IsDate(ActiveCell.Value) Then '<-- this then checks if the value is in a Date Format.
If (DateDiff("M", Now(), ActiveCell.Value)) = -1 Then
Debug.Print (DateDiff("M", Now(), ActiveCell.Value))
If Cells(i, "J") = "x" Then
Cells(i, "Y").Value = Cells(i, "I").Value
ElseIf Cells(i, "K") = "x" Then
Cells(i, "Z").Value = Cells(i, "I").Value
Else: Cells(i, "L") = "x"
Cells(i, "AA").Value = Cells(i, "I").Value
End If
End If
End If
End With
End If
Next
End Sub
```

So if the date in Q is June 2014 or older it will not do anything regarding the copying to the Y, Z or AA cell.

Is that what you want to have done or do you want to have anything <= to Last Month.

So if the date is Mar 2014 or June 2013 and the current Month is Aug 2014 then copy?

When we get to September the previous month will be all August 2014 dates. And when we get to October 2014 the previous month will be all dates in September 2014, and so on.

Then it is working as it should.

Are you not seeing the values copy over one of the Cells Y,Z or AA?

Are you not seeing the values copy over one of the Cells Y,Z or AA?

If so will it be based on Column Q and will it be newest date at the top?

```
Sub ScanColumn()
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
''MsgBox (LastRow & vbNewLine & LastCol)
End With
For i = 2 To LastRow
If Not IsEmpty(Cells(i, "Q").Value) Then
Cells(i, "Q").Select
With Selection
If IsDate(Cells(i, "Q").Value) Then
If (DateDiff("M", Now(), ActiveCell.Value)) = -1 Then
Debug.Print (DateDiff("M", Now(), ActiveCell.Value))
If Cells(i, "J") = "x" Then
Cells(i, "Y").Value = Cells(i, "I").Value
ElseIf Cells(i, "K") = "x" Then
Cells(i, "Z").Value = Cells(i, "I").Value
Else: Cells(i, "L") = "x"
Cells(i, "AA").Value = Cells(i, "I").Value
End If
End If
End If
End With
End If
Next
ActiveWorkbook.Worksheets("Applicants").Sort.SortFields.Add Key:=Range("Q2") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Applicants").Sort
.SetRange Range("A2:AA" & lastrow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
```

If that is too much then this is fine.

So if I get you all you want is a list of last month from ColA to show?

This can be done pretty simply by creating a new column with a row formula that populates.

Here is the formula: =IFERROR(EOMONTH($Q2,-1),0

Change Q2 to whatever the new column is AC2 or something like that.

why would put the formula is AC2 when the the columns needing this sorting are Col y, z and AA?

So to work around this I created a calculated column in AC to populate the cell with last month last day of the end of the month so the items can be sorted with the items that were approved Newest to oldest.

That is the impression that I got that you wanted to do.

I think I'll just take the previous solution you provided as that will be sufficient.

So technically the newest month in Column AC would be last months end of months date.

I will be more than happy to continue to assist, but if you feel that I am going down the wrong road please let me know

Title | # Comments | Views | Activity |
---|---|---|---|

trailing spaces all columns | 4 | 44 | |

Posting V12 | 2 | 17 | |

maro to copy and paste from one worksheet to another based on a condition | 11 | 28 | |

Ensuring all processes are complete before continuing | 6 | 16 |

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

Connect with top rated Experts

**17** Experts available now in Live!