Find a value in a workbook and activate that cell

[giyer.ops]

Hi
Need a code in VBA which, finds a value in a workbook and activates that cell.

 

[obiron]

In a workbook or in a worksheet?

In a worksheet is relatively easy
In all the worksheets of a workbook is also not too difficult
finding the values in a chart sheet could be harder

do you just want the 1st occurance (define how to find first: worksheets, rows, columns), all occrances or a count

Do you want to do this for a single value or for many values (it could get slow if you have a lot of them)

 

[Andrew Poulsom]

Try:

Sub Test()
    Const LookFor As String = "abc"
    Dim Sh As Worksheet
    Dim rng As Range
    For Each Sh In ActiveWorkbook.Worksheets
        With Sh
            Set rng = .Cells.Find(What:=LookFor, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:= _
                xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                , SearchFormat:=False)
            If Not rng Is Nothing Then
                    Application.Goto rng, True
                Exit Sub
            End If
        End With
    Next Sh
End Sub

Change LookFor to suit.

 

[giyer.ops]

.Cells(.Cells.Count) gives me an error of "Overflow"

Also I replaced it with ActiveCell it does not work.
It is searching for the the "valu" but not activating the cell

For Each Sh In ActiveWorkbook.Worksheets
With Sh
Set rng = .Cells.Find(What:=valu, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Exit Sub
End If
End With
Next Sh

 

[giyer.ops]

I tweeked the macro a little, go the solution

For Each Sh In ActiveWorkbook.Worksheets
With Sh
Set rng = .Cells.Find(What:=valu, After:=ActiveCell.Offset(-1, 0), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not rng Is Nothing Then
Application.Goto rng, True


Exit For
End If
End With
Next Sh

 

[Andrew Poulsom]

If you have Excel 2007 or above you can use CountLarge to avoid the overflow.