| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACROS DATES
I try to the macro reconize the date as format mm/dd/yyyy but is given me an
error, and i only need to reconize the date and not the time, i had a column E its showing dates, times, and names. I just need the macro to reconize the date and copy and paste another column. Can some one help me please please Range("e1").Select 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente Do While d < 1 With Application d = mm / dd / yyyy Do If ActiveCell.Value = d Then ActiveCell.Copy ActiveCell.Offset(1, -4).Activate ActiveCell.PasteSpecial ActiveCell.Offset(0, 4).Activate If ActiveCell.Value = "BLANK" Then Else ActiveCell.Offset(1, 0).Activate End If Application.CutCopyMode = False Call INFORMATIONFOR_EOD2 End If Loop End With Loop End Sub here is the column E 12/28/09 AREVALO 15:30 17:15 CANTU 14:30 17:45 HERNANDEZ 20:00 VALDEZ 14:30 17:45 VILLABOS 20:00 HERNANDEZ 20:00 HERNANDEZ 20:00 JONES 16:00 18:45 LARSON 20:00 CANTU 11:30 13:45 GARCIA 15:30 18:00 GARZEZ 11:30 14:00 PONCE 20:00 SANDOVAL 11:30 15:00 12/28/09 SEGOVIA 16:00 18:45 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MACROS DATES
Hi
You can use the IsDate() function to find dates: Sub aaa() 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente LastRow = Range("E1").End(xlDown).Row For Each cell In Range("E1:E" & LastRow) If IsDate(cell.Value) Then cell.Copy Destination:=cell.Offset(1, -4) Call INFORMATIONFOR_EOD2 End If Next End Sub Hopes this helps. .... Per On 27 Dec., 17:31, Noe wrote: I try to the macro reconize the date as format mm/dd/yyyy but is given me an error, and i only need to reconize the date and not the time, i had a column E its showing dates, times, and names. I just need the macro to reconize the date and copy and paste another column. Can some one help me please please Range("e1").Select * * 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente Do While d < 1 With Application d = mm / dd / yyyy Do If ActiveCell.Value = d Then * * * * * * * * ActiveCell.Copy * * * ActiveCell.Offset(1, -4).Activate * * * *ActiveCell.PasteSpecial * * * *ActiveCell.Offset(0, 4).Activate * * If ActiveCell.Value = "BLANK" Then * * Else * * * * ActiveCell.Offset(1, 0).Activate * * End If Application.CutCopyMode = False Call INFORMATIONFOR_EOD2 End If Loop End With Loop End Sub here is the column E 12/28/09 * * * * * * * * * * * * * AREVALO 15:30 17:15 CANTU 14:30 17:45 HERNANDEZ 20:00 VALDEZ 14:30 17:45 VILLABOS 20:00 HERNANDEZ 20:00 HERNANDEZ 20:00 JONES 16:00 18:45 LARSON 20:00 CANTU 11:30 * *13:45 GARCIA 15:30 18:00 GARZEZ 11:30 14:00 PONCE 20:00 SANDOVAL 11:30 15:00 12/28/09 * * * * * * * * * * * * * SEGOVIA 16:00 18:45 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MACROS DATES
Hi Per Jessen,
Thank you for your help, i just have a question because the macro still given me the time and i just need the dates on that column, there is any way to have only the dates? Thank you again "Per Jessen" wrote: Hi You can use the IsDate() function to find dates: Sub aaa() 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente LastRow = Range("E1").End(xlDown).Row For Each cell In Range("E1:E" & LastRow) If IsDate(cell.Value) Then cell.Copy Destination:=cell.Offset(1, -4) Call INFORMATIONFOR_EOD2 End If Next End Sub Hopes this helps. .... Per On 27 Dec., 17:31, Noe wrote: I try to the macro reconize the date as format mm/dd/yyyy but is given me an error, and i only need to reconize the date and not the time, i had a column E its showing dates, times, and names. I just need the macro to reconize the date and copy and paste another column. Can some one help me please please Range("e1").Select 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente Do While d < 1 With Application d = mm / dd / yyyy Do If ActiveCell.Value = d Then ActiveCell.Copy ActiveCell.Offset(1, -4).Activate ActiveCell.PasteSpecial ActiveCell.Offset(0, 4).Activate If ActiveCell.Value = "BLANK" Then Else ActiveCell.Offset(1, 0).Activate End If Application.CutCopyMode = False Call INFORMATIONFOR_EOD2 End If Loop End With Loop End Sub here is the column E 12/28/09 AREVALO 15:30 17:15 CANTU 14:30 17:45 HERNANDEZ 20:00 VALDEZ 14:30 17:45 VILLABOS 20:00 HERNANDEZ 20:00 HERNANDEZ 20:00 JONES 16:00 18:45 LARSON 20:00 CANTU 11:30 13:45 GARCIA 15:30 18:00 GARZEZ 11:30 14:00 PONCE 20:00 SANDOVAL 11:30 15:00 12/28/09 SEGOVIA 16:00 18:45 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MACROS DATES
Try it this way...
Sub aaa() ' aqui agreagaste el paso de copiar la ' macro para poder pegar verticalmente LastRow = Range("E1").End(xlDown).row For Each cell In Range("E1:E" & LastRow) If IsDate(cell.Value) Then If Int(cell.Value) Then cell.Copy Destination:=cell.Offset(1, -4) Call INFORMATIONFOR_EOD2 End If End If Next End Sub -- Rick (MVP - Excel) "Noe" wrote in message ... Hi Per Jessen, Thank you for your help, i just have a question because the macro still given me the time and i just need the dates on that column, there is any way to have only the dates? Thank you again "Per Jessen" wrote: Hi You can use the IsDate() function to find dates: Sub aaa() 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente LastRow = Range("E1").End(xlDown).Row For Each cell In Range("E1:E" & LastRow) If IsDate(cell.Value) Then cell.Copy Destination:=cell.Offset(1, -4) Call INFORMATIONFOR_EOD2 End If Next End Sub Hopes this helps. .... Per On 27 Dec., 17:31, Noe wrote: I try to the macro reconize the date as format mm/dd/yyyy but is given me an error, and i only need to reconize the date and not the time, i had a column E its showing dates, times, and names. I just need the macro to reconize the date and copy and paste another column. Can some one help me please please Range("e1").Select 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente Do While d < 1 With Application d = mm / dd / yyyy Do If ActiveCell.Value = d Then ActiveCell.Copy ActiveCell.Offset(1, -4).Activate ActiveCell.PasteSpecial ActiveCell.Offset(0, 4).Activate If ActiveCell.Value = "BLANK" Then Else ActiveCell.Offset(1, 0).Activate End If Application.CutCopyMode = False Call INFORMATIONFOR_EOD2 End If Loop End With Loop End Sub here is the column E 12/28/09 AREVALO 15:30 17:15 CANTU 14:30 17:45 HERNANDEZ 20:00 VALDEZ 14:30 17:45 VILLABOS 20:00 HERNANDEZ 20:00 HERNANDEZ 20:00 JONES 16:00 18:45 LARSON 20:00 CANTU 11:30 13:45 GARCIA 15:30 18:00 GARZEZ 11:30 14:00 PONCE 20:00 SANDOVAL 11:30 15:00 12/28/09 SEGOVIA 16:00 18:45 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MACROS DATES
Thank you so much for the help
"Rick Rothstein" wrote: Try it this way... Sub aaa() ' aqui agreagaste el paso de copiar la ' macro para poder pegar verticalmente LastRow = Range("E1").End(xlDown).row For Each cell In Range("E1:E" & LastRow) If IsDate(cell.Value) Then If Int(cell.Value) Then cell.Copy Destination:=cell.Offset(1, -4) Call INFORMATIONFOR_EOD2 End If End If Next End Sub -- Rick (MVP - Excel) "Noe" wrote in message ... Hi Per Jessen, Thank you for your help, i just have a question because the macro still given me the time and i just need the dates on that column, there is any way to have only the dates? Thank you again "Per Jessen" wrote: Hi You can use the IsDate() function to find dates: Sub aaa() 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente LastRow = Range("E1").End(xlDown).Row For Each cell In Range("E1:E" & LastRow) If IsDate(cell.Value) Then cell.Copy Destination:=cell.Offset(1, -4) Call INFORMATIONFOR_EOD2 End If Next End Sub Hopes this helps. .... Per On 27 Dec., 17:31, Noe wrote: I try to the macro reconize the date as format mm/dd/yyyy but is given me an error, and i only need to reconize the date and not the time, i had a column E its showing dates, times, and names. I just need the macro to reconize the date and copy and paste another column. Can some one help me please please Range("e1").Select 'aqui agreagaste el paso de copiar la macro para poder pegar verticalmente Do While d < 1 With Application d = mm / dd / yyyy Do If ActiveCell.Value = d Then ActiveCell.Copy ActiveCell.Offset(1, -4).Activate ActiveCell.PasteSpecial ActiveCell.Offset(0, 4).Activate If ActiveCell.Value = "BLANK" Then Else ActiveCell.Offset(1, 0).Activate End If Application.CutCopyMode = False Call INFORMATIONFOR_EOD2 End If Loop End With Loop End Sub here is the column E 12/28/09 AREVALO 15:30 17:15 CANTU 14:30 17:45 HERNANDEZ 20:00 VALDEZ 14:30 17:45 VILLABOS 20:00 HERNANDEZ 20:00 HERNANDEZ 20:00 JONES 16:00 18:45 LARSON 20:00 CANTU 11:30 13:45 GARCIA 15:30 18:00 GARZEZ 11:30 14:00 PONCE 20:00 SANDOVAL 11:30 15:00 12/28/09 SEGOVIA 16:00 18:45 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dates in macros | Excel Programming | |||
| dates in macros | Excel Programming | |||
| weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
| How do I convert Dates from Text back to Date format using macros | Excel Programming | |||
| Using dates in macros | Excel Programming | |||
Linear Mode
