jump from a cell to another

[fadib]

Hi guys,
I want your help to add couple things to the excel.
After the selection in the combobox, I want to do couple things.
it is hard to explain, I think I am gonna give an example.
1111 is selected in combobox, sheet2 get activated,
locate 1111, highlight all the data that belongs to 1111
in this case
11 12 123
2 22 223
-7 32 323
-16 42 423

and copy them in sheet1 cell A1

I started working on it, I got some help, I have attached where I am so far.

[GTO]

Greetings fadib,

Seems to be a little different than what we did before, but similar. Is this more what you were trying to accomplish?

Hope this helps,

Mark

[vba]Private Sub cboValLookFor_Change()
Dim _
rngLookIn As Range, _
rngBottomRight As Range, _
rngToCopy As Range

'// Use the same Find we previously did. //
Set rngLookIn = Sheet2.Range("B:B").Find(What:=cboValLookFor.Value, _
After:=Sheet2.Cells(1, 2), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows)

'// Ensure that we found something and that the combo box wasn't empty. //
If Not rngLookIn Is Nothing _
And Not cboValLookFor.Value = "" Then

'// Since we now may have more or less rows/cols from one search to the next, //
'// clear the entire columns. Change as needed. //
Sheet1.Columns("A:J").ClearContents

'// From where we found the val in the combo box, go down and right, so we'll //
'// know where the records end. NOT FAULTLESS, as if there are empty cells in //
'// the last row of records... //
Set rngBottomRight = rngLookIn.End(xlDown).End(xlToRight)

'// Copy from one row below where we found the val we were looking for (similar //
'// to our previous Offset), to the bottom right corner we figured above... //
With Sheet2
.Range(.Cells(rngLookIn.Row + 1, rngLookIn.Column), _
.Cells(rngBottomRight.Row, rngBottomRight.Column)) _
.Copy Sheet1.Cells(1, 1)
End With

Else

Sheet1.Columns("A:J").ClearContents
End If
End Sub[/vba]

[fadib]

Greetings Mark,
Thank you so much for your help.
This is exactly what I am looking for. Superbe!!!
One more Thing, how can the code be modified so that instead of clearing the content of A:J it clears only what was added.

[GTO]

In place of:

[vba] Sheet1.Columns("A:J").ClearContents[/vba]

You could try substituting:

[vba] With Sheet1
.Range(.Cells(1, 1), _
.Cells(.Range("A1").End(xlDown).Row, _
.Range("A1").End(xlDown).End(xlToRight).Column)).ClearContents
End With[/vba]

[fadib]

Awesome, thanks Mark for all your help.

[GTO]

You bet fadib :-)

Please remember to mark the thread Solved.

Mark