Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to find (VB6 & Excel)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to find (VB6 & Excel)

    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

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to find (VB6 & Excel)

    You
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Jun 2002
    Location
    Toronto, Ontario
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to find (VB6 & Excel)

    Thanks so much Hans that solved the problem for me.

    You Rock!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •