根据vba中单元格（find）中可用的值做while循环 [英] do while loop based on a value available in cell (find) in vba

问题描述

我正在vb中编写一个代码来检查工作表中的特定值，如果值不可用，那么应该返回到另一个工作表来获取新值来查找，如果找到该值，我必须做一些操作在该工作表我有以下代码来查找工作表中的值，但如果我通过相同的DO WHILE循环作为条件它给出一个编译错误

找到vaue代码

  Selection.Find（what：= last_received，After：= ActiveCell，LookIn：= _ 
 xlFormulas ，LookAt：= xlPart，SearchOrder：= xlByRows，SearchDirection：= _ 
 xlNext，MatchCase：= False，SearchFormat：= False）。激活
  

可能有一个请帮助我在循环条件中用上面的发现编写一个DO WHILE的代码，以便如果条件为false（i，如果没有找到该值，则为e在表格中）然后我应该使用一些其他的值来找到

这是我要放在循环中的代码

  Sheets（Ex选择
 Application.CutCopyMode = False 
 
 ActiveWorkbook.Worksheets（Extract -prev）。Sort.SortFields.Clear'使用票据排序
 ActiveWorkbook。工作表（Extract -prev）。Sort.SortFields.Add Key：= Range（_ 
C2：C2147），SortOn：= xlSortOnValues，Order：= xlAscending，DataOption：= _ 
 xlSortNormal 
使用ActiveWorkbook.Worksheets（Extract -prev）排序
 .SetRange范围（A1：AB2147）
 .Header = xlYes 
 .MatchCase = False 
.Orientation = xlTopToBottom 
 .SortMethod = xlPinYin 
。应用
结束
 
 Application.Goto参考：=R1C3'最后收到的票
 Selection.End（xlDown）。选择
 Selection.Copy 
表格（Calc）。选择
 Application.Goto参考：=Yesterday_last_received
 ActiveSheet.Paste 
  

此代码需要最后一张票，但基于它在下一张提取物中的可用性我必须在最后一张（和上）之前取一张票。

解决方案

请尝试下面代码：

  Sub test（）
 
 Dim lastRow As Long 
 Dim rng As Range 
 Dim firstCell As String 
 
 lastRow = Sheets（sheet1）。Range（A& Rows.Count）.End（xlUp）.Row 
 
对于i = 1 To lastRow 
设置rng = Sheets（sheet2）。Range（A：A）。Find什么：=单元格（i，1），LookIn：= xlValues，lookAt：= xlPart，SearchOrder：= xlByRows）
 
如果不是rng是没有，那么firstCell = rng.Address 
 
 Do While Not rng Not Not 
 
 rng.Offset（0，1）=found
 
设置rng =表（sheet2）。范围（A ：A）FindNext（rng）
 
 
如果不是rng不是然后
如果rng.Address = firstCell然后退出执行
结束如果
 
循环
下一个
 
 End Sub 
  

< img src =https://i.stack.imgur.com/fJ07a.pngalt =enter image description here>

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

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

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).

解决方案

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

这篇关于根据vba中单元格（find）中可用的值做while循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！