Help with dates in VB code please

[jedibrown]

Hello all,

I am currently working on the below coding:

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
Sheets("Sheet2").UsedRange.ClearContents
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Not IsError(Range("F" & i)) Then
If InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 Or InStr(LCase(Range("F" & i).Value), "large") > 0 Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

I want to add another variable which involves dates. I want it to look in column K and if the day is within the current month - paste it to sheet 2

Can anybody help me please as I'm stuck?

Thanks

 

[Jonmo1]

You could add

If Format(Range("K" & i),"mmyyyy") = Format(Date, "mmyyyy") Then

 

[jedibrown]

Thanks - is there anyway of using the =today() function? This is going to be used by people who don't know VB or Excel very well so I don't want to have to adjust it each day.

Thanks again

 

[Jonmo1]

Date is the VBA equivilent of =TODAY()

Use it exactly as I posted it.

 

[jedibrown]

Ok thank you - do you know where to add this into the coding? I have tried but I keep getting errors.

Sorry I'm new at this!

 

[jedibrown]

Ok I have figured it out now - It's still pasting all information though, not just within the current month.

Thanks again for all of your help - sorry to be a pain.

Thanks

 

[Jonmo1]

Try like this

<font face=Error: Key or Value Not Found.><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>Sheets("Sheet2").UsedRange.ClearContents<br>LR = Range("F" & Rows.Count).End(xlUp).Row<br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsError(Range("F" & i)) <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Format(Range("K" & i), "mmyyyy") = Format(Date, "mmyyyy") <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> InStr(LCase(Range("F" & i).Value), "compensation payment") > 0 <SPAN style="color:#00007F">Or</SPAN> InStr(LCase(Range("F" & i).Value), "large") > 0 <SPAN style="color:#00007F">Then</SPAN><br>                Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> i<br>End <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jedibrown]

Hi really sorry but it doesn't seem to be copy anything to sheet 2 now.