I have a worksheet “Mentor Search” which DerbyDad03 has helped me with previously. It performs a search for mentors with a particular skill and returns all names that hold that skill.
I’m trying to develop it further so that when you click on any name from the returned list a message box opens with their short biography that I have stored on a separate worksheet “Bios”.
I’ve managed to make it work for the skill chosen, where if you choose Excel from a drop down list, and Fred and Bob are returned as the skill holders, when you click on each of their names the message box is successfully displayed. However, as soon as I choose a different skill from the drop down list and then click on any of the returned skill holders, I get a Run Time Error 91: Object variable or With block variable not set.
I’ve tried debugging and think I understand what the problem is, but don’t know how to fix it. The code is triggered on a selection change and is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SearchScr As Worksheet Dim Bio As Worksheet Set SearchScr = Worksheets("Mentor Search") Set Bio = Worksheets("Bios") 'Find mentor name from clicked on name in "Mentor Search" With Bio.Range("$A$2:$B$5") Set n = .Find(Target, lookat:=xlWhole) End With 'Look for relevant mentor's bio and display it in a message box With Bio.Rows(n.Row) Set b = Bio.Cells(n.Row, 2) MsgBox (b) End With End Sub
When I click Debug, it takes me to the line
With my very limited knowledge I suppose that I have to clear the variable ‘n’ when a new skill is chosen in the drop down but I’m not sure?
If you need more information please let me know.