Cells.Find
 

Hello. I pasted a macro below. An error occurs when the line for Cells.Find
is executed. It is error 91 (object or variable not found). I have
experiemented with the "What" criteria. If the variable string PPMDate is
more than 1 character, the error occurs. If is 1 charter or empty, the error
does not occur. Could someone clue me in on this?

Thanks in advance,
Mike

Private Sub CopyData()

Dim PPMData As String

Dim PPMDate As String

' Get the Escape number

Selection.Offset(0, 9).Select



' If the cell is blank then force to zero

PPMData = ActiveCell.Value

If Not IsNumeric(PPMData) Then PPMData = "0"



Workbooks(myWorkbookName).Activate

Sheets("PPM_Data").Select

PPMDate = Replace(ReadWorkbookName, ".xls", "")

PPMDate = Format(PPMDate, "m/d/yyyy")

Range("A1").Select

'Error 91 Occurs he

Cells.Find(What:=PPMDate, After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Activate



Selection.Offset(0, 5).Select

ActiveCell.Value = PPMData



' Now get the Catch number

Workbooks(ReadWorkbookName).Activate

Selection.Offset(-1, 0).Select



' If the cell is blank then force to zero

PPMData = ActiveCell.Value

If Not IsNumeric(PPMData) Then PPMData = "0"



Workbooks(myWorkbookName).Activate

Selection.Offset(0, -1).Select

ActiveCell.Value = PPMData



End Sub


--
Thanks,
Mike

Cells.Find
 

Your code is doing a find looking for partial matches on your text string. If
no match is found your code will bomb. We can deal with the part where it
bombs without too much difficutly but are you looking for a text string in a
group of actual dates. If that is the case then this code is never going to
work. Actually looking for dates is just generally problematic. If however
you are looking to a text string of the format mm/dd/yyyy in a group of text
strings of similar format then we have a shot at this...
--
HTH...

Jim Thomlinson


"Mike Archer" wrote:

Hello. I pasted a macro below. An error occurs when the line for Cells.Find
is executed. It is error 91 (object or variable not found). I have
experiemented with the "What" criteria. If the variable string PPMDate is
more than 1 character, the error occurs. If is 1 charter or empty, the error
does not occur. Could someone clue me in on this?

Thanks in advance,
Mike

Private Sub CopyData()

Dim PPMData As String

Dim PPMDate As String

' Get the Escape number

Selection.Offset(0, 9).Select



' If the cell is blank then force to zero

PPMData = ActiveCell.Value

If Not IsNumeric(PPMData) Then PPMData = "0"



Workbooks(myWorkbookName).Activate

Sheets("PPM_Data").Select

PPMDate = Replace(ReadWorkbookName, ".xls", "")

PPMDate = Format(PPMDate, "m/d/yyyy")

Range("A1").Select

'Error 91 Occurs he

Cells.Find(What:=PPMDate, After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Activate



Selection.Offset(0, 5).Select

ActiveCell.Value = PPMData



' Now get the Catch number

Workbooks(ReadWorkbookName).Activate

Selection.Offset(-1, 0).Select



' If the cell is blank then force to zero

PPMData = ActiveCell.Value

If Not IsNumeric(PPMData) Then PPMData = "0"



Workbooks(myWorkbookName).Activate

Selection.Offset(0, -1).Select

ActiveCell.Value = PPMData



End Sub


--
Thanks,
Mike