PDA

View Full Version : Date format



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!

Simon Lloyd
07-18-2008, 12:52 AM
It's because you are only working with the activecell and the activecell doesn't move!, you are not progressing through cells in your code, A1 is always your activecell so you are only formatting the offsets with relation to it.

Bob Phillips
07-18-2008, 04:51 AM
I haven't looked in detail at the code, but you seem to have commented out the one line that moves the activecell, but seeing as it moves it up a row, and you start in row 1, that probab ly caused you a problem.

bear
07-20-2008, 06:16 PM
okay. thanks.
now that i know the problem, i can solve it. thanks a lot!