ststern45
Well-known Member
- Joined
- Sep 17, 2005
- Messages
- 961
- Office Version
- 365
- 2010
- Platform
- Windows
I'm using the following Function below which works perfectly.
The only quirk is that when I click a Toolbar I created on another worksheet to sort 2 columns of data and then go to the original worksheet the cells that contain this formula change to #VALUE in the selected cells that contain the Function below.
I wanted to ask what may be causing the #VALUE to appear. The Function worked perfectly fine until I clicked a Toolbar in another worksheet.
Function Nth_Occurrence(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
The only quirk is that when I click a Toolbar I created on another worksheet to sort 2 columns of data and then go to the original worksheet the cells that contain this formula change to #VALUE in the selected cells that contain the Function below.
I wanted to ask what may be causing the #VALUE to appear. The Function worked perfectly fine until I clicked a Toolbar in another worksheet.
Function Nth_Occurrence(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
