Getting multiple errors using OR with Cells.Find - RTE424 Object Required, RTE13 Type Mismatch, E91 Block Variable not set

Question

This is my first ever S.O. question, so apologies if I go overboard with Wall of Text/Lack of Relevant Info'.

I maintain Macro Excels built by a former employee for P&L by Market and Sales Rep. They pull in data from other packages which now vary in format due to different versions in use. There is also a French-language market. Everything works for English-only (barely) but now they want the French-language market included.

I'm pulling a value from a cell in multiple external files that are not fixed except by it's accompanying labeling text. Using Cells.Find, I am searching for an English phrase and activating the adjacent cell to copy that value into the main Excel. This works.

Now they want the French language version included in this process, and I was attempting a parallel search for the English and French phrases to find which one it is and to take one OR the other, but I am getting successive and cyclic errors upon attempting the solutions found in StackOverflow.

What is wrong with this code?

Sub FindMonthlyValues(ByVal varFile As String, ByRef MonthlyProfitLine As String, ByRef MonthlyTonnageLine As String, ByRef MonthlyTripNumberRange As String)

    Dim CurrentPnL, CurrentPath As String
    Dim k, FirstRow, LastRow As Integer
    Dim ProfitEn, ProfitFr As Range    

    'loop through excel files only
    Application.ScreenUpdating = False

    CurrentPnL = varFile

    k = InStr(CurrentPnL, "[") 'find the end position of the default link
    CurrentPath = Left(CurrentPnL, k - 1)
    CurrentPnL = Replace(CurrentPnL, "[", "")
    CurrentPnL = Replace(CurrentPnL, "]", "")

    ChDir _
        CurrentPath
    Workbooks.Open FileName:= _
        CurrentPnL

    ' Find Monthly Profit Line in Initial sheet
    Sheets("Initial").Select
    Range("A1:AK1").Select
    Set ProfitFr = Cells.Find(What:="PROFIT MENSUEL:", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Value

    If ProfitFr Is Nothing Then
        Set ProfitEn = Cells.Find(What:="Monthly Profit:", After:=ActiveCell, LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Value
    End If

    Cells.FindNext(After:=ActiveCell).Activate
    ActiveCell.Offset(, 1).Select
    MonthlyProfitLine = ActiveCell.Address

I've tried three different StackOverflow solutions but they lead to a circle of different errors as defined in the header.

I don't see an Option Explicit in the code section I'm modifying.

I've tried setting a Dim for new variables to hold the phrase found.

I've tried Set [DimName] = Cells.Find(BLAH).Value from the original (and functional) Cells.Find(BLAH).Activate.

I've tried an If [DimName] Is Nothing Then but it makes no difference to the errors, which happen at the first Cells.Find.

I've tried changing the order of the English or French Cells.Find, but it makes no difference to the errors, which happen at the first Cells.Find.

I've tried changing the Dim from Range to String but only get:

"Object undefined."

What happens if you step through the code? Side note: In general, you will want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. — cybernetic.nomad, Oct 08 '19 at 16:33
Drop the `.value` for starters, you are assigning an object variable, not a value of a cell. — SJR, Oct 08 '19 at 16:39
I am F8-stepping through the code and this is all I am getting. this one Cells.Find highlighted in Debug, and the various error messages stated. It's not super helpful, I know. — Andrew Brown, Oct 08 '19 at 16:40
To declare both as `Range`, you need `Dim ProfitEn As Range, ProfitFr As Range` etc. — SJR, Oct 08 '19 at 16:43
SJR hit the nail on the head with the separate range declarations; I cannot believe this was all it needed to execute without errors. — Andrew Brown, Oct 08 '19 at 17:03
Thanks everyone for the rapid responses and accurate diagnoses! This help is greatly appreciated. I have two other text strings to find per iteration of the macro so I'll apply what I've learned here to them too. — Andrew Brown, Oct 08 '19 at 17:08
Further to earlier, it was also removing both the '.value' and the '.activate' that finally allowed everything to function fully again. — Andrew Brown, Oct 08 '19 at 20:27

score 1 · Answer 1 · answered Oct 08 '19 at 16:56

Your code would benefit a lot from having parts of it factored into standalone Sub/Functions which encapsulate some specific functionality.

Finding text in a range is one obvious candidate for that:

Sub Tester()

    Dim f As Range

    Set f = FindFirst(ActiveSheet.Columns(1), Array("PROFIT MENSUEL:", "Monthly Profit:"))

    If Not f Is Nothing Then
        Debug.Print "Found:" & f.Offset(0, 1).Value
    Else
        Debug.Print "No match"
    End If

End Sub


'Find the first value in What in the range rngSearch
'   Returns Nothing if not matches found
Function FindFirst(SearchWhere As Range, FindWhat) As Range
    Dim f As Range, s
    For Each s In FindWhat
        Set f = SearchWhere.Find(What:=s, lookat:=xlPart, _
                     SearchDirection:=xlNext, MatchCase:=False)
        If Not f Is Nothing Then
            Set FindFirst = f
            Exit For
        End If
    Next s
End Function

By pushing out common tasks into separate methods your main code becomes much easier to develop and maintain.

Also, see if you can work the guidelines here into your code: How to avoid using Select in Excel VBA

Getting multiple errors using OR with Cells.Find - RTE424 Object Required, RTE13 Type Mismatch, E91 Block Variable not set

1 Answers1