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]

' 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

Are you checking the language in two cells? Once a cell is selected, the ActiveCell changes. The second MATCH is checking a different ActiveCell than the first one. If you are not checking the language in two cells, you want to put the code in an If ... End If statement. And if there are only two possible languages, you only need to check one.

[COLOR=#008000]' To see if the greeting is in English …[/COLOR]
    If Not ISERROR(MATCH(P1,SingleInteraction,0)) then
    ActiveCell.Offset(1, 0).Select
Else
[COLOR=#008000]' To see if the greeting is in French …[/COLOR]
[COLOR=#008000]'ActiveCell.Formula = "=MATCH(P2,SingleInteraction,0)" (probably no need)[/COLOR]
    ActiveCell.Offset(-1, 0).Select
End If

 

[bfournier5]

Hello yky

Thanks for the reply ... sorry it took me a long time to reply here ... I was in vacation out of internet range.

The thing I'm doing here is that I'll have a range of text that will either be English or French and should always have a specific sentence given in it. I want to find out which specific row it is mentioned in that range. So far, my code works if the interaction is in English, but as soon as the range is in French, code stop ... but you have given me pointers to test... I'll get back to you

 

[bfournier5]

When I run the code ... it will prompt with an error at this line

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

with MATCH being highlighted with the prompt message saying Sub or Function not defined

 

[bfournier5]

Solved it by adding quotes If Not IsError("=MATCH(P1,SingleInteraction,0)") Then

 

[bfournier5]

I still get the same problem ... the code run great until this

If TotRowInteraction > 4 Then

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

IsEnglish = ActiveCell.Value

The last line of the code above is highlighted with the error "Type mismatch". The reason is simple, the actual value for Match(P1, SingleInteraction, 0) is #n/a in the cell and what I want the code to do in this situation is to jump to the ElseIf section lower in code

 

[yky]

When I run the code ... it will prompt with an error at this line

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

with MATCH being highlighted with the prompt message saying Sub or Function not defined

Instead of Match, it should be Application.Match.

 

[bfournier5]

Instead of Match, it should be Application.Match.

Maybe I'm not doing it the right way ...

the problem I have is that I'm unable to get the code to go to the next step if the value is #N/A.

Here the code I'm having problem with ...

' To see if the greeting is in English … #n/a means it is not English
ActiveCell.Formula = "=MATCH(P1,SingleInteraction,0)"
ActiveCell.Offset(1, 0).Select

' To see if the greeting is in French … #n/a means it is not French

ActiveCell.Formula = "=MATCH(P2,SingleInteraction,0)"
ActiveCell.Offset(-1, 0).Select

==> up until here ... it goes perfect, the value is showed in the proper cells. If the TotRowInteraction is less then 3, then the code works like a charm. If the value is higher then 4, and I have a value inside the cell that has the formula "=MATCH(P1,SingleInteraction,0)" then that's where it breaks ... with this code like that :

If TotRowInteraction > 4 Then

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

it will simply ignore the second if and goes to the next one and fails.

what I want the code to do is this ... if the TotRowInteraction is greater then 4 AND =MATCH(P1,SingleInteraction,0) is a value, I want this value to be added to 2 variables. If the formula =MATCH(P1,SingleInteraction,0) is #N/A, then I want it to go check is =MATCH(P2,SingleInteraction,0) is a value ... and if this is the case, then apply that value to my 2 variables.

 

[yky]

Would you post the entire code? The code has changed from what is in the original post. Thus, it's difficult to debug without seeing the entire code.

 

[bfournier5]

Would you post the entire code? The code has changed from what is in the original post. Thus, it's difficult to debug without seeing the entire code.

Is it possible to send it to you private?