CODEWITHSUNDEEP
excelvba
excel34
excel34

Reputation: 799

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

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:

Upvotes: 0

Views: 7635

Answers (3)

Bob Phillips
Bob Phillips

Reputation: 437

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

Upvotes: 1

excel34
excel34

Reputation: 469

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)

Upvotes: 0

Jeff Storey
Jeff Storey

Reputation: 57212

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

Upvotes: 0

Related Questions