Excel Row/Column Jumping

[Chaozfate]

Dear All,

I have this excel whereby the rows and columns have quite a length of data.

Example:

	column AA	column AB	column AC	column AD
Row 1	AA50	AA51	AA52	AA53
Row 2223

My row 1 consist of product code.

I wonder if there is any macro or a method to fullfill my request below:

Lets say I am currently at Column AA Row 2223, I wish to jump to the column AD but maintain at Row 2223.

How I did it, is highlight the whole Row 1, and CTRL F the product code, but this will trigger excel to jump straight back to Row 1 due to my search result, this is very inconvenient cause I need to scroll all the way back to Row 2223.

Hope there is anyone out there would be able to provide advise/help to my little inconvenience.

Thanks and regards,
CU

 

[Chaozfate]

You said:
the column where the product code is at.

So we will need to know what your product code is:
So we can have a script popup a Inputbox.
You enter the Product code we are searching for and take you to that cell.

But can you say give a general area where we will find this product code

Like maybe column AA to GG.
Other wise we much search all cells on the sheet.

Yes.

My product code will only fixed at row 5.

My product code format will be in ascending format, ie: AA61, AA62, AA63, AA64, AA65, AA65-1, AA65-2, AA65-3, AA65-4, AA70, AA71, etc

Thanks and regards,
CU

 

[Peter_SSs]

My product code will only fixed at row 5.

That contradicts post 1

My row 1 consist of product code.

Try making this change in my earlier code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim sCode As Variant
  Dim rFound As Range
  
  sCode = Application.InputBox("Enter code to go to")
  If TypeName(sCode) <> "Boolean" Then
    Cancel = True
    Set rFound = Rows(5).Find(What:=sCode, LookAt:=xlWhole, MatchCase:=False)
    If rFound Is Nothing Then
      MsgBox sCode & " not found"
    Else
      Cells(Target.Row, rFound.Column).Select
    End If
  End If
End Sub

 

[My Aswer Is This]

I'm going to move on. I'm sure Peter will have a answer for you.

 

[Chaozfate]

I'm going to move on. I'm sure Peter will have a answer for you.

Thank you again for your time spent. Appreciated.

 

[Chaozfate]

That contradicts post 1

Try making this change in my earlier code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim sCode As Variant
  Dim rFound As Range
 
  sCode = Application.InputBox("Enter code to go to")
  If TypeName(sCode) <> "Boolean" Then
    Cancel = True
    Set rFound = Rows(5).Find(What:=sCode, LookAt:=xlWhole, MatchCase:=False)
    If rFound Is Nothing Then
      MsgBox sCode & " not found"
    Else
      Cells(Target.Row, rFound.Column).Select
    End If
  End If
End Sub

My apologies Peter, I inserted the code in the excel but not sure how to activate/prompt/trigger the search. Could you guide me further?

Thanks and regards,
CU

 

[Chaozfate]

That contradicts post 1

Try making this change in my earlier code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim sCode As Variant
  Dim rFound As Range
 
  sCode = Application.InputBox("Enter code to go to")
  If TypeName(sCode) <> "Boolean" Then
    Cancel = True
    Set rFound = Rows(5).Find(What:=sCode, LookAt:=xlWhole, MatchCase:=False)
    If rFound Is Nothing Then
      MsgBox sCode & " not found"
    Else
      Cells(Target.Row, rFound.Column).Select
    End If
  End If
End Sub

Ignore my previous reply, it works after i tried double click the current cell. It works like a charm and solve my problem, thank you so much Peter

 

[Peter_SSs]

My apologies Peter, I inserted the code in the excel but not sure how to activate/prompt/trigger the search. Could you guide me further?

Sorry, MAIT had given some instructions in post #2 & you appeared to be testing that code so I thought further instruction was not needed.

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by double-clicking any cell
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

 

[Peter_SSs]

double click the current cell.

As noted in my last post (posted at the same time as you) that any cell will do for the double-click.

Glad you got it working and thanks for the follow-up.

 

[Chaozfate]

As noted in my last post (posted at the same time as you) that any cell will do for the double-click.

Glad you got it working and thanks for the follow-up.

Ah yea, i got spoon-fed with the code and did not read the code properly. Your code has stated but I did not read it earlier.

Thanks again Peter. Very much appreciated for your time spent.

 

[My Aswer Is This]

Thank you again for your time spent. Appreciated.

Glad I was able to help with your original posting. But when there are a lot of other questions to help out with I move on when more then one person is helping with same posting.