CODEWITHSUNDEEP
vbaexcel
CivilSigma
CivilSigma

Reputation: 179

Error Reading Cell Date

I am trying to create a button that will hide rows based on the date the function reads.

My excel sheet is for meeting minutes, and based off column D, I will decide whether to hide or show the cell row. Now, column D contains dates of particular minutes, but occasionally contains a string called "Date" as part of a header row. For some reason, I cannot successfully write an if statement to skip said rows. Therefore, I am getting an error where my variable Current_Date is assigned the default VBA date value and my code crashes.

I made sure to format those particular cells as "Text" on the spread sheet, but it seems like my if statement still does not execute.

Can some one please provide me with some guidance.

Thank you in advance.

Private Sub CommandButton1_Click()

Dim x As Integer
Dim Current_Date As Date
Dim Last_Meeting_Date As Date
Dim default_date As Date

' Loop to hide old meeting minutes
For x = 150 To 1000

If Worksheets("Minutes").Cells(x,4) = "Date" Then
x = x + 1
End If

Current_Date = Worksheets("MINUTES").Cells(x, 4)
Last_Meeting_Date = Worksheets("HOME").Cells(19, 16)

If Current_Date < Last_Meeting_Date Then
Worksheets("MINUTES").Rows(x).Hidden = True
End If

Next x

End Sub

Upvotes: 0

Views: 57

Answers (2)

Marcucciboy2
Marcucciboy2

Reputation: 3259

I took a few liberties in reformatting and simplifying your code. I reordered the declarations, removed 'default date' since it was unused, changed your references to column '4' to 'D', reversed the logic of your if statement, and used a 'With' statement to prevent repeated specifications of your Worksheet.

Private Sub CommandButton1_Click()

    Dim Last_Meeting_Date As Date
    Last_Meeting_Date = CDate(Worksheets("HOME").Cells(19, 16).Value2)

    Dim x As Long
    Dim Current_Date As Date

    ' Loop to hide old meeting minutes
    With Worksheets("MINUTES")
        For x = 150 To 1000
            If CStr(.Cells(x, "D").Value2) <> "Date" Then

                Current_Date = CDate(.Cells(x, "D").Value2)

                If Current_Date < Last_Meeting_Date Then .Rows(x).Hidden = True

            End If
        Next x
    End With

End Sub

Upvotes: 1

Vincent G
Vincent G

Reputation: 3188

You might try:

Private Sub CommandButton1_Click()

    Dim x As Integer
    Dim Current_Date As Date
    Dim Last_Meeting_Date As Date
    Dim default_date As Date

    Last_Meeting_Date = Worksheets("HOME").Cells(19, 16)

    ' Loop to hide old meeting minutes
    For x = 150 To 1000
        If Worksheets("Minutes").Cells(x,4) <> "Date" Then 'You might want to use IsDate()?
            Current_Date = Worksheets("MINUTES").Cells(x, 4)
            'original code is only able to hide row, this one can unhide them as well
            Worksheets("MINUTES").Rows(x).Hidden = (Current_Date < Last_Meeting_Date)
        End If
    Next x
End Sub

Upvotes: 2

Related Questions