VBA check if date in cell is between 1st and 15th of current month

[powercell99]

I'm banging my head against my monitor here. Dealing with dates and VBA feels like ironing my tongue. What am i doing wrong in this code? I just want to do 2 things. If the cell in column 68 is blank, do nothing. If its not blank, check the date to see if its between the 1st and 15th of the current month. Any help or suggestions you could provide would really help me out.

Sub datecheck()


Dim CurrenFirst As DateDim CurrentFifteenth As Date

Dim lastrow As LongDim rownum As LongDim NReq As DateDim newreqdt As Date


lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).RowFor rownum = 2 To lastrowNReq = Sheets("Travel Table").Cells(rownum, 68)newreqdt = DateValue(NReq)
CurrenFirst = DateSerial(Year(Date), month(Date), 1)CurrenFifteenth = DateSerial(Year(Date), month(Date), 15)


   If Cells(rownum, 68).Value <> "" And newreqdt >= CurrentFirst And newreqdt <= CurrentFifteenth Then   Cells(rownum, 72).Value = Year(Date) & "-" & month(Date)      End If      Next rownum      
End Sub

 

[Joe4]

What sheet are you on when the Macro is called?
Note that you have NOT included a sheet reference in your lastrow calculation. So, if you are not already on the "Travel Table" sheet when the macro is called, it is finding the lastrow of the sheet you are on, not the "Travel Table" sheet.

 

[Joe4]

I think the issue is typos.
In some places, you have CurrentFirst, in others you have CurrenFirst.
Likewise, in some places, you have CurrentFifteenth, in others you have CurrenFifteenth.

I highly recommend that you use "Option Explicit" in all your code. It will force you to declare all variables, and find all typos like this.
See: Excel VBA Option Explicit - EASY Excel Macros

 

[powercell99]

You guys are awesome! Thanks for the suggestions. I added the sheet to the lastrow declaration and added Option Explicit to test the variable declarations. There were some typos :-O . but OE identified exactly what needed to be corrected. Thanks for your great suggestions, i appreciate them both