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

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/25/2021  10:32:53 PM  EDT
Cancel = True
If Target.Address = "$AA$2223" Then Target.Offset(, 3).Select
End Sub

Double click On Range("AA2223") and then script will do what you wanted.

 

[Chaozfate]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/25/2021  10:32:53 PM  EDT
Cancel = True
If Target.Address = "$AA$2223" Then Target.Offset(, 3).Select
End Sub

Double click On Range("AA2223") and then script will do what you wanted.

Thanks for your help.

The code provide is able to solve my sample question. However, what I am looking for is a flexible code to jump between a known column within a row.

Lets say,
I am at row 100, I wish to jump to any column anytime (identified by my product code), but I will still be at row 100.

Apologies for the unclear question.

Thanks and regards,
CU

 

[My Aswer Is This]

I figured you may want more so provided what you asked for

You now said:
(identified by my product code
Not sure what that means.

I can write a script so if you enter any value in any cell the script will jump you to another cell.
Or if you double click on any row script will jump you to another cell on that row.
But there would need to be some sort of a trend.
Like always jump two cells to right or two cells to the left

Can you provide such a trend. I'm sure you understand trend like always jump same many cells each time

 

[My Aswer Is This]

I can say if you double click on any cell in any column script will jump you five cells to the right

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Modified  10/25/2021  11:08:44 PM  EDT
Target.Offset(, 5).Select
End Sub

 

[Peter_SSs]

I'm wondering if it is something more flexible like this?

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(1).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

 

[Chaozfate]

I can say if you double click on any cell in any column script will jump you five cells to the right

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Modified  10/25/2021  11:08:44 PM  EDT
Target.Offset(, 5).Select
End Sub

Thank You for your time to draft the code again.

My intention is to be able to determine the column i wish to jump to.

Ie:
1. if i am at column A, row 9999, i wish to jump to the column where the product code is at. lets say, the product code is AA80, and I wont be able to know AA80 is located at which column (due to too lengthy), so i wish that i could jump to the row 9999 which my product AA80 is at. (if my AA80 product code is at column BQ, i wish to jump from column A, row 9999, to column BQ, row 9999)
2. if i am at column A, row 9991, i wish to view my product code AA81 (if my product code AA81 is at column DT, i wish to jump from column A, row 9991, to column DT, row 9991)

Wonder if there is any code exists to solve my desire.

Thanks and regards,
CU

 

[My Aswer Is This]

I'm wondering if it is something more flexible like this?

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(1).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

I tried this but was not sure what to enter. I entered G5 script said G5 not found

 

[My Aswer Is This]

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.

 

[Peter_SSs]

I tried this but was not sure what to enter.

Example:

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

My row 1 consist of product code.

Lets say,
I am at row 100, I wish to jump to any column anytime (identified by my product code),