why do I get an error runtime '91' when using Find method vba

Stack Overflow Asked by Santiago on November 30, 2020

I get a run-time error ’91’ everytime I try to use the find method in a range. I have an array of dates("A1:A5") from which I get the highest one by using a max method, but then I want to get another value which is column B ("B1:B5") and in the same row of the highest date I have found before.

Dim wkbFrom As Workbook
Dim wsmb As Sheets
Dim MBSheet2 As Worksheet
Dim rowTMO as long
Dim carica_range As Range
Dim TMOcarica_range As Range

'Define The MB worksheet
Set wkbFrom = Workbooks.Open(MBPath)
Set wsmb = wkbFrom.Worksheets
Set MBSheet2 = wsmb(CStr("Foglio1"))

For m = 2 To row_counter
Set TMOcarica_range = DataSheet.Cells(m, DataSheet.Range("TMO_carica").Column)
Set carica_range = DataSheet.Cells(m, DataSheet.Range("data_carico_magazzino").Column)

'here I put some other code that fills the respective values in the MBSheet2.("A1:B5") arrange

'Find the max value in the ("A1:A5") array and give the values date format
carica_range.value = WorksheetFunction.Max(MBSheet2.Range("A1:A5"))
carica_range.NumberFormat = "dd/mm/yyyy"
MBSheet2.Range("A1:A5").NumberFormat = "dd/mm/yyyy"
rowTMO = MBSheet2.Columns(1).Find(carica_range, LookIn:=xlValues).Row
TMOcarica_range.Value = MBSheet2.Cells(rowTMO, "B").Value

Next m

One Answer

Range.Find returns a range object; if the search fails it returns nothing. The use you make of it is not correct; you should assign the value returned by Find to a variable of type range, check that the variable does not contain Nothing and, only then, perform operations on it.

Dim wkbFrom As Workbook
Dim wsmb As Sheets
Dim MBSheet2 As Worksheet
Dim rowTMO As Long
Dim load_range As Range
Dim TMOload_range As Range
Dim Fnd As Range

    load_range.Value = WorksheetFunction.Max (MBSheet2.Range ("A1: A5"))
    load_range.NumberFormat = "dd / mm / yyyy"
    MBSheet2.Range ("A1: A5"). NumberFormat = "dd / mm / yyyy"
    Set Fnd = MBSheet2.Columns (1) .Find (load_range, LookIn: = xlValues)
    If Not Fnd Is Nothing Then
        rowTMO = Fnd.Row
        TMOload_range.Value = MBSheet2.Cells (rowTMO, "B"). Value
        MsgBox "Not found"
    End If

PS: Range.Find doesn't work well with dates.

Correct answer by Zer0Kelvin on November 30, 2020

Add your own answers!

Related Questions

“Live” data capable alternative for Google Earth KML

1  Asked on February 15, 2021 by christoph1197


React component dispatching generic payload

1  Asked on February 14, 2021 by andyroo


CSS animted gradient

1  Asked on February 14, 2021


Running a value through an array of functions

2  Asked on February 14, 2021 by lars-holdaas


How to revert back to python 2.7?

1  Asked on February 14, 2021 by nikhil-shrivastava


Using same name for attribute and getter

1  Asked on February 13, 2021 by janpeterka


What is the purpose to use className beside functionName using colon(:)

1  Asked on February 13, 2021 by mahedi-hasan-durjoy


Python: access a variable in a module from another file

2  Asked on February 13, 2021 by yeroduk


How to get elements of a sublist in Java

5  Asked on February 13, 2021 by alvira


`SyntaxError` in if-else one-liner

2  Asked on February 13, 2021 by evgeniy-golovin


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir