Doing Cntrl F by VBA, if value not found then do another Cntrl F with different value

[ThrottleWorks]

Hi,

Sorry for the confusing subject line.
I am using below code for doing control F.

Cells.Find(What:=rn, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I am finding a string defined as rn from a file.

The problem is rn may or may not be present in the file.
If rn (string value) is not present, then the code gives bug.
Run-Time Error 91

If the code gives bug or if the rn is not present I have to change my rn value and search again.

For example, my rn value is "A". The value is not present in the file.
So the macro will give bug, in this case I want to find rn.offset(0,1).

The reason is, I have 2 columns. Column A and Column B.
If cell("A1").value is giving bug then I have to find cell("b1").value in the same file.

I know how to write "If then End if" but do not know how to write if there is an error.
If I use on error resume next, then that is also wrong.

I am trying to write code in below fashion but not able to do it

If first control find result = error then excute below code.

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

Can anyone please help me in this.

 

[Hui]

Dim i As Integer
On Error Resume Next

For i = 0 To 1
  Cells.Find(What:=rn.Offset(0, i), After:=ActiveCell, LookIn:=xlFormulas, _
  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False, SearchFormat:=False).Activate
Next

 

[ThrottleWorks]

Hui Sir, thanks a lot for the help.
Sorry but I am not able to understand your code.

I am getting bug at the first instance, which is,

Cells.Find(What:=rn, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Instead of bug, I want to use below mentioned code.

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

Once again, sorry (if) I am missing something from your post.

P.S - I understood it now, thanks a lot.
I got confused because I was under impression that on error resume next has to be used. Your code does not have this line so I thought that this will give bug if value is not found at first instance. I still do not know how it works.

Good night.

 

[Marc L]

Hi,

If you just have well read the VBA help of Find method and its code example, no need On Error …
​

 

[ThrottleWorks]

Thanks for the guidence Marc, will definately do, have a nice day ahead.