Beginner question (I hope)

C

chadt74

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
 
B

Bernie Deitrick

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
 
C

chadt74

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!!
 
Top