Nested if issue

[bfournier5]

Hello, looking for some help to get this code working. The code I have working so far is to check if some specific text is part of a range or not. The saerch tells me if the text is French or English by searching for specific words. This part works and will place the value where the text is in a cell. Here is the code I have

' To see if the greeting is in English …
ActiveCell.Formula = "=MATCH(P1,SingleInteraction,0)"
ActiveCell.Offset(1, 0).Select
' To see if the greeting is in French …
ActiveCell.Formula = "=MATCH(P2,SingleInteraction,0)"
ActiveCell.Offset(-1, 0).Select


IsEnglish = ActiveCell.Value

GreetingRow = ActiveCell.Value

Range("S4").Select
ActiveCell.Offset(GreetingRow - 1, 0).Select
Selection.Copy

If the IsEnglish variable has a number - the code works. It means the result of ActiveCell.Formula = "=MATCH(P1,SingleInteraction,0)" is a value and then I get what I want.

If the result of ActiveCell.Formula = "=MATCH(P1,SingleInteraction,0)" is #n/a, then the code stop, which is normal because I want the code to go to take the value from ActiveCell.Formula = "=MATCH(P2,SingleInteraction,0)". Note that I'm in a If ... then ... Else with a loop.

 

[yky]

Post if as a private message to me.

 

[yky]

If Not IsError("Match(P1, SingleInteraction, 0)") Then

This line is not doing what you want to do. You just pass a string to the IsError function. So, IsError always returns FALSE because there is no error. Try this:

If Not IsError(Application.Match(P1, SingleInteraction, 0)) Then

 

[yky]

Since I don't the worksheet, I tested the following code in A1:A20. The code does tell if the range contains English or French.

[COLOR=#008000]'To see if the greeting is in English … #n/a means it is not English[/COLOR]
If Not IsError(Application.Match(Range("P1"), Range("A1:A20"), 0)) Then
ActiveCell.Offset(1, 0).Select
ElseIf Not IsError(Application.Match(Range("P2"), Range("A1:A20"), 0)) Then
[COLOR=#008000]'To see if the greeting is in French … #n/a means it is not French[/COLOR]
ActiveCell.Offset(-1, 0).Select
Else
MsgBox ("no match")
End If

You said you got a type mismatch error on this line:

IsEnglish = ActiveCell.Value

What is in ActiveCell? IsEnglish is declared as an integer. If ActiveCell doesn't contain an integer, you'd get the error message.

 

[bfournier5]

Since I don't the worksheet, I tested the following code in A1:A20. The code does tell if the range contains English or French.

[COLOR=#008000]'To see if the greeting is in English … #n/a means it is not English[/COLOR]
If Not IsError(Application.Match(Range("P1"), Range("A1:A20"), 0)) Then
ActiveCell.Offset(1, 0).Select
ElseIf Not IsError(Application.Match(Range("P2"), Range("A1:A20"), 0)) Then
[COLOR=#008000]'To see if the greeting is in French … #n/a means it is not French[/COLOR]
ActiveCell.Offset(-1, 0).Select
Else
MsgBox ("no match")
End If

You said you got a type mismatch error on this line:

IsEnglish = ActiveCell.Value

What is in ActiveCell? IsEnglish is declared as an integer. If ActiveCell doesn't contain an integer, you'd get the error message.

The ActiveCell as the value 5 in it.
IsEnglish is a declared variable like this ... Dim IsEnglish As Integer.

I can provide you with a sample Worksheet so that you see the issue.

 

[bfournier5]

I have sent you an email to your Yahoo address you gave me as private message. Let me know if you received it.