0

I have wrote a code to find if a value exists in other sheet then if exists it selects the cell. The code worked without any errors when I have implemented it, but when I have closed and reopened excel it throws the Run-time error 91. My code below:


Dim rngCell As Excel.Range
Dim Rng As Excel.Range
Dim FindCell As String

If Not Intersect(Target, Range("C8:O8")) Is Nothing Then
            For Each rngCell In Intersect(Target, Range("C8:O8"))
                FindCell = rngCell.Value
                With Sheets("Master List").Range("A:A")
                    Set Rng = .Find(What:=FindCell)
                    If Not Rng Is Nothing Then
                        With Sheets("Master List").Select
                            Selection.Find(What:=FindCell).Activate
                        End With
                    Else
                    'do nothing'
                    End If
                End With
            Next rngCell
            Sheets("Data Input").Select
        End If

End Sub

The error is on the line Selection.Find(What:=FindCell).Activate I understand that it might be because FindCell is not assigned as an object? Weird that it worked before, so I have no idea how to fix it.

I tried to find this in previous questions, but couldn't get it from there.

Thanks, Adam

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Adam Ziaja
  • 15
  • 3
  • Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and get rid of all `.Select` statements. Further before you can `.Activate` you need to ensure that something was found by `Selection.Find(What:=FindCell)` you cannot `.Activate` if nothing was found. You need to do the same check as you did here `If Not Rng Is Nothing Then` for `Rng`. – Pᴇʜ May 07 '21 at 10:10
  • Further you need to read the documentation of the [Range.Find method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) because you should at least specify those parameters `LookIn`, `LookAt`, `SearchOrder`, and `MatchByte` otherwise Excel will use whatever was used before by VBA or the user interface and your code will randomly work or not work! – Pᴇʜ May 07 '21 at 10:12
  • @Pᴇʜ Thanks! This is great help. I've re-wrote the whole code with your hints and it works perfect. Also faster! – Adam Ziaja May 07 '21 at 11:35
  • Great! Yes, using `Select` makes it really slow. – Pᴇʜ May 07 '21 at 11:37

0 Answers0