无法找到如何编码：如果单元格值等于范围中的任何值 [英] Unable to find how to code: If Cell Value Equals Any of the Values in a Range

问题描述

我经过大量的研究后发现了下面的代码，它做了一些我想要它做的事，除了我不知道如何指定标准来引用一系列的单元格，而不是只有一个单一的标准。

I have found the following code after a lot of research and it does a little of what I want it to do except I don't know how to specify the criteria to reference a range of cells instead of just one single criteria.

我也试图复制记录并将它们附加到Sheet1中匹配记录的行的末尾。此代码仅将记录复制到Sheet3，因此它们不像我想要的那样粘贴到Sheet1中的相应行。

I am also trying to copy the records and append them to the end of the rows of the matching records in Sheet1. This code only copies the records to Sheet3 so they aren't pasted with their corresponding rows in Sheet1 like I want.

Sub copytosheet()

 Dim sRng As Range, cell As Range
 Dim dRng As Range
 Set sRng = Sheets("Sheet2").Range([A2], [A65536].End(xlUp))
 For Each cell In sRng
 If cell.Value = "80560" Then
 Set dRng = Sheets("Sheet3").[A65536].End(xlUp)(2, 1)
 cell.EntireRow.Copy dRng
 End If
 Next
 End Sub

因此，Sheet2中有10,000多条记录，Sheet1中有30多条记录。

Sheet2和Sheet1在A列中有一个ID号。

Sheet1中的所有记录将在Sheet2中具有匹配的记录。

我要从Sheet2复制记录，并将它们附加到记录的结尾，并在Sheet1中具有相同的ID。

上面的代码不能解决我的问题，因为它只找到一个记录80560，并将其复制到表3.

非常感谢您提供任何帮助，您可以提供： ）

So, there are 10,000+ records in Sheet2, and 30+ records in Sheet1.
Sheet2 and Sheet1 have an ID number in Column A.
All of the records in Sheet1 will have a matching record in Sheet2.
I want to copy the records from Sheet2 and append them at the end of the record with the same ID in Sheet1.
The code above doesn't solve my problem because it only finds the one record "80560" and copies it to sheet 3.
Thank you so much in advance for any assistance you can offer :)

-Lindsay

推荐答案

位的编程，得到这个工作在一组值，而不只是'80560'。这将需要在两个阶段完成。

You'll need to do a bit of programming to get this to work on a set of values and not just '80560'. It will be need to be done in 2 stages.

创建一个包含字符串的数组，它们可以从电子表格上的一个位置获取。然后需要一个函数来验证列表中是否存在字符串：

Make an array that holds your strings, they may be fetched from a location on the spreadsheet. Then a function needs to be made which verifies whether a string exists in the list or not:

Dim DictionaryArray() as String
Redim DictionaryArray(1 to 1000)
' Fill it with the stuff you need to check against
' e.g. DictionaryArray(1) = '80536', etc.
' Do note this is HIGHLY INEFFICIENT, you would need to use a nice binary search algo to make it fast, after sorting it internally

' Now build a function to check if a given string is in this dictionary
Function CheckIfFound(StringToCheck as string, DictionaryArray() as string) as Boolean
    'Implement some search function here
End Function

最后，在您发布的代码中，将验证步骤替换为

And finally in your code that you posted, replace the verification step with

if CheckIfFound(cell.Value, DictionaryArray) = True then
' ---- Implement rest of your code

---

编辑：
关于如下所述的问题，这可以这样做：

---

With regard to the problem as described below, something like this can be done:

Sub CopyFrom2TO1()
    Dim Source as Range, Destination as Range
    Dim i as long, j as long

    Set Source = Worksheets("Sheet1").Range("A1")
    Set Dest = Worksheets("Sheet2").Range("A2")

    for i = 1 to 100

        for j = 1 to 100
            if Dest.Cells(j,1) = Source.Cells(i,1) then
                    Source.Range("A" & j).Range("A1:Z1").Copy ' A1:Z1 relative to A5 for e.g.
                    Dest.Range("A"&i).Paste
                    Exit For
            end if
        next j
    next i
End Sub

同样，这是非常低效的，具有O（n ^ 2）复杂性，但会工作。

Again, this is highly inefficient, with O(n^2) complexity, but will work.

这篇关于无法找到如何编码：如果单元格值等于范围中的任何值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！