ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   MACROS DATES (https://www.excelbanter.com/excel-programming/437816-macros-dates.html)

Noe
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

Per Jessen[_2_]
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

Noe
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

.

Rick Rothstein
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

.

Noe
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

.


.


All times are GMT +1. The time now is 02:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com