bear
07-17-2008, 11:01 PM
Hello,
I want to make my excel column in a date format.
the following are my code, but i not too sure where i go wrong.
The output only format one cell and not the whole column.
Sub Identify_TxnGrouping()
Dim i As Integer, lastfoundrow
Range("A1").Select
Columns("A:B").Select
'Selection.Insert Shift:=xlToRight
Selection.EntireColumn.Insert
lastfoundrow = 0
Range("A1").Select
Do While ActiveCell.Row <= lastrow
i = 0
XLFind "TC=000", "Part"
If Not blnfound Then
Exit Do
End If
If ActiveCell.Row < lastfoundrow Then
Exit Do
End If
'getting the file date
i = InStr(1, ActiveCell.Offset(-8, 0).Value, "FILE DATE", vbTextCompare)
If i > 0 Then
ActiveCell.Offset(1, -1).Value = Format _
(Mid(ActiveCell.Offset(-8, 0).Value, i + 10, 10), "dd-mmm-yyyy")
ActiveCell.Offset(1, -1).NumberFormat = "dd-mm-yyyy"
End If
'ActiveCell.Offset(-1, 0).Select
x = ActiveCell.Offset(-5, 0).Row
y = ActiveCell.Row
If x >= Range("lastdatarow").Row Or y >= Range("lastdatarow").Row Then
ActiveCell.Offset(1, 0).Name = "LastDataRow"
End If
Range(ActiveCell.Offset(-5, 0).Cells, ActiveCell.Cells).Select
Selection.EntireRow.Delete
lastfoundrow = ActiveCell.Row
lastrow = Range("LastDataRow").Row
Loop
lastrow = Range("LastDataRow").Row
End Sub
Thanks!
I want to make my excel column in a date format.
the following are my code, but i not too sure where i go wrong.
The output only format one cell and not the whole column.
Sub Identify_TxnGrouping()
Dim i As Integer, lastfoundrow
Range("A1").Select
Columns("A:B").Select
'Selection.Insert Shift:=xlToRight
Selection.EntireColumn.Insert
lastfoundrow = 0
Range("A1").Select
Do While ActiveCell.Row <= lastrow
i = 0
XLFind "TC=000", "Part"
If Not blnfound Then
Exit Do
End If
If ActiveCell.Row < lastfoundrow Then
Exit Do
End If
'getting the file date
i = InStr(1, ActiveCell.Offset(-8, 0).Value, "FILE DATE", vbTextCompare)
If i > 0 Then
ActiveCell.Offset(1, -1).Value = Format _
(Mid(ActiveCell.Offset(-8, 0).Value, i + 10, 10), "dd-mmm-yyyy")
ActiveCell.Offset(1, -1).NumberFormat = "dd-mm-yyyy"
End If
'ActiveCell.Offset(-1, 0).Select
x = ActiveCell.Offset(-5, 0).Row
y = ActiveCell.Row
If x >= Range("lastdatarow").Row Or y >= Range("lastdatarow").Row Then
ActiveCell.Offset(1, 0).Name = "LastDataRow"
End If
Range(ActiveCell.Offset(-5, 0).Cells, ActiveCell.Cells).Select
Selection.EntireRow.Delete
lastfoundrow = ActiveCell.Row
lastrow = Range("LastDataRow").Row
Loop
lastrow = Range("LastDataRow").Row
End Sub
Thanks!