Beginner question (I hope)
 

Hey guys I've been in other areas of this forum (can even answer a few
questions) but I'm not much when it comes to visual basic so here is
my question...

I have a small macro to format a few ugly reports and I have one
problem, the Date column moves from report to report and the date
comes in the general format (i.e. 38098) so I put together this little
macro :

Rows("1:1").Select '(headers are always row 1)
Selection.Find(What:="date", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
ActiveCell.Offset(1, 0).Range("A1").Activate
Selection.NumberFormat = "mm/dd/yy"

My problem is that IF there is no date it of course fails and a pop up
window comes up Since I have to share this macro with others I was
wondering if there is any way to put in a statement that if the Find
does not find anything if it can just end. This is the last of the
lines of code so if it is just a full stop that is ok. If we were in
Excel I'd put something like IF(ISERROR(find) = true, STOP,highlight
and format the column) but I'm not sure how to do that in visual
basic.

Any help would be much appreciated and thanks in advance for any help.

Chad

Beginner question (I hope)
 

Chad,

Use firnd to set a range, and check for its existence prior to the
formatting step:

Dim myCell As Range
Set myCell = Rows("1:1").Find(What:="date", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not myCell Is Nothing Then
myCeell.EntireColumn.NumberFormat = "mm/dd/yy"
End if

HTH,
Bernie
MS Excel MVP

"chadt74" wrote in message
m...
Hey guys I've been in other areas of this forum (can even answer a few
questions) but I'm not much when it comes to visual basic so here is
my question...

I have a small macro to format a few ugly reports and I have one
problem, the Date column moves from report to report and the date
comes in the general format (i.e. 38098) so I put together this little
macro :

Rows("1:1").Select '(headers are always row 1)
Selection.Find(What:="date", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Columns("A:A").EntireColumn.Select
ActiveCell.Offset(1, 0).Range("A1").Activate
Selection.NumberFormat = "mm/dd/yy"

My problem is that IF there is no date it of course fails and a pop up
window comes up Since I have to share this macro with others I was
wondering if there is any way to put in a statement that if the Find
does not find anything if it can just end. This is the last of the
lines of code so if it is just a full stop that is ok. If we were in
Excel I'd put something like IF(ISERROR(find) = true, STOP,highlight
and format the column) but I'm not sure how to do that in visual
basic.

Any help would be much appreciated and thanks in advance for any help.

Chad

Beginner question (I hope)
 

Bernie, thanks so much for your help. With your code I was able to
get the results that I needed and even add a few more searches to my
macro.

Thanks again!!