Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

match data between two sheets based on cell value

0

hi

 I try writing short macro without using loop to match data between two sheets , but not succeeded so far .

Private Sub CommandButton1_Click()
    Dim fnd As Range, sh1, sh2 As Worksheet
    Set sh1 = sheet1
    Set sh2 = sheet2
    Set fnd = sh1.Range("o:o").Find(sh2.Range("B3").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
       sh1.Range("E" & fnd.Row) = sh2.Range("H" & fnd.Row) + 1
       sh1.Range("P" & fnd.Row) = sh2.Range("I" & fnd.Row) + 1
       sh1.Range("J" & fnd.Row) = sh2.Range("J" & fnd.Row) + 1
       sh1.Range("F" & fnd.Row) = sh2.Range("K" & fnd.Row) + 1
    End If

End Sub

so  in sheet 2  I  write  the  name in B3   . it should  match  with  column  O   then should copy  data   from sheet 1  to  sheet 2 ( E=H, P=I, J=J, F=K) . so  I  put  the  result  in  sheet 2  when  write  OMER  in B3  then  should  match  with  the sheet1  and  bring  tha  data 

I  hope  this  clear

excel
MS Office Version: 2019
posted by: Hasson (rep: 30) on Wed Jul 21, 2021 at 1:45 pm
Answer
Discuss

Answers

0
Selected Answer

Hasson

Please try the modified code below, noting that when you write an equation in VBA whatever is on the LEFT becomes the value on the RIGHT (which may be counter-intuitive in many Eastern countries).

I could not see your CommandButton1 but the code can be run manually and will return more than one result (for which you do need to have a loop for find subsequent rows in sh1). Changes are in bold:

Private Sub CommandButton1_Click()
    Dim fnd As Range, sh1, sh2 As Worksheet
    Set sh1 = sheet1
    Set sh2 = sheet2

    With sh1.Range("o:o")
    Set fnd = .Find(sh2.Range("B3").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        sh2.Range("H6:K15").Value = ""
        firstAdd = fnd.Address
        n = 6
       Do
       sh2.Range("H" & n) = sh1.Range("E" & fnd.Row)
       sh2.Range("I" & n) = sh1.Range("P" & fnd.Row)
       sh2.Range("J" & n) = sh1.Range("J" & fnd.Row)
       sh2.Range("K" & n) = sh1.Range("F" & fnd.Row)
       n = n + 1
       Set fnd = .FindNext(fnd)
       Loop While Not fnd Is Nothing And fnd.Address <> firstAdd
    End If
   End With
End Sub
posted by: John_Ru (rep: 6142) on Wed Jul 21, 2021 at 6:06 pm
Discuss

Discussion

John
 I  thought   to  do  that  without  any  loop . you  know  more  than  me 
thanks   for  your  answering 
Hasson (rep: 30) Jul 21, '21 at 10:51 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login