Looping If Statement Problem - 2

0

Thank you for your response to Qestion 1.  I learned something new about conditional statements.  Unfortunately I can't get either to work.  Code below:

Sub Copy_Africa()
 '
 'Scroll down in "Form" tab for Transfer Macro Button
 'The Cntrl + J macro is a present day manual selection transfer
 'Copies only "Africa's" previous month's dates and if column 3 is green (conditional
 'format) by "yes" or "Y" entire row to Summary sheet
 '

Dim wsSource As Worksheet
Dim wsSum As Worksheet
Dim today1 As Long
Dim today2 As Long
Dim k As Long
Dim sourceRow As Long
Dim summaryRow As Long

On Error Resume Next
Application.ScreenUpdating = False
Set wsSource = Sheets("Africa")
Set wsSum = Sheets("Summary")
today1 = Month(Cells(k, 2).Value)
today2 = Month(Date).Value - 1      'this works, previous month number
wsSum.Visible = True

sourceRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row

With wsSource
    For k = sourceRow To 4 Step -1
            summaryRow = wsSum.Cells(Rows.Count, 1).End(xlUp).Row

            'Compares datarow month(date) with today2 - double parentheses
        If today2 = today1 And ((wsSource.Cells(k, 9).Value = "yes") Or _
            (wsSource.Cells(k, 9).Value = "y") Or (wsSource.Cells(k, 12).Value = "yes") Or _
            (wsSource.Cells(k, 12).Value = "y")) Then

            'Compares datarow month(date) with today2 - single paraentheses
        'If today2 = today1 And (wsSource.Cells(k, 9).Value = "yes") Or _
            '(wsSource.Cells(k, 9).Value = "y") Or (wsSource.Cells(k, 12).Value = "yes") Or _
            '(wsSource.Cells(k, 12).Value = "y") Then

            'Compares datarow month(date) with today2 and color of Column C
        'If today2 = today1 And wsSource.Cells(k, 3).interior _
                '.ColorIndex = RGB(0, 255, 0) Then

            wsSource.Cells(k, 1).EntireRow.Copy wsSum.Cells(summaryRow + 1, 1)
            Application.CutCopyMode = False
        End If
    Next k

End With

'Future Call other lands to monthly "Summary" sheet
'Future Print wsSum sheet
'Future ClearContents wsSum sheet

MsgBox "Transfer of Africa entries Completed"
wsSum.Activate
Range("A3").Select  'eyeball "Summmary" sheet if transfer OK
On Error GoTo 0
End Sub
Answer
Discuss

Answers

0
Selected Answer

Don

As @Variatus said in his Answer to your previous question, your IF statements need to be constructed with care (and appropriate parentheses/brackets).

Equally your date comparison needs some thought (and just looking at the month part of the date has problems between years).

In the attached file I've mocked up what I think your question relates to. In the sheet Africa, there's some test data (in columns 1,2 9 and 12) and clicking the green shape "Transfer..." will  transfer only some of the rows (using your logic from the question).

Rather than use your variables today1 and today2, it works using the VBA function DateDiff, which (alone) would read:

If DateDiff("m", Date, Cells(k, 2).Value) = -1 Then
where the function DateDiff works out the difference between today and the date in the cell in terms of the interval "m" = months, whole numbers actually). This works over year ends too and can work with different intervals (like days or years)- here's the Microsoft guidance on DateDiff function

My file has the following modified version of your macro in Module 1 (with key changes shown in bold and uncommented If statements removed):

Option Explicit
Sub GetLastMonthAfrica()

Dim wsSource As Worksheet
Dim wsSum As Worksheet
Dim k As Long
Dim sourceRow As Long
Dim summaryRow As Long

Dim MonthsBack As Integer

Application.ScreenUpdating = False
Set wsSource = Sheets("Africa")
Set wsSum = Sheets("Summary")

wsSum.Visible = True

sourceRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row

With wsSource
    For k = sourceRow To 4 Step -1

        summaryRow = wsSum.Cells(Rows.Count, 1).End(xlUp).Row

        MonthsBack = DateDiff("m", Date, .Cells(k, 2).Value)

        If MonthsBack = -1 And (.Cells(k, 9).Value = "yes" Or _
            .Cells(k, 9).Value = "y" Or .Cells(k, 12).Value = "yes" Or _
            .Cells(k, 12).Value = "y") Then '

            .Cells(k, 1).EntireRow.Copy wsSum.Cells(summaryRow + 1, 1)

            Application.CutCopyMode = False
        End If
    Next k

End With

'Future Call other lands to monthly "Summary" sheet
'Future Print wsSum sheet
'Future ClearContents wsSum sheet

Application.ScreenUpdating = True ' It's good practice to turn it back on after you're done


wsSum.Activate
Range("A3").Select  'eyeball "Summary" sheet if transfer OK

