HI:

WHEN I USE THE LEFT FUNCTION IN EXCEL TO RETURN THE FIRST TWO CHARACTERS I GET AN DIFFERENT RESULT IF I USE DATE OR THE VALUE FROM A CELL.


Dim myleft1, myleft2 As Date

'************************************************
myleft1 = Left(Format(Range("b" & z).Value, "MM/DD/YYYY"), 2)
'HERE THE RESULT IS '07', WHICH IS WHAT I WANT

myleft2 = Left(Format(Date, "MM/DD/YY"), 2)
HERE THE RESULT IS '1/8/1900' AND I DON'T UNDERTAND WHY I GET THIS AND NOT 'O7'
'************************************************
.
.
.
.
.
If myleft1 = myleft2 Then
mycount = mycount + Sheet5.Range("C" & z).Value
Select Case mycount
Case Is = "8/"
Sheet7.Range("b9").Value = mycount
Case Is = "9/"
Sheet7.Range("b10").Value = mycount
End Select
eNd If

IS THERE A WAY TO FIX THIS??

I think it has to do with how Excel stores the date as a number (i.e. 37821).

In your second one, it doesn't matter what you put int the cell. It really isn't referencing the cell at all.

This is because you have dimensioned your variables 'myleft1' as an implied Variant type, and 'myleft2' as a date type, So with myleft2 you are saying take '9' ( or whatever month number in 'Date') and make it a date which is going to show however many days from 1/0/1900 as a date format. With myleft1 excel is setting the Variable type per the context of its use, a string in this case. To resolve it you need to set explicit variables like this.

'********************
Dim myleft1 As String, myleft2 As String
OR
Dim myleft1 As String
Dim myleft2 As String
'********************

You could also type these as an integer or long variable type, if you don't care about the leading '0'. The numeric types use a smaller bit size requirement.

* Not trying to be the Excel Code Nazi, but I have seen you frequently refer to range objects using a concatenated string notation as the range name ie. "myleft1 = Left(Format(Range("b" & z).Value, "MM/DD/YYYY"), 2)" This will certianly work, but is not good Syntax. For cleaner easier to follow code that uses less overhead, use the Cells Property to refer to a cell where a Variable Column / Row is needed. The Cells property can use a number or numeric value in a variable for the Row and Col coordinants.

If you want wave the big red NEWBIE flag then keep doing what you've been doing, otherwise use the Cells property where appropriate

Thank you for your great advise. Once the application is functional, i will review the code and comply with your standards. I need to pay attention to them in my code not only to make it better but more readable and understandable.