Find next without activating First cell Address
 

Hi again.


In My following code how can I limit the search in CurRange?

also running the code from sheet1 while the named ranges are in sheet2
is it possible to find the Next Occurance without Activating any
CellIs?

In my code i have to activate both FirstCell and NextCell.

Thabks for your help.

Sub FindWord(WhatToFind As Variant)

Dim FirstCell As Range
Dim NextCell As Range
Dim CurRange As Range
Dim CountFound As Long
Dim i As Integer

Application.ScreenUpdating = False

If CmbAraEng = "Arabic" Then
Set CurRange = Range("Arabic")
ElseIf CmbAraEng = "English" Then
' Set CurRange = Range("English")
ElseIf CmbAraEng = "Malayalam" Then
' Set CurRange = Range("Malayalam")
ElseIf CmbAraEng = "Urdu" Then
Set CurRange = Range("Urdu")
End If

With CurRange

If WhatToFind < "" Then
Set FirstCell = Cells.Find(What:=WhatToFind,
LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not FirstCell Is Nothing Then
FirstCell.Activate
i = LstFind.ListCount
LstFind.AddItem FirstCell.Address
CountFound = CountFound + 1

LstFind.List(i, 1) = Cells(FirstCell.Row, 1).Value
LstFind.List(i, 2) = Cells(FirstCell.Row, 2).Value
LstFind.List(i, 3) = Cells(FirstCell.Row, 3).Value

CountFound = CountFound + 1
On Error Resume Next
While (Not NextCell Is Nothing) And (Not
NextCell.Address = FirstCell.Address)
Set NextCell = Cells.FindNext(After:=X)
If Not NextCell.Address = FirstCell.Address
Then
NextCell.Activate
i = LstFind.ListCount
LstFind.AddItem NextCell.Address

LstFind.List(i, 1) = Cells(NextCell.Row, 1).Value
LstFind.List(i, 2) = Cells(NextCell.Row, 2).Value
LstFind.List(i, 3) = Cells(NextCell.Row, 3).Value
CountFound = CountFound + 1
End If
Wend
End If
Set NextCell = Nothing
Set FirstCell = Nothing


End If
LblCount.Caption = CountFound & " Matche(s) Found"

On Error Resume Next
Application.ScreenUpdating = True
End Sub