MsgBox "Transfer of Africa entries Completed"

End Sub
Note that between the With... / End With statements, you don't need to write wsSource for items related to wsSource in the With, you just write .Cells(k,2) instead of wsSource.Cells(k,2). The dot (or period) is then very important (since if you miss it, Cells() would refer to the active sheet instead) and you need to add the worksheet for other references (e.g. wsSum.Cells(summaryRow + 1, 1) )

Hope this is what you need/ starts you in the right direction.

Discuss

Discussion

Thank you ery much John, It works as you said it would.  Sorry for all the discuss but it is what I needed as I begin my journey into VBA.  DateDiff made the difference and all the tidbits along the way.  Awesome, thank you.
DonC (rep: 2) Apr 6, '21 at 11:34 pm
Add to Discussion
0

Pay attention to this line of your code.

today1 = Month(Cells(k, 2).Value)

The variable k wasn't given a value. Therefore it is 0. Therefore the cell reference evaluates to an error. today1 remains 0.

The mistake is in On Error Resume Next. Remove it and VBA will show you the mistake. Keep it in place and VBA will keep your secret. In other words, use On Error Resume Next only when you know which error to expect and how you intend to deal with it.

One frequent response is this kind of code following the line that might cause the error.

If Err.Number Then
    MsgBox "The current month could not be determined."
End If

You might fling your net further and differently with this code in the same location.

If today1 < 1 Or today1 > 12 Then
    MsgBox "The current month could not be determined."
End If

On Error Resume Next will ensure that this code gets to run and the fact that today1 will remain 0 if an error occurs will ensure that the MsgBox is shown in case of an error (caused by k being not set). So, you might decide to keep the MsgBox even after setting k and removed On Error Resume Next (because there is no sense for the latter if k is assigned a value).

Discuss

Discussion

Good spot Variatus! Looks like moving that line today1 = Month(Cells(k, 2).Value) to after For k = sourceRow To 4 Step -1 might do the trick (combined with your other pointers).
John_Ru (rep: 1002) Apr 5, '21 at 5:37 am
Moved today1 to after FOR statement and commented out On Error.  now error sez "object required" on today2.   I'm not understanding object required for today2 when I'm checking for a certain monthdate.  How do I give it a set value?
DonC (rep: 2) Apr 5, '21 at 8:55 am
Your statement needs to read:
today2=Month(Date) - 1 
- you mustn't add a.Value property because Month() is a function (so doesn't have one) which just returns an integer (today1 and today2 can be declared as Integer rather than Long).

Incidentally you might need to think now what will happen when you get to Jan 2022 when Month(date) will be 1 but the previous month's entries will have a month of 12.
John_Ru (rep: 1002) Apr 5, '21 at 10:15 am
1. removed ".Value" from today1 and today2 
2. Step thru macro with no errors. today2 =3 (good) but today1=12 (not good) except for January 2022.
3. today2 = Month(Date) - 1
today1 = Month(Cells(k, 2))
so my IF statement is false, how do I find today1 = same month as today2?
I really appreciate your responses 
DonC (rep: 2) Apr 5, '21 at 2:31 pm
Don

You should change today2 but leave today1 as today1 = Month(Cells(k, 2).Value) since Value is only one of the properties of that cell (and the statement works  without it because it's the default property) but it's good practice to keep .Value with Cells().

You don't have to use two variables and the Month() function to work out if a date in a cell was from last month or not. You could use:
If DateDiff("m", Date, Cells(k, 2).Value) = -1 Then
where the function DateDiff works out the difference between today and the date in the cell in terms of the interval "m" = months, whole numbers actually). This works over year ends too.

Here's the Microsoft guidance on DateDiff function
John_Ru (rep: 1002) Apr 5, '21 at 5:31 pm
What a mess, John! It all starterd with your well-meant comment which really should have been an independent answer if given at all. An independent answer might be the only salvation now - if you can still figure out what the question is. @DonC, please bear in mind that this is a Q & A forum, not a discussion blog. Before this discussion started there was your question and one answer. I know that wasn't all you wanted but it as progress. Now I feel like deleting my answer and start over again with your question. Problem is, your question is no longer the same because you already learned something you didn't know before. That's good! That's what TeachExcel is all about.
Variatus (rep: 4544) Apr 5, '21 at 8:22 pm
Ok, 
I learned from the first answer, but it did not fix the problem.  Thanks for your time.  I'll go somewhere
DonC (rep: 2) Apr 5, '21 at 8:57 pm
@Variatus- you're right. My initial comment was intended as a small help attached to your full answer but led to a rambling chat (with no file for us to test against) and probably a dissatisfied user. 

Looks like Don's gone elsewhere for an answer but I'll review this and probably put my DateDiff suggestion late as an alternative Answer, as you suggest
John_Ru (rep: 1002) Apr 6, '21 at 1:57 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login