go to a specific row in excel

reddcannon

Board Regular
Joined
Aug 11, 2011
Messages
118
Using windows 10
office 2016
I want to type a dollar amount in cell b4 and have it go to the row in column "A" between cells a6:a113
I have tried so many ways, like vlookup, match, find etc and nothing is working. Can someone help please
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
"between cells a6:a113"

Why such a broad range of cells ? Perhaps you should explain further.
 
Upvote 0
Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if cell B4 was just updated
    If Target.Address(0, 0) = "B4" Then
'       Find value entered in cells
        Set rng = Range("A6:A113").Find(What:=Target.Value, After:=Range("A6"), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
'       If value found, go to cell, otherwise return message
        If rng Is Nothing Then
            MsgBox "Cannot find the value " & Target.Value & " in A6:A113", vbOKOnly, "ERROR!"
        Else
            rng.Select
        End If
    End If

End Sub
Then, whenever you type a value in B4 it will look for it in A6:A113 and if it finds it, it will select that cell.
Otherwise, if it does not find it, it will tell you that in a Message Box.
 
Upvote 0
Solution
I am not sure what the issue is - it seems like a pretty clear and straightforward question.
Did you try my solution?
 
Upvote 0
Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   Only run if cell B4 was just updated
    If Target.Address(0, 0) = "B4" Then
'       Find value entered in cells
        Set rng = Range("A6:A113").Find(What:=Target.Value, After:=Range("A6"), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
'       If value found, go to cell, otherwise return message
        If rng Is Nothing Then
            MsgBox "Cannot find the value " & Target.Value & " in A6:A113", vbOKOnly, "ERROR!"
        Else
            rng.Select
        End If
    End If

End Sub
Then, whenever you type a value in B4 it will look for it in A6:A113 and if it finds it, it will select that cell.
Otherwise, if it does not find it, it will tell you that in a Message Box.
It says it cannot find it, even though it is there, could it because they are dollar values
 
Upvote 0
I am not sure what the issue is - it seems like a pretty clear and straightforward question.
Did you try my solution?
I did and it says it cannot find it. I kchanged all my values to general instead of dollars and it still says cannot find it
 
Upvote 0
It says it cannot find it, even though it is there, could it because they are dollar values
It sounds like you may have a mix of text and numbers.

Do the following:
Enter a value that you know has a match. Let's say it in cell A40. Then enter these four formulas into any blank cells on your sheet and tell me what they all return:
Excel Formula:
=ISNUMBER(B4)
=LEN(B4)
=ISNUMBER(A40)
=LEN(A40)
(be sure to change "A40" to whatever cell the match appears in).

Also, are any of these values calculated, or have more decimal places than what is being shown?
 
Upvote 0
It sounds like you may have a mix of text and numbers.

Do the following:
Enter a value that you know has a match. Let's say it in cell A40. Then enter these four formulas into any blank cells on your sheet and tell me what they all return:
Excel Formula:
=ISNUMBER(B4)
=LEN(B4)
=ISNUMBER(A40)
=LEN(A40)
(be sure to change "A40" to whatever cell the match appears in).

Also, are any of these values calculated, or have more decimal places than what is being shown?
returns with the following
=ISNUMBER(B4) TRUE
=LEN(B4) 6
=ISNUMBER(A40) TRUE
=LEN(A40) 6
 
Upvote 0
What is the exact value you are looking up in that example?
I want to re-create your exact example.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,022
Latest member
RobertV1609

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