Find and copy

[fadib]

Hi Guys,
I have two sheets, S1 and S2.
on S1 I have a my Data. on S2 I have a combobox.
I want after doing a selection in combobox to go to S1 and find its address and select the cell, copy the row and paste it in A1 in S2.
I tried to do do that by recording a macro, but it is not working properly.

Please I need your help.

[fadib]

Sorry,
This is the excel I am practicing on.
So when I have a selection on combobox for example 1111, The code should go to sheet1, find the location of 1111, and copy only the two lines after 1111 into A1 of sheet2.

[GTO]

Greetings fadib,

Not too sure what you meant by "copy only the two lines...", but presuming you want the rows, here is one way:

[vba]Private Sub cboValLookFor_Change()
Dim rngLookIn As Range
'// Look in Col B to find "1111", "2222", or whatever value is selected in //
'// the combo box. PLEASE NOTE: I changed the name of the combo box from //
'// the default assigned name, like, "ComboBox1", to "cboValToLookFor". You //
'// need to do the same for the code to run, or change the below to match //
'// object name of combo box. //

Set rngLookIn = Sheet2.Range("B:B").Find(What:=cboValLookFor.Value, _
After:=Sheet2.Cells(1, 2), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows)

If Not rngLookIn Is Nothing _
And Not cboValLookFor.Value = "" Then

Sheet2.Rows(rngLookIn.Row).Offset(1).EntireRow.Resize(2).Copy Sheet1.Rows("1:2")
Else

Sheet1.Rows("1:2").ClearContents

End If
End Sub[/vba]

To use the above, right-click "Sheet2" tab, select View Code, and paste.

Please note that as I used the sheets' object or codenames, it "looks" backwards. This is because somehow you changed (codename) Sheet1's name on the tab to "Sheet2" and visa-versa.

Hope this helps,

Mark

Happy Thanksgiving!

[fadib]

GTO, awesome it worked. Thanks
Yes you are right what if I have more then 2 lines in one case and 3 lines or more in others.
how would you do that?

on the other hand can you help me understand the code.
like what does "set" mean?
how would you save the address of the cell in which your data rezides.
like for example 1111 address is B2.

Thank for your help.

[fadib]

I have been looking at couple ways to store the data.
I will always have a blank row before 2222, 3333, 4444 .... etc.
I know how to select the rows using ctrl shift and arrows (I recorded a macro for that)
What I still have to figure out is how to select the right cell.
example, if 1111 is selected in combobox, The search function will select B2 (In this case), I want to shift down one cell to B3.