Giggs1991

Board Regular
Joined
Mar 17, 2019
Messages
50
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
===========================================
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top