Excel VBA .find works on first macro run but not second

Question

I open my workbook and run my macro and all code (that I can tell) executes fine. Without changing any data I try and run the macro again and I get an error. The error specifically happens with .Find and is returning Nothing when in fact there is a date to be found in the specified range.

I have gone through the debug tool and by all accounts I do not know why .Find is returning Nothing on the second Macro run and not the first.

The line in question is:

Set DateFind = .Find(what:=TripFind.Offset(0, 2).Value, LookAt:=xlWhole, _
                     MatchCase:=False, SearchFormat:=False)

The first time running the Macro DateFind Returns the correct value. The second time the Macro is run DateFind Returns Nothing but on all accounts should be returning the same value as on the first run of the Macro.

Here is the full code section that is in question: **The first part of the code runs fine. The second part starting at 'Adds day numbers... etc. is the important part.*

ElseIf TripCal.Range("A1") = "SESSION 2" Then

        '-----Copies and pastes cabin numbers into tripcal-----
    For TotalRowsOffered = 5 To 168
        If TotalRowsOffered >= Level1Offered And TotalRowsOffered < Level2Offered And TripsOffered.Cells(TotalRowsOffered, Session2) <> "" Then
            a = TotalRowsOffered - 6
            TripCal.Cells(TotalRowsOffered + a, "B") = TripsOffered.Cells(TotalRowsOffered, Session2).Value
        ElseIf TotalRowsOffered >= Level2Offered And TotalRowsOffered < Level3Offered And TripsOffered.Cells(TotalRowsOffered, Session2) <> "" Then
            a = TotalRowsOffered - 8
            TripCal.Cells(TotalRowsOffered + a, "B") = TripsOffered.Cells(TotalRowsOffered, Session2).Value
        ElseIf TotalRowsOffered >= Level3Offered And TotalRowsOffered < Level4Offered And TripsOffered.Cells(TotalRowsOffered, Session2) <> "" Then
            a = TotalRowsOffered - 10
            TripCal.Cells(TotalRowsOffered + a, "B") = TripsOffered.Cells(TotalRowsOffered, Session2).Value
        ElseIf TotalRowsOffered >= Level4Offered And TotalRowsOffered < Level5Offered And TripsOffered.Cells(TotalRowsOffered, Session2) <> "" Then
            a = TotalRowsOffered - 12
            TripCal.Cells(TotalRowsOffered + a, "B") = TripsOffered.Cells(TotalRowsOffered, Session2).Value
        ElseIf TotalRowsOffered >= Level5Offered And TripsOffered.Cells(TotalRowsOffered, Session2) <> "" Then
            a = TotalRowsOffered - 14
            TripCal.Cells(TotalRowsOffered + a, "B") = TripsOffered.Cells(TotalRowsOffered, Session2).Value
        End If
    Next

        '-----Adds day number of trip for each cabin-----
    For TripCounter = 4 To 323
        If TripCal.Cells(TripCounter, "B") = "" Then
                'Skips if there is no trip name accounted for _
                 beside the level on the Trip Calender
        Else
            With TripsOffered.Range(TripsOffered.Cells(5, Session2), TripsOffered.Cells(168, Session2))
                    Set TripFind = .Find(what:=TripCal.Cells(TripCounter, "B"), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
                            If Not TripFind Is Nothing Then
                                Tripdays = TripFind.Offset(0, 4).Value - TripFind.Offset(0, 2).Value
                                    With TripCal.Range(TripCal.Cells(1, 3), TripCal.Cells(1, LastDate))
                                        Set DateFind = .Find(what:=TripFind.Offset(0, 2).Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
                                            If Not DateFind Is Nothing Then
                                                For TripDayCount = 0 To Tripdays
                                                    TripCal.Cells(TripCounter, DateFind.Column + TripDayCount) = TripDayCount + 1
                                                Next
                                            Else
                                                MsgBox ("The Trip Date for " & TripFind.Value & " is outside of the current session dates." & vbNewLine & vbNewLine & "Please check the trip dates in the 'Trips Being Offered' sheet for " & TripFind.Value & " in Session 2.")
                                            End If
                                    End With
                            End If
            End With
        End If
        TripCounter = TripCounter + 1
    Next

ElseIf TripCal.Range("A1") = "SESSION 3" Then
'the above code repeat depending on Range "A1"

Jeremy · Accepted Answer · 2017-02-24 09:21:35Z

2

.Find looks after the active cell by default - if you change that line to Set DateFind = .Find(what:=TripFind.Offset(0, 2).Value, After:=TripsOffered.Range(TripsOffered.Cells(5, Session2), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

that'll check all the cells

edited Feb 24, 2017 at 9:21

answered Feb 23, 2017 at 17:19

Jeremy

1,3373 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Walkintall Over a year ago

Hi @Jeremy, Thank you very much for you speedy response. It is my understanding that while using the 'With' statement above the '.Find' line that I am specifying a range, therefore not resorting to the default. I did however try and add 'After:=Cells(1,1)' and I then came up with a 'Type mismatch (Error 13)'

Walkintall Over a year ago

Thanks so much for your help. I didn't end up trying your edit. I did add 'LookIn:=xlFormulas' as suggested by Docmarti. I did however the first time I tried you suggestion define where to look after more specifically and it also didn't work but I may have been doing something wrong. Thanks again! Keep on Keeping on!

Docmarti · Accepted Answer · 2017-02-24 05:09:12Z

0

The parameters of the Range.Find Method are optional. But when looking for dates, it is highly recommended to always set the Lookin parameter

Set TripFind = .Find(what:=CDate(TripCal.Cells(TripCounter, "B")), LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

If you don’t set a Find parameter it uses the existing setting.

answered Feb 24, 2017 at 5:09

Docmarti

3862 silver badges6 bronze badges

1 Comment

Walkintall Over a year ago

It worked! All I needed to add was the LookIn:=xlFormulas and it now runs on every macro. It is interesting to see the added "CDate" as I don't think I have ever seen that before although I didn't need to add it. The final line of code I ended up using which thus far works fine is: Set DateFind = .Find(what:=TripFind.Offset(0, 2).Value, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

Collectives™ on Stack Overflow

Excel VBA .find works on first macro run but not second

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related