VBA - Search and go to the cells

Calvin1128

New Member
Joined
Mar 27, 2022
Messages
8
Office Version
  1. 2021
Platform
  1. Windows
Hi!

I am seeking help on VBA for locating and going to the cell.
I wish if I can type something in a cell under tab1, I can locate and go to the same cell in another tab (Only 1 cell would be the same within the whole file) after clicking a button
It is literally like control + F...
I have created a test file for detailed reference.

Like If I type "ABC1234" in "Sheet1", I would like to go to cell A4 in "TAB2" as they are the same.
Is it possible to make it? I have seen similar threads and posts, but I am a newcomer in VBA and I cannot change it for my use..
Hope anyone can help and let me learn from this task🙏
Feel free to ask me to supplement any details! Thanks a lot!

1668396622717.png
1668396553601.png
1668396678828.png
 

Attachments

  • 1668396572923.png
    1668396572923.png
    9.2 KB · Views: 2

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I'm assuming your sheet name with the value in Range("A1")
is named "Sheet1" and that this sheet is the first sheet in your workbook.
Then try this:
VBA Code:
Sub Search_Me()
'Modified  11/14/2022  3:01:02 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim s As Long
s = 0
Dim r As Range
Dim ans As String
ans = Sheets("Sheet1").Range("A1").Value

For i = 2 To Sheets.Count
    For Each r In Sheets(i).UsedRange
        If r.Value = ans Then
            s = s + 1
            Application.Goto Sheets(i).Range(r.Address)
            Exit Sub
            End If
        Next
Next
If s < 1 Then
MsgBox ans & "  Not Found"
End If
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you!! Omg It seems the thing I am finding for!!
May I ask more for clarifications:
  • Can I start the macro at the choosing cell instead of A1?
  • Is "i" equal to the total sheet number except the first sheet? or does it have another meaning?
 
Upvote 0
Thank you!! Omg It seems the thing I am finding for!!
May I ask more for clarifications:
  • Can I start the macro at the choosing cell instead of A1?
  • Is "i" equal to the total sheet number except the first sheet? or does it have another meaning?
i is used to search all sheets except sheet 1
Because you already have the search value in Range("A1") of a sheet(1)
The script would find that value and stop there.

Not sure what this means:
  • Can I start the macro at the choosing cell instead of A1?
What does choosing cell mean.
In your original post you showed A1
The macro searches all ranges in each sheet of whats known as the used range meaning any cell that has or had values
So why are you happy with the script I provided but now wanting to change things?
I could have a input box popup asking for the search value.
Would you want that?
 
Upvote 0
Here try this:
A Inputbox will Popup wanting you to enter the search for value:
VBA Code:
Sub Search_Me()
'Modified  11/14/2022  4:13:26 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim s As Long
s = 0
Dim r As Range
Dim ans As String
ans = InputBox("Enter search value")

For i = 1 To Sheets.Count
    For Each r In Sheets(i).UsedRange
        If r.Value = ans Then
            s = s + 1
            Application.Goto Sheets(i).Range(r.Address)
            Exit Sub
            End If
        Next
Next
If s < 1 Then
MsgBox ans & "  Not Found"
End If
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you for teaching about "i". 🙏
I would like to apologize for the change as I forgot the cell will change after filling in record one by one.

Thank you for all your solutions! The second solution perfectly fits my needs 🙏 Thank you very much!
 
Upvote 0
Thank you for teaching about "i". 🙏
I would like to apologize for the change as I forgot the cell will change after filling in record one by one.

Thank you for all your solutions! The second solution perfectly fits my needs 🙏 Thank you very much!
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top