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
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.
Correct answer by Zer0Kelvin on November 30, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP