Hi Frank,

Very true. I had no idea what Monthname was so I left, but I shouldn't
have<vbg

Bob

"Frank Kabel" wrote in message
...
Hi Bob
I would also remove the Monthname call in this procedure (also removed
a small typo in cell.offsey):

--------
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")

For Each cell In Range("myTime")
cell.offset(0, 1).Value = format(cell.value,"DD/MMMM/YYYY")
Next cell
End With
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
Although Frank's suggestion seems reasonable, your code looks awfully
kludgy
to me.
Does this work for you?

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")

For Each cell In Range("myTime")
mytime = cell.Value
cell.offsey(0, 1).Value = Day(mytime) & "/" & _
MonthName(Month(mytime), False) & "/" & _
Year(mytime)
Next cell
End With
End Sub



--

HTH

RP

"Frank Kabel" wrote in message
...
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