Excel Macro That Programmatically finds a matching cell

Question

In cell R12, I have an integer that changes each time I need to run this macro. I need the macro to analyze what the number is in that cell, and find the one other cell on the spreadsheet (guaranteed to be in the range M31:M40 because that is where the match will be) that matches the contents of the cell R12, and then makes the matched cell the active cell.
Obviously, I could tell excel what the contents are in R12, and do a search, but I need it to determine whats in R12 by itself...then find the matching cell, and go there.

No one types the value of R12. It is calculated. R12 is a number that is created by totaling the contents of ten other cells in that column. I am trying to leave "breadcrumbs" for my macro to find its way back to a certain spot on the spreadsheet, where the macro can continue and paste data there... I'm hoping Excel can determine the number in R12, and find that exact number elsewhere on the sheet..by itself. That same number WILL exist in the other array... (M31:M40) If it somehow can move the active cell to the matching number, I can get my active cell back to where I started the macro. The full contents of the macro weren't posted. Hope that clarifies.

How is R12 changed? If someone types a value there some VBA can do this. — andy holaday
Can you shed some light on the larger purpose for this? Maybe your overall approach could use some help too. — Marc
You can store the Activecell address at the beginning of the routine for retrieval later in the routine. You should consider posting your code. From what you've described so far, it sounds like you are making things more difficult than they need to be. — Doug Glancy

Tony Dallimore Tony Dallimore · Accepted Answer · 2012-05-05T20:10:46

I am guessing from "Hunting and pecking thru info to build macros to enhance the efficiency of all of the jobs done at a video post-production facility" that your knowledge of VBA is limited and you do not know what functionality might be relevant. This answer is an introduction to the event routines which I believe are what you need.

Events occur: workbook opened or closed, worksheet added, cell value changed, etc. For many of these events, you can create a routine to be called when that event occurs.

To demonstrate these routines, I have created a workbook and a worksheet "Work". I have set 12 cells to numeric values and set R12 to their sum. I have set range J2:O25 to the numbers 1 to 144. This is a larger range than you want but this has no effect on the principle.

In the code below, I have used two event routines: Workbook_Open and Workbook_SheetChange.

In Workbook_Open, I save the original value of R12. In Workbook_SheetChange, If the value of R12 has changed and if J2:O25 contains the new value, I move the cursor to it.

If I understand your question, this is the functionality you seek. If not, I hope this answer will help you to ask a better, more detailed question.

You must place this statement in a module:

  Public R12Last As Variant

You must place the following code in ThisWorkbook which you will find in Microsoft Excel Objects under the sheets.

Option Explicit
Sub Workbook_Open()

  ' This routine is called when the workbook is opened.

  With Worksheets("Work")
    ' Save the value of cell R12 when the workbook opens.
    R12Last = .Range("R12").Value
  End With

End Sub

Private Sub Workbook_SheetChange(ByVal WSht As Object, ByVal ChgRng As Range)

  ' This routine is called when cells in any worksheet are changed by the
  ' user or by an external link.

  ' WSht is the worksheet within which cells have changed.
  ' ChgRng can be a single cell or a range.

  ' For this application, we are not interested in the cell that has changed.
  ' We want to know if the change affected R12.

  Dim SearchRng As Range
  Dim FoundRng As Range

  With Worksheets("Work")
    If R12Last <> .Range("R12").Value Then
      ' Cell R12 has changed
      R12Last = .Range("R12").Value     ' Record the new value for next time

      ' I have a larger range containing the values that might be in R12
      ' but the principle is the same.  You will need to change this to M31:M40.
      Set SearchRng = .Range("J2:O25")

      ' Look for the new value of R12 in SearchRng.
      ' "What" is the value to be found.  "After" must be within the search
      ' range. "After" is the last cell to be searched.  I have set
      ' SearchDirection to xlNext and SearchOrder to xlByRows so the Find will
      ' check cells starting with J2.
      ' Look Find up in VBA Help for a fuller description.
      Set FoundRng = SearchRng.Find(What:=R12Last, After:=Range("O25"), _
                                    LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext)
      If FoundRng Is Nothing Then
        ' The value of R12 has not been found in the search range.
        ' Add code to handle this situation
        Call MsgBox("Target value not found", vbOKOnly)
      Else
        ' Make the cell with the matching value the active cell.
        FoundRng.Select
      End If
    End If
  End With

End Sub

Excel Macro That Programmatically finds a matching cell

2 Answers