How to find (VB6 & Excel)

[jovialjohn]

I am writing a small program to populate an excel spreadsheet.
Items are sent out and the program finds the corresponding entry
in the spreadsheet and enters the date sent and who it was sent to.
When the item is returned the program records who it came from
and the date it was returned.

As long as the "find" finds a match - no problem - if there is
NO match I get Run-time error 91
Object variable or With block variable not set.

Code section is below


For i = 0 To 14
intCol = 1
If strDieIn(i) <> "" Then
Cells.Find(What:=strDieIn(i), After:=ActiveCell, _
LookIn:=xlValues, LookAt:= xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
intRow = ActiveCell.Row
Cells(intRow, intCol).Select
With Sheets("AllTheDies")
.Cells(intRow, intCol + 3).Value = theDateIn
.Cells(intRow, intCol + 4).Value = "YES"
.Cells(intRow, intCol + 5).Value = cboInVendor
.Cells(intRow, intCol + 7).Value = theDateIn
End With
End If
Next i

What to do?

[HansV]

You can assign the result of Find to a Range variable and check if it is Nothing:

Dim rng As Range
For i = 0 To 14
intCol = 1
If strDieIn(i) <> "" Then
Set rng = Cells.Find(What:=strDieIn(i), After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
intRow = rng.Row
With Sheets("AllTheDies")
.Cells(intRow, intCol + 3).Value = theDateIn
.Cells(intRow, intCol + 4).Value = "YES"
.Cells(intRow, intCol + 5).Value = cboInVendor
.Cells(intRow, intCol + 7).Value = theDateIn
End With
End If
End If
Next i

[pieterse]

You

[jovialjohn]

Thanks so much Hans that solved the problem for me.

You Rock!