vba 基于vba中单元格（查找）中可用的值执行while循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16643981/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
do while loop based on a value available in cell (find) in vba
提问by murugan_kotheesan
Hi i am writing a code in vb to check a particular value in a sheet, if the value is not available then it should go back to another sheet to take new value to find, if the value is found i have to do some operation on that sheet i have the below code to find the value in the sheet but if i pass the same in a DO WHILE loop as condition it gives a compile error
嗨，我正在用 vb 编写代码来检查工作表中的特定值，如果该值不可用，则应返回另一张工作表以获取新值以查找，如果找到该值，我必须对其进行一些操作该工作表我有以下代码来查找工作表中的值，但是如果我在 DO WHILE 循环中传递相同的条件作为条件，则会出现编译错误
find vaue code
查找值代码
Selection.Find(What:=last_received, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
could some one please help me to write a code of DO WHILE with the above find in the loop condition so that if the condition gives false (i,e if the value is not found in the sheet) then i should use some other value to find
有人可以帮我写一个 DO WHILE 的代码，上面的 find 在循环条件中，这样如果条件给出 false（即，如果在工作表中找不到值），那么我应该使用其他一些值找
this is the code that i am going to put in loop
这是我要放入循环的代码
Sheets("Extract -prev").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Extract -prev").Sort.SortFields.Clear 'sorting with tickets
ActiveWorkbook.Worksheets("Extract -prev").Sort.SortFields.Add Key:=Range( _
"C2:C2147"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Extract -prev").Sort
.SetRange Range("A1:AB2147")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Goto Reference:="R1C3" 'taking last received ticket
Selection.End(xlDown).Select
Selection.Copy
Sheets("Calc").Select
Application.Goto Reference:="Yesterday_last_received"
ActiveSheet.Paste
this code takes the last ticket but based on it's availablity in next sheet "extract" i have to take one ticket previous to the last one (and on).
此代码获取最后一张票，但根据它在下一张“提取”中的可用性，我必须在最后一张（以及之后）之前拿一张票。
回答by Santosh
Try below code :
试试下面的代码：
Sub test()
Dim lastRow As Long
Dim rng As Range
Dim firstCell As String
lastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lastRow
Set rng = Sheets("sheet2").Range("A:A").Find(What:=Cells(i, 1), LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows)
If Not rng Is Nothing Then firstCell = rng.Address
Do While Not rng Is Nothing
rng.Offset(0, 1) = "found"
Set rng = Sheets("sheet2").Range("A:A").FindNext(rng)
If Not rng Is Nothing Then
If rng.Address = firstCell Then Exit Do
End If
Loop
Next
End Sub
