如何在列中查找文本并保存首次找到的行号 - Excel VBA [英] How to find text in a column and saving the row number where it is first found - Excel VBA
问题描述
我有以下列(列A)命名为project(行列只显示行号):
I have the following column (column A) named project (rows column is just displaying the row number):
rows project
1 14
2 15
3 16
4 17
5 18
6 19
7 ProjTemp
8 ProjTemp
9 ProjTemp
我有一个输入消息框,用户在其中写入新项目名称,想要最后一个插入。例如:项目20将在项目19之后和第一个ProjTemp之前插入。
I have an input message box where the user writes the new project name which I want inserted right after the last one. Ex: project 20 will be inserted right after project 19 and before the first "ProjTemp".
我的理论是找到第一个ProjTemp的行号,然后插入项目为20的新行。
My theory was to locate the row number of the first "ProjTemp" and then insert a new row where the project is 20.
我正在尝试使用Find函数,但是我遇到溢出错误(我确定我得到了因为它找到3个ProjTemp字符串,并尝试将其设置为一个参数):
I was trying to use the Find function but I'm getting an overflow error (I'm sure I'm getting it because it's finding 3 "ProjTemp" strings and trying to set it to one parameter):
Dim FindRow as Range
with WB.Sheets("ECM Overview")
Set FindRow = .Range("A:A").Find(What:="ProjTemp", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
end with
我如何编写代码,所以我只找到拳头ProjTemp ?
有没有更好的方式来做这个,也许是一个循环?
How do I code this so I only find the row number of the fist "ProjTemp"? Is there a better way to do this, maybe a loop?
谢谢,任何帮助将不胜感激!
Thanks, any help will be appreciated!
推荐答案
我不太熟悉 Find
方法的所有参数;但是在缩短之后,以下内容正在为我工作:
I'm not really familiar with all those parameters of the Find
method; but upon shortening it, the following is working for me:
With WB.Sheets("ECM Overview")
Set FindRow = .Range("A:A").Find(What:="ProjTemp", LookIn:=xlValues)
End With
如果你只需要行号,你可以使用这个:
And if you solely need the row number, you can use this after:
Dim FindRowNumber As Long
.....
FindRowNumber = FindRow.Row
这篇关于如何在列中查找文本并保存首次找到的行号 - Excel VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!