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

Question

Hi,

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.

Say worksheet A is (lines represent empty cells):

a
b
c

Worksheet B looks like this before I run the macro:

a  12  32  
c  34  45
b  23  21

Worksheet C looks like this before I run the macro:

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

Answer 1

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

Answer 2

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.

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

Answer 3

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