VBA Find and Select loop

[Giggs1991]

I have the below line of code as part of my VBA .

As shown below, the first "find " section looks for the word "Paris". When it finds the work Paris, it updates the cell value as France and then moves on to the 2nd "find" section where it looks for the word "London and then updates the cell value as England.

The problem I have with this is that when the code does not find the word "Paris", it shows up an error. Is there a way to modify the below code in such a way that even if the word "paris" is not found in the first "find" section, the vba will still continue to look for the work "London" in the 2nd "find" section without giving an error message.

Cells.find(What:="Paris", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value <> ""
If ActiveCell.Value = "" Then
ActiveCell.Value = "France"
End If
ActiveCell.Offset(1, 0).Select
loop




Cells.find(What:="London", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Value <> ""
If ActiveCell.Value = "" Then
ActiveCell.Value = "England"
End If
ActiveCell.Offset(1, 0).Select
Loop
===========================================

 

[My Aswer Is This]

It's never a good practice to search the entire sheet.
And it's never a good practice to use active cell.

I'm not sure I understand your request but if your attempting to search column A for:

Paris and London

And if found put France in column 2 same row
And if found put England in column 2 same row

Try this script:

Add more values to the script as needed.

You should see the ideal.

If this is not what you want please explain again I may be slow at understanding.

Sub Find_Me()
'Modified  11/11/2019  1:50:14 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        With Cells(i, 2)
            Select Case Cells(i, 1).Value
                Case "Paris"
                    .Value = "France"
    
                Case "London"
                    .Value = "England"
    
            End Select
        End With
    Next
Application.ScreenUpdating = True
End Sub

 

[dmt32]

Hi,
try this update to your code & see is does what you want

Option Base 1
Sub Giggs1991()
    Dim rng As Range, FoundCell As Range
    Dim CityNames As Variant, CountryNames As Variant
    Dim City As Variant
    Dim strCountry As String, FirstAddress As String
    
    Set rng = ThisWorkbook.Worksheets("Sheet1").UsedRange
    
    CityNames = Array("Paris", "London")
    CountryNames = Array("France", "England")
    
    For Each City In CityNames
        Set FoundCell = rng.Find(What:=City, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                 SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not FoundCell Is Nothing Then
            FirstAddress = FoundCell.Address
            
            strCountry = CountryNames(Application.Match(City, CityNames, 0))
            Do
                FoundCell.Value = strCountry
                Set FoundCell = rng.FindNext(FoundCell)
                If FoundCell Is Nothing Then Exit Do
            Loop Until rng.Address = FirstAddress
        End If
        Set FoundCell = Nothing
    Next City
        
End Sub

Note Option Base 1 statement at the top of code. This MUST be placed at very TOP of your module OUTSIDE any procedure.
Change the sheet name you are searching shown in RED as required.

You can add to The CityNames & CountryNames Arrays as required.

Dave

 

[Akuini]

Another way:

[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1114683a()
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] ary, arz
Application.ScreenUpdating = False
ary = Array([COLOR=Darkcyan]"Paris"[/COLOR], [COLOR=Darkcyan]"London"[/COLOR])
arz = Array([COLOR=Darkcyan]"France"[/COLOR], [COLOR=Darkcyan]"England"[/COLOR])
[COLOR=Royalblue]For[/COLOR] i = [COLOR=Royalblue]LBound[/COLOR](ary) [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](ary)
    Cells.Replace What:=ary(i), Replacement:=arz(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[COLOR=Royalblue]Next[/COLOR]
Application.ScreenUpdating = True
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Note: if you want to find as whole match (not partial match) then replace this:
LookAt:=xlPart
with this:
LookAt:=xlWhole

 

[Giggs1991]

Hi All,

Thank you for your brilliant replies. Just wanted to know how to loop from first find section to the 2nd find section if the word Paris is missing in below code. The code works fine is the words both Paris ans London are present. However, if the word Paris is absent, VBA throws an error. Is there a way to go from first find section to 2nd find section even if the word Paris is missing? :


Cells.Find(What:="Paris", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select










Cells.Find(What:="London", After:= _
ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select

 

[My Aswer Is This]

I provided a code that I thought would work for you and two other posters provided their suggestions on how to do what you wanted.

But you said nothing about if you tried any of the provided code.

I have no solution on how you can use the code your wanting to use and have it work for you.

It would be nice if you were to explain in words what your attempting to do.

Showing us code that does not work without explanation of what your trying to do is not helpful to me.

 

[dmt32]

Hi All,

Thank you for your brilliant replies. Just wanted to know how to loop from first find section to the 2nd find section if the word Paris is missing

You have been shown how to resolve this

My Solution kept with the Range.Find method you are using & shows how to loop through each of the search values.

Others have provided alternative solutions you can consider


Dave