View Full Version : jump from a cell to another
fadib
12-01-2008, 09:44 PM
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.
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
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
fadib
12-02-2008, 09:07 PM
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.
In place of:
Sheet1.Columns("A:J").ClearContents
You could try substituting:
With Sheet1
.Range(.Cells(1, 1), _
.Cells(.Range("A1").End(xlDown).Row, _
.Range("A1").End(xlDown).End(xlToRight).Column)).ClearContents
End With
fadib
12-03-2008, 10:41 PM
Awesome, thanks Mark for all your help.
You bet fadib :-)
Please remember to mark the thread Solved.
Mark
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.