Go To Cell in Excel 1

[DeLaMartre]

I have scoured my books, but I can't find the answer to building a formula which will take you directly (or indirectly) to a specified cell in a similar fashion to the "Find" function on the toolbar. Also, I would like to have the capability to find the next duplicate entry in the range (and the next, etc).

Any help will be greatly appreciated. Thank you. -Bob-

 

[xlbo]

AFAIK - A formula can't create an action - therefore, no formula is going to be able to replicate the "Find" functionality.
You could write a formula to give the cell address of what you're trying to find but not actually take you there - for that you need code (which can be provided)
Again, with duplicate entries, you can build a formula to indicate which entries are duplicate but you're not gonna get one that takes you to 'em
Geoff

 

[DeLaMartre]

Thanks very much for the response, Geoff. Could you please provide the VBA code for the "Find" and "Duplicate Entries Find"?

I appreciate your help! -Bob

 

[xlbo]

Hi Bob - sorry I havn't replied sooner - here's the find code:
sub Finder()
Columns("A:A&quot.Select 'Change this to include your whole search area
mySearchText = Range("B1&quot.text
Selection.Find(What:=mySearchText,After:=ActiveCell,LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
).Activate
Activecell.select
end sub

For duplicates, it kinda depends on exactly what you want to do - do you want to highlight a cell and find out whether there is a duplicate, do previous and then take you there, create a list of all duplicates in a seperate area etc etc ??
HTH
Geoff

 

[DeLaMartre]

Thanks so very much for the code, Geoff, it works great!

As regards the second item, I would like to either do previous and then take you there, or create a list of all duplicates in a seperate area. If you have a solution for this, I would be enormously grateful.

You are a great human being!

-Bob

 

[xlbo]

ok - you ready for this......
Sub FindDupes()
Dim chkCell As Range, lRow As Integer, chkTxt As String, Dim chkDupe As Integer, chkAdd, chkCol As String
Dim Duplicates(50) As Variant

Set chkCell = ActiveCell
chkTxt = chkCell.Text
chkAdd = chkCell.Address
chkCol = Mid(chkAdd, 2, 1) & ":" & Mid(chkAdd, 2, 1)

lRow = ActiveSheet.Range(Mid(chkAdd, 2, 1) & "65536&quot.End(xlUp).Row

chkDupe = WorksheetFunction.CountIf(Range(chkCol), chkTxt)
Select Case chkDupe
Case Is <= 1
MsgBox &quot;There are no duplicates for &quot; & chkTxt & &quot; (&quot; & chkAdd & &quot&quot;
Exit Sub
Case Is > 1
MsgBox &quot;There are &quot; & chkDupe & &quot; occurences of &quot; & chkTxt
End Select
A = 0
For i = 2 To lRow
If Range(Mid(chkAdd, 2, 1) & i).Text = chkTxt Then
dAdd = Range(Mid(chkAdd, 2, 1) & i).Address
Duplicates(A) = dAdd
A = A + 1
Else
End If
Next i
For x = 0 To A - 1
Application.Goto reference:=Sheets(&quot;Sheet1&quot.Range(Duplicates(x))
MsgBox &quot;occurance &quot; & x + 1
Next x
End Sub

All you have to do is select the cell that you want to test for dupes and play the macro
Please let me know what you think
HTH
Geoff

 

[BobDe]

Whew! Thanks for all of your hard work, Geoff! This is exactly what I need.

I gave you a star, and I hope others recognize you in this regard as well.

Thanks again for your fine expertise and willingness to help out another user.

Cheers,

Bob

 

[xlbo]

Just realised - to make it totally dynamic, you need to change Goto reference:=Sheets(&quot;Sheet1&quot
to Goto reference:=Sheets(activesheet.name)

Rgds
Geoff