运行时错误91使用Range.find [英] Run-time error 91 using Range.find

问题描述

解决Nathan_SAV有正确的想法。对于以后的人来说，我将代码更改为

 对于x = 3 To lRow 
在错误恢复Next 
'如果然后Find语句
如果Err.Number<> 0然后
'.... 
结束如果
  

我还在新的VBA和我打错误，我无法弄清楚如何解决它。

我得到一个运行时错误'91'对象变量或在块变量未设置

在获取数据在这里表单中，我有H列中的数字，我需要转移到缺少数据表。该代码应该查看缺少数据表，并在列H中找到空格。如果找到空白，则在缺少数据上搜索A数据中的列A中的获取数据。然后，它从获取数据在这里的值，并将其放入缺少数据。我知道这个描述是令人困惑的，但是如果你看看这些注释应该有帮助的代码。

当我得到运行时错误时，它突出显示S = Range（A： A）....

如果有人可以帮助我，我会很感激。

  Dim x As Integer 
 Dim lRow As Long，S As Long 
 
 Sheets（Missing Data）。激活
'查找最后一行在Missing数据
 lRow = Cells.Find（什么：=*，_ 
之后：=范围（A1），_ 
 LookAt：= xlPart，_ 
 LookIn： = xlFormulas，_ 
 SearchOrder：= xlByRows，_ 
 SearchDirection：= xlPrevious，_ 
 MatchCase：= False）.Row 
 
对于x = 3 To lRow 
'搜索没有SAP＃的所有单元格
如果单元格（x，8）=然后
表单（获取数据在这里）激活
范围（A1 ）.Activate 
'Set S等于发现SQP＃的行
 S =范围（A：A）。Fi nd（What：= Sheets（Missing Data）。Cells（x，1），_ 
 After：= ActiveCell，_ 
 LookAt：= xlWhole，_ 
 LookIn：= xlFormulas， _ 
 SearchOrder：= xlByRows，_ 
 SearchDirection：= xlNext，_ 
 MatchCase：= False）.Row 
'将空白单元格的值更改为列H中的值
表单（缺少数据）单元格（x，8）.Value =表格（获取数据在这里）单元格（S，8）
'如果更改单元格颜色为黄色
如果表格（缺少数据）。单元格（x，8） 然后
 Sheets（Missing Data）。Cells（x，8）.Interior.ColorIndex = 6 
 End If 
 End If 
 Next x 
  

解决方案

可能找不到数据，所以尝试

  Dim S as long 
 Dim R as excel.range 
 set R = Range（A：A）。找到
如果R没有，那么
'没有找到，处理
 else 
 S = R.row 
 end if 
  

SOLVED Nathan_SAV had the right idea. For anyone looking at this for reference later I changed the code to

For x = 3 To lRow
On Error Resume Next
'If then Find statement
If Err.Number <> 0 Then
'....
End If

I'm still new to VBA and I hit an error and I can't figure out how to fix it.

I am getting a "run-time error '91' Object variable or With block variable not set"

On the "Get data here" sheet I have numbers in Column H that I need to transfer to the "Missing Data" sheet. This code is supposed to look at the "Missing Data" sheet and find blanks in column H. If it does find a blank it searches "Get data here" for the value of the cell in Column A on "Missing Data". Then it takes the value from "Get data here" and places it into "Missing Data". I realize this description is confusing but if you look at the code the comments should help.

When I get the run time error it is highlighting S = Range("A:A")....

If anyone could help me out I would appreciate it.

    Dim x As Integer
    Dim lRow As Long, S As Long

    Sheets("Missing Data").Activate
    'Find last row in Missing Data
    lRow = Cells.Find(What:="*", _
        After:=Range("A1"), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row

    For x = 3 To lRow
        'Search for all cells without an SAP#
        If Cells(x, 8) = "" Then
            Sheets("Get data here").Activate
            Range("A1").Activate
            'Set S equal to the row where the SQP# was found
            S = Range("A:A").Find(What:=Sheets("Missing Data").Cells(x, 1), _
                After:=ActiveCell, _
                LookAt:=xlWhole, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False).Row
            'Change the value of the blank cell to the value in column H
            Sheets("Missing Data").Cells(x, 8).Value = Sheets("Get data here").Cells(S, 8)
            'Change the cell color to yellow if it changed
            If Sheets("Missing Data").Cells(x, 8) <> "" Then
                Sheets("Missing Data").Cells(x, 8).Interior.ColorIndex = 6
            End If
        End If
    Next x

解决方案

The data may not be found, so try

Dim S as long
Dim R as excel.range 
set R=Range("A:A").Find
if R is nothing then 
    'Not found, handle
else
    S=R.row
end if

这篇关于运行时错误91使用Range.find的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！