CODEWITHSUNDEEP
excelvba
Benette
Benette

Reputation: 11

Search for a value in a column and use the row number obtained to copy a cell

I have been trying to search for a value in a column and using the row number obtained I want copy a cell. Here's my code.

If Trim(FindString) <> "" Then
    With Sheets("Details").Range("A:A") 'searches all of column A
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        x = Rng.Row
        If Not Rng Is Nothing Then
            Sheets("Details").Activate
            Sheets("Details").Range("Hx").Copy
            Sheets("Report").Activate
            .Range("A6").PasteSpecial xlPasteFormats
        Else
            MsgBox "Unique ID Doesnot Exist! Please Check Again." 'value not found
        End If
    End With
End If

How to get the value of x and refer to the cell in column H with the row number obtained from the search?

Thanks

Upvotes: 0

Views: 51

Answers (1)

BigBen
BigBen

Reputation: 49998

To close the question: variables don't belong inside quotes. Concatenate with &:

Sheets("Details").Range("H" & x).Copy

Also, you need to move the x = Rng.Row after the If Not Rng Is Nothing Then.

Upvotes: 1

Related Questions