查找并设置为变量，如果 [英] find and set as variable and if

问题描述

我一直坚持使用查找并将其设置为变量。我无法获得所需的结果。在第一张纸上，我有一列Test的值是x或（x）。如果值为x，则需要从EN列复制该值。如果值是（x），则不要复制。

I'm stucked with using Find and set as variable. I cannot get the result I need. In first sheet I have a column Test with values x or (x). If the value is x I need to copy the value from column EN. If the value is (x) do not copy.

无论x或（x），代码都会从 EN列中复制值。

the code copies values from column "EN" no matter x or (x)

在使用Set stfound时我可能有一个错误

I must have probably an error in using Set stfound

Dim ENcolumn
Dim xcolumn
Dim secrow
Dim lastrow
Dim totrow

Worksheets("List1").Activate
Worksheets("List1").Range("A1:C1").Find(What:="EN", MatchCase:=True, 
lookAT:=xlWhole).Activate
ENcolumn = ActiveCell.Column 'find and create variable

Worksheets("List1").Range("A1:C1").Find(What:="test", MatchCase:=True, 
lookAT:=xlWhole).Activate
xcolumn = ActiveCell.Column 'find and create variable

currow = ActiveCell.Row + 1 ''make one low rower than current row (first 
value)
lastrow = Worksheets("List1").Cells(Rows.Count, xcolumn).End(xlUp).Row
For totrow = currow To lastrow

Set stfound = Cells.Find(What:="x", After:=Cells(totrow, xcolumn), 
MatchCase:=True, lookAT:=xlWhole)

If Not stfound Is Nothing Then 'if value is found then do this

Worksheets("List1").Cells(totrow, ENcolumn).Copy 'copy values
Worksheets("List2").Activate
b = Worksheets("list2").Cells(Rows.Count, ENcolumn).End(xlUp).Row
Worksheets("list2").Cells(b + 1, 2).Select 'select first empty cell in 
second column
ActiveSheet.Paste

ActiveCell.Offset(0, 1).Value = "receivercode"
ActiveCell.Offset(0, 2).Value = "01.01.2019"
Worksheets("list1").Activate

End If
Next

Application.CutCopyMode = False 'stop if false
ThisWorkbook.Worksheets("List1").Cells(1, 1).Select
MsgBox ("done")`

现在，我将 EN列中的所有值复制到sheet2到column2。

Now I get all the values from column "EN" copied to sheet2 to column2.

I仅需要EN列中在第1列中具有x值的那些值即可。

I need only those values in column EN that has x value in column 1

推荐答案

您需要重复搜索直到找到全部。请注意，搜索是循环的，因此您需要记住第一个匹配项。 （好消息是它可以是任何单元格，因此您无需从第一个单元格开始搜索。）这是一个框架，用于在整个工作表中搜索特定值：

You need to repeat search until you find all. Note that search is circular so you need to remember the first match. (The good news is that it can be any cell so you do not need to start searching from the very first cell.) Here is a frame for searching the entire sheet for a specific value:

Dim s1st As String
Dim rFnd as Range

Set rFnd = Nothing
With ActiveSheet.UsedRange
     Set rFnd = .Cells.Find(What:="x", LookIn:=xlValues, lookat:=xlWhole, _ 
                             SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=True)
     If Not rFnd Is Nothing Then
       s1st = rFnd.Address
       Do
                ' do here what you need to do with your found cell. 
                ' rFnd points to the found cell with the value "x"
                ' e.g. 
           rFnd.Copy    ' single cell
           b = Worksheets("list2").Cells(Rows.Count, ENcolumn).End(xlUp).Row
           Worksheets("list2").Paste Destination:=Worksheets("list2").Cells(b + 1, 2)
           Set rFnd = .FindNext(rFnd)
       Loop While Not rFnd Is Nothing And rFnd.Address <> s1st
    End If
End With

NB：您可以跟踪实际的目标单元，而不是在每个循环中都找到最后一个单元。因此，您在初始化阶段就找到了第一个目标单元（ .End（...）... ），然后只需在循环内递增行计数器。尽管您会注意到速度仅在数千行上有所提高。

NB: you may keep track of the actual destination cell instead of finding the last one in every loop. So you find the first destination cell (.End(...)...) once in the initialisation phase and then simply increment the row counter within the loop. Though you will notice any increase in speed only over thousands of rows.

这篇关于查找并设置为变量，如果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！