Find的奇怪行为 [英] Weird Behavior with Find

问题描述

我想让这个小脚本工作。

如果searchVal = findVal则
设置rngX =工作表（"新格式"）。范围（"C：C"）。查找（What：= resultVal，LookIn：= xlValues）
 ID = rngX.Offset（0，-1）。值
结束如果

我知道'searchVal'是的，我知道'findVal'是正确的，我知道'resultVal'是正确的。 此外，rngX被定义为范围。 但是，每次运行时，rngX始终为Nothing。 该ID应设置为找到
'recomeVal'的行，左边是一个单元格。

我也试过了。

如果searchVal = findVal则
范围（"C2：C"& LRow）。选择
对于选择中的每个单元格
 cell.Select 
如果ActiveCell.Value = resultVal则
 ID = cell.Offset（0，-1）.Value 
结束如果
下一个
结束如果

这也不起作用。

我在这里缺少什么？

---

我的书

解决方案

您好
ryguy72，

以下是我修改的第一个例子。

 Sub demo（）
 Dim searchVal，findVal，resultVal As String 
 Dim rngX As Range 
 Dim ID As Integer 
 searchVal = 1 
 findVal = 1 
 resultVal ="xyz" 
如果searchVal = findVal那么
设置rngX =工作表（"新格式"）。范围（"C：C"）。查找（What：= resultVal，LookIn：= xlValues）
 ID = rngX.Offset（0，-1）.Value 
 MsgBox（ID）
 End if 
 End Sub 
 

<下面是输出：

以下是我修改的第二个例子。

 Sub demo2（）
 Dim searchVal，findVal，resultVal As String 
 Dim LRow As Long 
 Dim cell As Range 
 LRow = Worksheets（" NEW Format"）。单元格（工作表（"新格式"）。Rows.Count，"C"）。End（xlUp）.Row 
 resultVal =" xyz" 
 Dim ID As String 
如果searchVal = findVal则
范围（"C2：C"& LRow）。选择
对于选择
单元格中的每个单元格。选择
如果ActiveCell.Value = resultVal那么
 ID = cell.Offset（0，-1）.Value 
 MsgBox（ID）
结束如果
下一个
结束如果
结束子

输出：

这两个代码示例都符合您的要求。

您提到过您"rngX"中没有任何内容。这意味着您的" resultVal "与单元格值不匹配。

确保匹配。如果不匹配，你什么也得不到。

问候

Deepak

I am trying to get this small script to work.

         If searchVal = findVal Then
            Set rngX = Worksheets("NEW Format").Range("C:C").Find(What:=resultVal, LookIn:=xlValues)
            ID = rngX.Offset(0, -1).Value
         End If

I know the 'searchVal' is correct, I know the 'findVal' is correct, and I know the 'resultVal' is correct.  Also, rngX is defined as a range.  However, each time this runs, the rngX is always Nothing.  The ID should be set as the row where 'resultVal' is found, one cell to the left.

I tried this as well.

         If searchVal = findVal Then
            Range("C2:C" & LRow).Select
            For Each cell In Selection
             cell.Select
                If ActiveCell.Value = resultVal Then
                    ID = cell.Offset(0, -1).Value
                End If
            Next
         End If

That didn't work either.

What am I missing here?

---

MY BOOK

解决方案

Hi ryguy72,

Below is your 1st example modified by me.

Sub demo()
Dim searchVal, findVal, resultVal As String
Dim rngX As Range
Dim ID As Integer
searchVal = 1
findVal = 1
resultVal = "xyz"
 If searchVal = findVal Then
            Set rngX = Worksheets("NEW Format").Range("C:C").Find(What:=resultVal, LookIn:=xlValues)
            ID = rngX.Offset(0, -1).Value
            MsgBox (ID)
         End If
End Sub

below is the output:

below is the 2nd example of yours modified by me.

Sub demo2()
Dim searchVal, findVal, resultVal As String
Dim LRow As Long
Dim cell As Range
LRow = Worksheets("NEW Format").Cells(Worksheets("NEW Format").Rows.Count, "C").End(xlUp).Row
resultVal = "xyz"
Dim ID As String
    If searchVal = findVal Then
            Range("C2:C" & LRow).Select
            For Each cell In Selection
             cell.Select
                If ActiveCell.Value = resultVal Then
                    ID = cell.Offset(0, -1).Value
                    MsgBox (ID)
                End If
            Next
         End If
End Sub

Output:

both code example are working as your requirement.

you had mentioned you are getting nothing in "rngX". it means that your "resultVal" is not matched with the cell value.

make sure that it get matched. if it not matched you will get nothing.

Regards

Deepak

这篇关于Find的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！