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

Question

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

Zer0Kelvin · Accepted 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
    Else
        MsgBox "Not found"
    End If

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

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

One Answer

Add your own answers!

Ask a Question