vba 如果一个单元格等于一个条件，则查找范围内的单元格将其值复制到另一列

Find cells within a range if one cell equals a criteria copy its value to another column

提问by hs0

I'm having real trouble trying to get this to work for me. To help explain what I need to do I've produced a (hopefully) very simple example below...

我真的很难让它对我来说有效。为了帮助解释我需要做什么，我在下面制作了一个（希望如此）非常简单的例子......

What I would like to do is:

我想做的是：

• Within a range of cells A:Afind J Bloggs(note there can be multiple entries and I need all of them)
• When an order from J Blogsis found copy his order date B1, request del. date C1and actual del. date D1
• Paste this information into a table G1:J4

• 在一系列单元格中A:A找到J Bloggs（注意可以有多个条目，我需要所有条目）
• 当J Blogs找到一个订单来自复制他的订单日期B1，请求 del。日期C1和实际德尔。日期D1
• 将此信息粘贴到表格中 G1:J4

NB: The list of customers can be long, and some customers may make seperate orders. I need to generate a list of all of these orders (don't need to check if date is in the past etc.).

注意：客户名单可能很长，有些客户可能会单独下单。我需要生成所有这些订单的列表（不需要检查日期是否过去等）。

Each time the query is run, say this time for H Simpson, only the details for H Simpsonwill appear in the table G1:J4

每次运行查询时，说这次 for H Simpson，只有详细信息H Simpson会出现在表中G1:J4

+---------------+----------------+---------------------+------------------+  
|   Customer    |   Order Date   | Requested Delivery  | Actual Delivery  |  
+---------------+----------------+---------------------+------------------+  
| J Bloggs      | 01/01/2013     |  02/01/2013         |  02/01/2013      |  
| H Simpson     | 05/01/2013     |  08/01/2013         |  09/01/2013      |  
| A Name        | 10/01/2013     |  10/01/2013         |  10/01/2013      |   
| J Bloggs      | 15/01/2013     |  22/01/2013         |  22/01/2013      |  
+---------------+----------------+---------------------+------------------+

采纳答案by pnuts

Your lucky day! I had a free spare minutes and wrote this code for you.

你的幸运日！我有空闲时间为你写了这段代码。

It will ask you for the Name- you simply select the cell with the name you wan't to generate data for

它会询问您Name- 您只需选择您不想为其生成数据的名称的单元格

It will create a tablein columns G:Jand stick in the results of matches in columns A:D

它将在列中创建一个表并在列中G:J保留匹配结果A:D

Sub Findining()

    Dim r As Range, i As Long, j As Long, rng As Range
    Range("G:J").ClearContents
    For i = 1 To 4
        Cells(1, i + 6) = Cells(1, i)
    Next i
    Set r = Application.InputBox("Select Name", Type:=8)
    If r.Columns.Count > 1 Or r.Rows.Count > 1 Then
      Do Until (r.Columns.Count = 1 And r.Rows.Count = 1)
        MsgBox "You can only select 1 name"
        Set r = Application.InputBox("Select Name", Type:=8)
      Loop
    End If

    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Set rng = Range("A" & i)
        If StrComp(r, rng, vbTextCompare) = 0 Then
            For j = 0 To 3
                Cells(Cells(Rows.Count, rng.Offset(0, 6 + j).Column).End(xlUp).Row + 1, rng.Offset(0, 6 + j).Column).Value = rng.Offset(0, j).Value
            Next j
        End If
        Set rng = Nothing
    Next i
    Columns.AutoFit
End Sub

before:

前：

after:

后：

回答by Gary's Student

There are at least three different approaches:

至少有三种不同的方法：

1. Use AutoFilter
2. Use a macro to extract the data
3. Use VLOOKUP()

1. 使用自动筛选
2. 使用宏提取数据
3. 使用 VLOOKUP()

Using VLOOKUP() to get more than one result is explained here:

此处解释了使用 VLOOKUP() 获得多个结果：

http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx

http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx

回答by pnuts

I go with Vasim every time, eg:

我每次都和 Vasim 一起去，例如：