1. Registered User
Join Date
Sep 2002
Location
NJ
Posts
139

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??

2. Registered User
Join Date
Oct 2003
Posts
1,091
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.

3. Registered User
Join Date
Feb 2004
Posts
533
Originally Posted by Alexxx12
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'
'************************************************

IS THERE A WAY TO FIX THIS??
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

From Microsoft Visual Basic Help Example
This example sets the font size for cell C5 on Sheet1 to 14 points.

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14

This example clears the formula in cell one on Sheet1.

Worksheets("Sheet1").Cells(1).ClearContents

This example sets the font and font size for every cell on Sheet1 to 8-point Arial.

With Worksheets("Sheet1").Cells.Font
.Name = "Arial"
.Size = 8
End With

This example loops through cells A1:J4 on Sheet1. If a cell contains a value less than 0.001, the example replaces that value with 0 (zero).

For rwIndex = 1 to 4
For colIndex = 1 to 10
With Worksheets("Sheet1").Cells(rwIndex, colIndex)
If .Value < .001 Then .Value = 0
End With
Next colIndex
Next rwIndex

This example sets the font style for cells A1:C5 on Sheet1 to italic.

Worksheets("Sheet1").Activate
Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
Last edited by savbill; 09-04-04 at 08:40.

4. Registered User
Join Date
Sep 2002
Location
NJ
Posts
139

## date format

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•