我有一个代码,它的第一个任务是在 P 列中查找“Qty”和“Fty”字样。然后它将获取该行的行号。代码运行顺利。但现在当我添加一个循环时,代码会抛出一个错误。“运行时错误方法查找对象范围失败”我希望循环将获取这两个行号,并且在第一行之后直到最后一行都将打印一个值。
假设您在行号 12 和列号 P 中输入了单词“qty”,并在行号 15 和列号 P 中输入了单词“fty”(这里需要说明的是,输入单元格的列号始终为 P)。循环的工作是从 A13 到 A14 获取值,并在 P13 到 P14 中打印。
这是我的代码:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim qtyWordFinder As Range
Dim qtyWordFinder2 As Range
Dim acidWashQty As Integer
Dim rw1 As Integer
Dim rw2 As Integer
Set qtyWordFinder = Range("$P$1:$P$53").Find("Qty", SearchOrder:=xlColumns)
Set qtyWordFinder2 = Range("$P$1:$P$53").Find("Fty", SearchOrder:=xlColumns)
If qtyWordFinder Is Nothing Then
Else
rw1 = qtyWordFinder.row
End If
If qtyWordFinder2 Is Nothing Then
Else
rw2 = qtyWordFinder2.row
End If
Dim i As Integer
For i = rw1 + 1 To rw2 - 1
Cells(i, 16).Value = Cells(i, 1).Value
Next i
End Sub
请帮忙!
答案1
尝试这个语法
对于消息请求:
If Not Intersect(Target, Range("A1")) Is Nothing Then
Dim WordFinder As Range
Dim rw1 As Range
Dim rw2 As Range
For Each WordFinder In Range("$P$1:$P$53")
If InStr(WordFinder.Value, "qty") Then Set rw1 = Range(WordFinder.Address)
If InStr(WordFinder.Value, "fty") Then Set rw2 = Range(WordFinder.Address)
Next WordFinder
Debug.Print rw1.Value & "-" & rw2.Value
End If
End Sub
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Dim WordFinder As Range
Dim qtyWordFinder2 As Range
Dim rw1 As Integer
Dim rw2 As Integer
For Each WordFinder In Range("$P$1:$P$53")
If InStr(WordFinder.Value, "qty") Then rw1 = WordFinder.Row
If InStr(WordFinder.Value, "fty") Then rw2 = WordFinder.Row
Next WordFinder
If rw1 > 0 And rw2 > 0 Then
Dim i As Integer
For i = rw1 + 1 To rw2 - 1
Cells(i, 16).Value = Cells(i, 1).Value
Next i
End If
End If
End Sub
页面顶部的选项比较文本意味着它将忽略文本中的大小写差异。
我已将其与 A1 中的更改联系起来进行测试