Loop through selected cells

[casperlove]

Hello. please help.

I have a problem down here. I need to do a loop + If..Then..Elsei
procedure for my assignment. I was given a list of dates (A2:A9), and
got to change their format from dd/mm/yyyy to dd/monthname/yyyy. I a
able to do that, but the problem arise when i need to do the loo
through the cells from A2 to A9.
I assigned a name called "mytime" to the various cells, and i us
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'
wrong with it

 

[Frank Kabel]

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"

 

[Bob Phillips]

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

 

[Frank Kabel]

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

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

 

[Bob Phillips]

Hi Frank,

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

Bob