ensure value is always two digits --> macro using cell value 1

[y2k1981]

I have the following code to to a Save As action on a file:

    Range("A3").Select
        Range("A1").Formula = Now()
    Range("B1").FormulaR1C1 = "=DAY(RC[-1])"
    Range("C1").FormulaR1C1 = "=MONTH(RC[-2])"
    Range("D1").FormulaR1C1 = "=YEAR(RC[-3])"
    MyDay = Range("B1").Value
    MyMonth = Range("C1").Value
    MyYear = Range("D1").Value

This is used to archive a file every day with the date, however, I'd like the name to bein the format

MyFileName MM-DD-YY.xls

ALWAYS, but, say for example on the first of march, the file name will be

MyFileName M-D-YY.xls

. How can I change the above code so that the values in the three cells are always two digits?

 

[xlbo]

set the format in the cells to "00" and then pick up the cell TEXT rather than the cell VALUE Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[y2k1981]

thanks for your response. That works perfet for the date and month, however, the value for the year cell si 2003, and chaning the formatting doesn't appear to be having any effect. Can you help me out a little there?

second question, how do I pick up the text instead of the value, is it just

MyDay = Range("B1").Text

????

 

[xlbo]

Perhaps a slightly different way round.....

DateString = format(Now(),"dd-mm-yy&quot

would ensure 2 digits for each part and mean that you don't have to pull values from cells Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[y2k1981]

That's a good idea, but I'm still stuck!! This is the piece of code I've written now:

    MyDateString = Format(Now(), MM - DD - YY)
    MsgBox (MyDateString)
    'ActiveWorkbook.SaveAs Filename:="C:\My Documents\archive\MyFileName " & MyDateString & ".xls"

As you can see, I commented out the save as for now, and used a msgbox to alert me of the value, but its returning 37672 - which I presume is the amount of days today is since jan 1st 1900, but it's now what I want. can you help me out?

 

[y2k1981]

sorry, I just spotted my problem, I forgot to put MM-DD-YY in " And ". it's sorted now, thanks for all your help and patience.

Big shiney star for you!!

 

[xlbo]

was thinking more along the lines of:

ActiveWorkbook.SaveAs Filename:="C:\My Documents\archive\MyFileName " & format(now(),"dd-mm-yy&quot & ".xls"
Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur