VBA Find and Select loop

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
28
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,304
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.

Code:
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
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,289
Office Version
2013
Platform
Windows
Hi,
try this update to your code & see is does what you want

Rich (BB code):
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,382
Office Version
2013
Platform
Windows
Another way:

Code:
[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

New Member
Joined
Mar 17, 2019
Messages
28
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,304
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,289
Office Version
2013
Platform
Windows
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
 

Forum statistics

Threads
1,082,587
Messages
5,366,486
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top