excel - 试图在指定列中查找值
问题描述
以前的问题https://www.mrexcel.com/board/threads/run-time-error-13.1163596/page-2#posts
试图在 C 列“WINGS”中找到一个指定的值,然后穿过 3 列然后向下 1 列,并用一个值填充该单元格。这是一个案例功能,见下文
Private Sub TextBox6_Change() Dim FirstAddress As String Dim MyArr As Variant Dim Rng As Range Dim ws As Worksheet Dim I As Long
Set ws = ThisWorkbook.Worksheets("Job Card Master")
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Search for a Value Or Values in a range
'You can also use more values like this Array("Dove", "Sky")
MyArr = Array("WINGS")
'Search Column or range
With ws.Range("C:C")
For I = LBound(MyArr) To UBound(MyArr)
'If you want to find a part of the rng.value then use xlPart
'if you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "ron"
Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
'mark the cell in the column to the right if "WING" is found
'Rng.Offset(1, 3).Value = "X"
Select Case Me.TextBox6.Value
Case ("Transit")
Rng.Offset(1, 3).Value = "550mm"
Case ("Sprinter")
Rng.Offset(1, 3).Value = "550mm"
Case ("Master")
Rng.Offset(1, 3).Value = "465mm"
Case ("Movano")
Rng.Offset(1, 3).Value = "465mm"
Case ("NV400")
Rng.Offset(1, 3).Value = "465mm"
Case ("Boxer")
Rng.Offset(1, 3).Value = "465mm"
Case ("Ducato")
Rng.Offset(1, 3).Value = "465mm"
Case ("Relay")
Rng.Offset(1, 3).Value = "465mm"
End Select
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next I
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
结束子
解决方案
我想出了以下解决方案:
Private Sub TextBox6_Change()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim I As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Search for a Value Or Values in a range
'You can also use more values like this Array("Dove", "Sky")
MyArr = Array("WINGS")
'Search Column or range
With Sheets("Blad1").Range("C:C")
For I = LBound(MyArr) To UBound(MyArr)
'If you want to find a part of the rng.value then use xlPart
'if you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "ron"
Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
'mark the cell in the column to the right if "WING" is found
'Rng.Offset(1, 3).Value = "X"
Select Case Me.TextBox6.Value
Case ("Transit")
Rng.Offset(1, 3).Value = "550mm"
Case ("Sprinter")
Rng.Offset(1, 3).Value = "550mm"
Case ("Master")
Rng.Offset(1, 3).Value = "465mm"
Case ("Movano")
Rng.Offset(1, 3).Value = "465mm"
Case ("NV400")
Rng.Offset(1, 3).Value = "465mm"
Case ("Boxer")
Rng.Offset(1, 3).Value = "465mm"
Case ("Ducato")
Rng.Offset(1, 3).Value = "465mm"
Case ("Relay")
Rng.Offset(1, 3).Value = "465mm"
End Select
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next I
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
推荐阅读
- android - 应用因违反家庭政策 Play 商店被拒绝
- c++ - 服务器套接字在后台运行时无法正常工作
- python - 如何使用 postgres 在 django 中获取数组字段数据
- python - 如何使用 django 和 GeoIP2 保存和显示用户 IP 和位置
- elasticsearch - elasticsearch中多个字段的聚合
- arrays - 具有特定类型的对象数组
- c - A Little C 程序中的一些问题
- scala - spark scala maven 错误:SparkConf 没有构造函数
- node.js - 在 Hyperledger Fabric NodeJS 智能合约中实例化链码后,如何防止调用“init”函数?
- angular - ng v 命令给出错误:“路径”参数必须是字符串、缓冲区或 URL 类型之一。接收到的类型对象'