excel - 为什么在使用 Find 方法 vba 时出现错误运行时“91”
问题描述
每次尝试在某个范围内使用 find 方法时,都会收到运行时错误“91”。我有一个日期数组(“A1:A5”),我通过使用 max 方法从中获得最高的日期,但是我想获得另一个值,即 B 列(“B1:B5”)并且在同一行我之前发现的最高日期。
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 返回一个范围对象;如果搜索失败,它什么也不返回。你对它的使用是不正确的;您应该将 Find 返回的值分配给 range 类型的变量,检查该变量是否不包含 Nothing,然后才对其执行操作。
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 不适用于日期。
推荐阅读
- deployment - Can we run terraform import in Azure DevOps release pipeline
- python - Seaborn, how to draw 2 lines from same dataframe
- css - Tailwind:图像顶部居中的 div
- c++ - 通过朋友将构造函数添加到 std::vector
- teradata - BTEQ Keywords in Snowflake SnowSQL
- flutter - Issue in uploading an image as binary body in Flutter. Always showing as corrupted
- r - why in sabreR library if i running multivariate linear mixed models with three models thats found error
- python-3.x - 10 <=n<=1 和 n<=1 或 n>=10 有什么区别?
- generics - Tell the Rust compiler when two generic types are the same
- arrays - Removing specific Value from an array using .remove() . But why im not getting the correct answer?