Go to cell by matching value in a column

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
Is there a code that will allow me to use a button to go to a cell by matching the value of a list of values cell with the values in column D. When I choose option “Apple” from the drop down and click the button, the code searches column D and locates the cell with “Apple” and goes to that cell.

The code that I have will no longer work.

Thanks
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,564
The code that I have will no longer work.
What code do you have?
Did it work at one time? What happened to change that? Did you get a new version of Excel or ....?
What are the details of "doesn't work"? Does it do nothing or go to the wrong cell or ....?
 
Last edited:

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
The problem with my code was only that I had to manually add each new “go to cell”. Every time I added a new value to the drop down menu, I would have to add it to the vba and specify where to go. That quote really has nothing to do with what I’m asking. I should not have mentioned it as I can see it was confusing
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
888
Office Version
2010
Platform
Windows
With no idea of what you're working with I'd guess you'll want to use the Range.Find method.
You could use event code to trigger the macro right away, why do you need a button for it ?
 

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
My spreadsheet is very large so a user will be able to use the data validation dropdown list of values to choose a value then click the button next to it in order to go directly to that spot on the spreadsheet. Column D has the matching values to the list of values. So the logic for the macro would probably be something like this; the value in cell B2 says “Apple”, so search in column D until I find “Apple”, once the matching cell (Apple) is found in column D, GoTo this cell
 
Last edited:

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
888
Office Version
2010
Platform
Windows
Assuming everything is on the same sheet
maybe something along the lines of this
Code:
Sub FromSomeCommandButton()
    Dim srchString As String
    Dim fndRng As Range
If Range("B2").Value = vbNullString Then Exit Sub
srchString = Range("B2").Value
Set fndRng = ActiveSheet.Range("D:D").Find(What:=srchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not fndRng Is Nothing Then
    Application.Goto fndRng
Else
    MsgBox srchString & "  was not found"
End If
End Sub
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
888
Office Version
2010
Platform
Windows
You're welcome, and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,697
Messages
5,470,224
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top