Search specific Row for value that matches certain Cell

[Hyakkivn]

Hi everyone. I'm the newest member of the forum thought.
I'd like to ask for a help with my simple problem.(Yet I'm noob at VBA)
For example, I have 2 sheets: Sheet1 and Sheet2.
In Sheet1, I have Cell A1 with value = 10.
In Sheet2, I have a ROW (for ex: ROW 2) with value from 1 to 20.
Question: Can anyone help me write the code that Search for Cell in sheet 2 of ROW 2 that matches value of Cell A1 from Sheet1, then select it.
Thanks!

 

[Hyakkivn]

Question: Can anyone help me write the code that Search for Cell of ROW 2 in Sheet2 that matches value of Cell A1 from Sheet1, then select it.
Thanks!
Sorry for wrong typing !

 

[Hyakkivn]

Up ! :D

 

[Hyakkivn]

Up! 2

 

[Hyakkivn]

I've found the solution.

Hi everyone. I'm the newest member of the forum thought.
I'd like to ask for a help with my simple problem.(Yet I'm noob at VBA)
For example, I have 2 sheets: Sheet1 and Sheet2.
In Sheet1, I have Cell A1 with value = 10.
In Sheet2, I have a ROW (for ex: ROW 2) with value from 1 to 20.
Question: Can anyone help me write the code that Search for Cell in sheet 2 of ROW 2 that matches value of Cell A1 from Sheet1, then select it.
Thanks!

I found the solution. Post it here in case anyone need it. Thanks everyone for reading the topic

Sub FindandSelectCell()
Dim a As Range
Set a = Sheets("Sheet1").Range("A1")
Rows("2:2").Select
Selection.Find(What:=a, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

 

[antman2988]

Hi Hyakkivn,

Personally, I wouldn't use .Select as it's bad practice to use it. You can try my code below as it will accomplish the same feat. It's also good to add error handlers in case something goes wrong. What are you using the selected cell for?

Sub FindAndSelectCell()
    Dim a As Range
    Dim row As Range

    On Error Resume Next
    Set a = Sheets("Sheet1").Range("$A$1")
    Set row = Rows(2).Cells
    On Error GoTo 0

    If Not a Is Nothing Then
        row.Find(What:=a, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
    Else
        MsgBox "Please enter a value in cell A1"
    End If
End Sub

 

[Hyakkivn]

Hi Hyakkivn,

Personally, I wouldn't use .Select as it's bad practice to use it. You can try my code below as it will accomplish the same feat. It's also good to add error handlers in case something goes wrong. What are you using the selected cell for?

Sub FindAndSelectCell()
    Dim a As Range
    Dim row As Range

    On Error Resume Next
    Set a = Sheets("Sheet1").Range("$A$1")
    Set row = Rows(2).Cells
    On Error GoTo 0

    If Not a Is Nothing Then
        row.Find(What:=a, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
    Else
        MsgBox "Please enter a value in cell A1"
    End If
End Sub

Thanks for your code. I really appreciate it.
I use Select cell for pasting a copied data from another sheet.
This is just a step in my whole macro that is used to calculate a lot of things relate to my work. (I'm working for a Logistic company relate to Coal and mineral)