Go to cell in a column that matches the value of another cell

[kivikatz]

Hi all. I would like to add VBA code that scrolls to cell in a column which matches the value of another cell. For instance if a cell in Column A1:A100 matches cell B1, I would like to scroll down to the cell in Column A. Alternatively, is it possible if I manually click on cell B1 that it will scroll down to the matching cell in Column A.

I've searched around but cannot seem to find a solution. Thanks for all your help.

 

[petertenthije]

Sub Macro1()

' Find the last row number in column A.
    Dim lastRow As Long
    With Sheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

' Find the value you are looking for
    lookupvalue = Worksheets("Sheet1").Range("B1").Value
 
' Select the range you are looking in
    lookuprange = Worksheets("Sheet1").Range("A1:A" & lastRow)
 
' Find the first matching value, and return the row number
' If there is no matching number, the macro will jump to "ErrorMessageBox"
    On Error GoTo ErrorMesageBox
    FirstMatchRowNumber = WorksheetFunction.Match(lookupvalue, lookuprange, 0)

' Go to the applicable row.
    Worksheets("Sheet1").Range("A" & FirstMatchRowNumber).Select
Exit Sub



' In case of an unknown number, the macro will show an message box
ErrorMesageBox:
    MsgBox "The number entered in B1 is not known in A1:A" & lastRow
    Exit Sub
End Sub

 

[Fluff]

Another option

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   
   If Target.Count > 1 Then Exit Sub
   If Target.Address(0, 0) = "B1" And Target.Value <> "" Then
      Set Fnd = Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Activate
      Else
         MsgBox Target.Value & " not found"
      End If
   End If
End Sub

This needs to go in the relevant sheet module.

 

[kivikatz]

Hi All. Sorry for the late reply. I was tied up with things and did not have a chance to check the post. It seems I am missing something with both of the solutions. Maybe it's because the data in column A and cell B2 is calculated based on input from another sheet. Could this be the problem why the solutions doesn't work, or am I doing something wrong with adding the VBA code?

Is it possible to click on cell B2 and then scroll to the matching cell in column A. Thanks!

 

[Fluff]

Do you mean B1 as in your op, orB2?

 

[kivikatz]

Sorry. I meant B1. Dunno why I typed B2.

 

[Fluff]

Ok, how about a double click event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Fnd As Range
  
   If Target.Address(0, 0) = "B1" And Target.Value <> "" Then
      Set Fnd = Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Activate
      Else
         MsgBox Target.Value & " not found"
      End If
   End If
End Sub

 

[kivikatz]

Ok, how about a double click event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim Fnd As Range
 
   If Target.Address(0, 0) = "B1" And Target.Value <> "" Then
      Set Fnd = Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Fnd.Activate
      Else
         MsgBox Target.Value & " not found"
      End If
   End If
End Sub

Thanks for trying to help me out, I tried the solution, but it doesn't work for me. Not sure what I am missing. When I double click on B1, I get a message box "Not Found". even though the data in B1 exists in one of the cells in column A.

 

[Fluff]

Is B1 an exact match or a partial match? Also what sort of values do you have?

 

[kivikatz]

Is B1 an exact match or a partial match? Also what sort of values do you have?

The values are dates, and B1 is an exact match of the value found in column A. Thanks.