你能在 VBA excel 中用宏找到特定值的行号吗?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1983592/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:10:04  来源:igfitidea点击:

Can you find the row number for a particular value with macro in VBA excel?

excel-vbavbaexcel

提问by excel34

I'm trying to find a command which returns the index of the row for which a particular value occurs. In addition, the value cannot be empty.

我试图找到一个命令,该命令返回出现特定值的行的索引。此外,该值不能为空。

In total, there are 3 worksheets. Worksheet A has a column with all the values. Worksheet B has a column with values which appear in worksheet A and columns with more information for that value and I want to copy that information into worksheet C.

总共有 3 个工作表。工作表 A 有一列包含所有值。工作表 B 有一列包含出现在工作表 A 中的值和包含该值更多信息的列,我想将该信息复制到工作表 C 中。

Say worksheet A is (lines represent empty cells):

说工作表 A 是(线条代表空单元格):

a
b
c

Worksheet B looks like this before I run the macro:

在我运行宏之前,工作表 B 看起来像这样:

a  12  32  
c  34  45
b  23  21

Worksheet C looks like this before I run the macro:

在我运行宏之前,工作表 C 看起来像这样:

a
b
c

and like this after I run the macro:

在我运行宏之后就像这样:

a  12  32  
b  23  21
c  34  45

The structure of the macro looks like this:

宏的结构如下所示:

  • If the value in worksheet A is not empty, look for a row that corresponds to the value in worksheet "B".
  • Copy the information in the row from worksheet B.
  • Paste it into row i in worksheet "C".
  • 如果工作表 A 中的值不为空,请查找与工作表“B”中的值对应的行。
  • 复制工作表 B 行中的信息。
  • 将其粘贴到工作表“C”中的第 i 行。

回答by Bob Phillips

Try this

尝试这个

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim RowNum As Long

    With Worksheets("C")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRow

            If Not IsError(Application.Match(.Cells(i, "A"), Worksheets("A").Columns(1), 0)) Then

                RowNum = Application.Match(.Cells(i, "A"), Worksheets("B").Columns(1), 0)
                Worksheets("B").Cells(RowNum, "B").Resize(, 2).Copy .Cells(i, "B")
            End If
        Next i
    End With

End Sub

回答by Jeff Storey

Have you looked at Excel's VLookup function? You can use that to find a value in a sheet and then get another value from that row. each of your cells in C could use a VLookup to get the correct value from B (or blank if it does not exist).

你看过Excel的VLookup函数吗?您可以使用它在工作表中查找值,然后从该行获取另一个值。C 中的每个单元格都可以使用 VLookup 从 B 中获取正确的值(如果不存在则为空白)。

回答by excel34

yea , I allready tried it with VLOOKUP , but I only managed to the find a value from an array not the index of the row.

是的,我已经用 VLOOKUP 试过了,但我只能从数组中找到一个值,而不是行的索引。

ie =VLOOKUP(1,A2:C10,2)

即 =VLOOKUP(1,A2:C10,2)