|
Loop through selected cells
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 -- casperlov ----------------------------------------------------------------------- casperlove's Profile: http://www.excelforum.com/member.php...fo&userid=1505 View this thread: http://www.excelforum.com/showthread.php?threadid=26674 |
Loop through selected cells
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 |
Loop through selected cells
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 |
Loop through selected cells
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 |
Loop through selected cells
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 |
| All times are GMT +1. The time now is 12:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com