Go to other cell based on matching values using button control

menikhali · Dec 22nd 2012

[TABLE="width: 448"]

[tr]

[td][/td]

[td]

a

[/td]

[td]

b

[/td]

[td][/td]

[td]

c

[/td]

[td]

d

[/td]

[td]

e

[/td]

[/tr]

[tr]

[td]

1

[/td]

[TD="align: left"]go button
[/TD]

[td][/td]

[td]

name list

[/td]

[td][/td]

[td]

date list

[/td]

[td][/td]

[/tr]

[tr]

[td]

2

[/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

name

[/td]

[td][/td]

[td]

date

[/td]

[TD="align: right"]01/01/12[/TD]
[TD="align: right"] 02/01/12[/TD]
[TD="align: right"] 03/01/12[/TD]

[/tr]

[tr]

[td]

4

[/td]

[td]

apple

[/td]

[td][/td]

[/tr]

[tr]

[td]

5

[/td]

[td]

orange

[/td]

[td][/td]

[/tr]

[tr]

[td]

6

[/td]

[td]

mango

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]
hi there, I am bit stacked getting formula for going to specific cell. I have list of name and date + go to button;
I want to match name and date from cell and go to particular cell. eg. from list if i select orange and 3/1/12, it will go to cell e5;
much appreciable if any genius could help me out.
thanks
Moderator Edit: email address deleted

menikhali · Dec 22nd 2012

Re: Go to other cell based on matching values using button control

hi there, I am bit stacked getting formula for going to specific cell. I have list of name and date + go to button;
I want to match name and date from given cells value and cursor will go to particular cells . eg. from list if i select orange and 3/1/12, and hit "go to" button, cursor will go to cell e5;
much appreciable if any genius could help me out.
thanks

turbonate · Dec 22nd 2012

Re: Go to other cell based on matching values using button control

Using the two find Methods listed on Ozgrid you can add this code to the button sub.

Find method reference: http://www.ozgrid.com/VBA/find-method.htm
Find Date reference: http://www.ozgrid.com/VBA/find-dates.htm

Code

Public Sub highlightCell()    
    Dim strName As String
    Dim strDate As Date
    Dim rName As Range
    Dim rDate As Range
    
    'get parameters
    strName = Range("B1").Value
    strDate = Range("D1").Value
    
    'format date
    strDate = Format(strDate, "Short Date")
    
    On Error Resume Next
    
    'find strName location in column 1 and set to rName range
    Set rName = ActiveSheet.Columns(1).Find(What:=strName, After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    
    'quit if not found
    If rName Is Nothing Then
        MsgBox "Can't find the selected Name"
        Exit Sub
    End If
    
    'find dtDate location in row 3 and set to rDate range
    Set rDate = Rows(3).Find(What:=CDate(strDate), After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    
    'quit if not found
    If rDate Is Nothing Then
        MsgBox "Can't find the selected Date"
        Exit Sub
    End If
    
    On Error GoTo 0
    
    'select the cell which corresponds to both Name and Date
    Cells(rName.Row, rDate.Column).Select
End Sub

Display More

Limitations: this code assumes that the sheet which you are editing will always be active while it is executing. We'd have to add more references to update it.

cheers!
Nate

AAE · Dec 22nd 2012

Re: Go to other cell based on matching values using button control

To navigate to, rather than select, the cell, change

Code

Cells(rName.Row, rDate.Column).Select

To

Code

Application.Goto reference:=Cells(rName.Row, rDate.Column), scroll:=True

menikhali · Dec 22nd 2012

Re: Go to other cell based on matching values using button control

Thanks Turbonate
I will apply it.
thanks again

AAE · Dec 22nd 2012

Re: Go to other cell based on matching values using button control

menikhali,

Please do not quote entire posts. When quoting follow these guidelines

1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow. Quote deleted.
Thanks

menikhali · Dec 26th 2012

Re: Go to other cell based on matching values using button control

hi there, thanks for replying.
when i debug, it shows ( strName="" and strDate=00:00:00 ); wheres
range("b2").value and range ("c2").value showing their cells value

additionally,
set rName and rDate result showing "nothing"

Plz clarify it
a b c d e
[TABLE="width: 353"]

[tr]

[TD="align: left"]

1

[/TD]

[td]

NameList

[/td]

[td]

DateList

[/td]

[td][/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

orange

[/td]

[TD="align: right"]01/01/2012[/TD]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

date

[/td]

[TD="align: right"]01/01/2012
[/TD]
[TD="align: right"]02/01/2012
[/TD]
[TD="align: right"] 03/01/2012
[/TD]

[/tr]

[tr]

[td]

4apple

[/td]

[td][/td]

[/tr]

[tr]

[td]

5oane

[/td]

[td][/td]

[/tr]

[tr]

[td]

5 mngo

[/td]

[td][/td]

[/tr]

[/TABLE]

Go to other cell based on matching values using button control

Participate now!

Share