go to a specific row in excel

[reddcannon]

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

 

[Logit]

"between cells a6:a113"

Why such a broad range of cells ? Perhaps you should explain further.

 

[Joe4]

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:

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.

 

[reddcannon]

"between cells a6:a113"

Why such a broad range of cells ? Perhaps you should explain further.

that is all the rows, I guess I could have just said Row A the values start at A6 and End at A113.

 

[Joe4]

I am not sure what the issue is - it seems like a pretty clear and straightforward question.
Did you try my solution?

 

[reddcannon]

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:

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

 

[reddcannon]

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

 

[Joe4]

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:

=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?

 

[reddcannon]

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:

=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

 

[Joe4]

What is the exact value you are looking up in that example?
I want to re-create your exact example.