Hi
not sure why you don't simply change the format in Excel to a custom
format such as
DD/MMMM/YYYY
as this will do just this

or in VBA try
range("A2:A9").numberformat = "DD/MMMM/YYYY"

--
Regards
Frank Kabel
Frankfurt, Germany

"casperlove" schrieb im
Newsbeitrag ...

Hello. please help.

I have a problem down here. I need to do a loop + If..Then..Elseif
procedure for my assignment. I was given a list of dates (A2:A9), and
i
got to change their format from dd/mm/yyyy to dd/monthname/yyyy. I am
able to do that, but the problem arise when i need to do the loop
through the cells from A2 to A9.
I assigned a name called "mytime" to the various cells, and i use
day(),month(),year() function. so the code will become day(mytime),
month(mytime), year(mytime).

my code looks like this:

Sub newdate()
Dim mytime As Date
Dim mthname As String
Dim i As Integer
Dim rowno As Integer
Dim cell As Range


Range("A2:A9").Name = "mytime"

With Worksheets("New Display").Range("mytime")
rowno = .Rows.count
End With

With Worksheets("New Display").Range("A2")

Do
mytime = .Offset(i, 0)

If month(mytime) = 1 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(1, False) & "/" &
Year(mytime)

ElseIf month(mytime) = 2 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(2, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 3 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(3, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 4 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(4, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 5 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(5, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 6 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(6, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 7 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(7, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 8 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(8, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 9 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(9, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 10 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(10, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 11 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(11, False) & "/" &
Year(mytime)
ElseIf month(mytime) = 12 Then
Offset(i, 1) = Day(mytime) & "/" & Monthname(12, False) & "/" &
Year(mytime)

Else: .Offset(0, 1) = Day(mytime) & "/" & Monthname(0, False) & "/"
& Year(mytime)

End If

i = i + 1
Loop Until (i = 8)

End With
End Sub


I can't loop correctly with this procedure. can someone tell me
what's
wrong with it?


--
casperlove
---------------------------------------------------------------------
---
casperlove's Profile:
http://www.excelforum.com/member.php...o&userid=15050
View this thread:
http://www.excelforum.com/showthread...hreadid=266745