• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date variable issue

A

Alex24

New Member
Hello everyone,

I am quite new to the VBA world. As you see in the title I have an issue with one date variable. I have to write a subroutine which, among others, has to check if the dates inserted in one column are today or in the past and then change the values in another column accordingly.
This is what I came up with so far:

Dim ReleaseDate As Date
Dim Status As String
Dim NewStatus As String
Columns("AV").Select
Range("AV160:AV2959") = ReleaseDate
Columns("AL").Select
Range("AL160:AL2959") = Status
If ReleaseDate <= Date Then NewStatus = "Available"

The actual problem is than when the code reaches the release date column and steps into the if, it changes all the values from the normal format (dd.mm.yyyy) into this : 12:00:00 AM. The following step is then somehow ignored, more precisely the whole status column is emptied.
I am using the Excel 2010 version. I would appreciate if you could help me solve this issue. Thanks in advance!
 
Hi Alex24
Are you trying to format the columns as dates or change their values? From the code you have given, you are setting ranges equal to variables which you have not given any value to. 'Status' is an empty string, so setting a range to equal that will just empty the range. I don't tend to use the 'Date' variable type, but I'm assuming that 12:00:00 AM is just '0' when formatted as a date.
Please elaborate.
 
This is what I'm working it (part of it). Basically, I need to check if the release date is today or in the past and the status is open, then it needs to change the status from open to available. I think it is something really easy to do, but my knowledge is not good enough.
 

Attachments

  • Example.xlsx
    9.3 KB · Views: 0
Hiya Alex,
I downloaded your file and I think I understand what you are trying to do.
Open up VBE and create a new module, and paste this in it:

Code: 
Sub available()
    Dim rowmax As Integer, currrow As Integer, today As Double
    today = Date
    rowmax = 26
    currrow = 2
    testdate = Sheets("Sheet1").Range("A2").Value
    While currrow < rowmax + 1
        If Sheets("Sheet1").Range("A" & currrow).Value <= today Then
            If Sheets("Sheet1").Range("B" & currrow).Text = "Open" Then
                Sheets("Sheet1").Range("B" & currrow) = "Available"
            End If
        End If
        currrow = currrow + 1
    Wend
End Sub

This will do what you were describing.
I also noticed that some of your dates are blank. The above macro will treat these as 0, and change the corresponding statuses to Available. If this is not what you want, use this instead to ignore blanks:

Code: 
Sub available()
    Dim rowmax As Integer, currrow As Integer, today As Double
    today = Date
    rowmax = 26
    currrow = 2
    testdate = Sheets("Sheet1").Range("A2").Value
    While currrow < rowmax + 1
        If Sheets("Sheet1").Range("A" & currrow).Value = 0 Then
        Else:
            If Sheets("Sheet1").Range("A" & currrow).Value <= today Then
                If Sheets("Sheet1").Range("B" & currrow).Text = "Open" Then
                    Sheets("Sheet1").Range("B" & currrow) = "Available"
                End If
            End If
        End If
        currrow = currrow + 1
    Wend
End Sub

I hope this helps, let me know!
 
Thank you for your prompt reply. I will only be able to test it later and get back to you. Cheers :)!
 
This will do what you were describing.
I also noticed that some of your dates are blank. The above macro will treat these as 0, and change the corresponding statuses to Available. If this is not what you want, use this instead to ignore blanks:

Code: 
Sub available()
    Dim rowmax As Integer, currrow As Integer, today As Double
    today = Date
    rowmax = 26
    currrow = 2
    testdate = Sheets("Sheet1").Range("A2").Value
    While currrow < rowmax + 1
        If Sheets("Sheet1").Range("A" & currrow).Value = 0 Then
        Else:
            If Sheets("Sheet1").Range("A" & currrow).Value <= today Then
                If Sheets("Sheet1").Range("B" & currrow).Text = "Open" Then
                    Sheets("Sheet1").Range("B" & currrow) = "Available"
                End If
            End If
        End If
        currrow = currrow + 1
    Wend
End Sub

I hope this helps, let me know!
Click to expand...



Hello again, Stevie, I tried to run the code you provided (thanks again), but I've encountered the following problem: the variable testdate is not defined; when I define it (I have defined it as Date since it takes values from a range populated with dates); after I define it, I'm confronted with a type mismatch error. If you could shed some light on this issue I would really appreciate it or point out some good VBA materials that I could read and improve. Thanks in advance !
 
Last edited by a moderator:
Hi Alex ,

Your workbook has data such as the following in column A :

02.04.2015

Is this a valid date in your system ?

In my system , this is not a valid date , and I get an identical error message.

However , that is neither here nor there ; the variable testdate is merely assigned a value , and is never used within the code !

You can delete the entire line of code , and it will not make any difference ; however , it will eliminate the error message !

Narayan
 
You must log in or register to reply here.
Back
Top