Condition If and Else

[Shenzar]

Hello,

I have been looking for a solution for a few days

In my code below

Sub Mod2() 
Const Lignes% = 110 Dim Sh1 As Worksheet, Sh2 As Worksheet, I As Long 

Set Sh1 = Worksheets("Calcul"): Set Sh2 = Worksheets("Calc") 

For I = 2 To Lignes    
If Sh2.Range("A1") = "YOY" then 
If Sh1.Cells(I, 1) = "P-YOY-01" Then Sh2.Cells(11, 10) = Sh1.Cells(I, 8) Else Sh2.Cells(11, 10) = "NA"    
end If
Next I 
End Sub

The if and the else are executed at the same time in the same cell, both conditions are checked at the same time instead of:

if the answer is equal to 1 then the answer is equal to 2 else the answer is equal to 3

If I delete else the code works perfectly

I do not understand

 

[joeu2004]

The if and the else are executed at the same time in the same cell, both conditions are checked at the same time

There is only __one__ condition to be checked. The Then and Else parts are assignment statements, not conditions.

if the answer is equal to 1 then the answer is equal to 2 else the answer is equal to 3

And that is exactly how the statement works, if I interpret your abstract terms correctly. More completely:

1. If the value of Sh1.Cells(I, 1) is equal to "P-YOY-01", then the value of Sh1.Cells(I, 8) is assigned to Sh2.Cells(11, 10).

2. Otherwise , "NA" is assigned to Sh2.Cells(11, 10).

If "NA" is always assigned to Sh2.Cells(11, 10), then Sh1.Cells(I, 1) is not equal to "P-YOY-01".

You have not provided any information for us to doubt that behavior.

I suggest that you insert the following statement before the If-Then-Else statement. It might give you some insight.

Debug.Print i, sh1.Cells(i, 1), Len(sh1.Cells(i, 1) = "P-YOY-01"), (sh1.Cells(i, 1) = "P-YOY-01")

-----

Aside.... The following has nothing to do with your problem.

It appears that the condition Sh2.Range("A1") = "YOY" does not change in the loop -- unless the cell value changes as a side-effect of the assignment statement in the loop.

If it indeed does not change, I believe the following is equivalent and much more efficient.

If Sh2.Range("A1") = "YOY" then
For I = 2 To Lignes    
If Sh1.Cells(I, 1) = "P-YOY-01" Then Sh2.Cells(11, 10) = Sh1.Cells(I, 8) Else Sh2.Cells(11, 10) = "NA"    
Next I 
End If
End Sub

PS.... In the future, copy-and-paste from VBA into your posting. Apparently, you did not do that. The evidence:

1. You posted "end If" instead of "End If". VBA would capitalize like the latter.

2. You have a syntax in the line with the Const statement. At a minimum, there should be a colon (":") before "Dim". Even better ("good practice"), the Const and Dim statements should be on separate lines. (And perhaps they were. That would explain the "missing" colon.)

 

[Shenzar]

Hello,

thank you for your help

Indeed the code is not correctly pasted

I'm attaching the file to be more specific and see what's going on

I can't see where my mistake is

Like this the code paste the value in the cell and then paste again what is in the else: "NA"

Option Explicit

Sub Mod2()

Dim Sh1 As Worksheet, Sh2 As Worksheet, I As Long

Set Sh1 = Worksheets("Calcul"): Set Sh2 = Worksheets("Calc")

For I = 2 To 20
    If Sh2.Range("B2") = "YOY1" Then
        If Sh1.Cells(I, 1) = "P-YOY-01" Then Sh2.[B5] = Sh1.Cells(I, 2) Else [B5] = "NA"
        If Sh1.Cells(I, 1) = "P-YOY-02" Then Sh2.[B7] = Sh1.Cells(I, 2) Else [B7] = "NA"
    End If
Next I

End Sub

I would like to understand my error, before going to a solution by vlookup

 

[joeu2004]

I'm attaching the file to be more specific and see what's going on

I wish you had indeed attached the file -- or at least used XL2BB to copy-and-paste the data and formulas into your posting.

Instead, you attached an image. "If a picture is worth 1000 words, an Excel file is worth 1000 pictures" . (Credit someone else.)

If you want to "attach" an Excel file (my preference) in this forum, you must upload it to a file-sharing website, then post the public download URL in a response here. I like box.net/files; others like dropbox.com. In any case, test the download URL first, being careful to log out of the file-sharing website and close all windows that might share that login.

My wild guess.... There are extraneous characters in Calcul!A2:A13 -- perhaps regular or non-breaking spaces. That is why I suggested that you print the Len() of the cell values using Debug.Print.

But I forgot to explain that you must press ctrl+g to open the Immediate Window in order to see the Debug.Print output.

If Sh1.Cells(I, 1) = "P-YOY-01" Then Sh2.[B5] = Sh1.Cells(I, 2) Else [B5] = "NA"
If Sh1.Cells(I, 1) = "P-YOY-02" Then Sh2.[B7] = Sh1.Cells(I, 2) Else [B7] = "NA"

FYI, [B5]=... is not the same as Sh2.[B5]=... if Sh2 is not the active worksheet.

 

[Shenzar]

I was looking for how to do it on the forum here is a link of the file, it will be more precise

TransferNow

Télécharger vos fichiers dès maintenant avec TransferNow.net, le service simple et gratuit d'envoi et de partage de gros fichiers.

www.transfernow.net

 

[joeu2004]

In your postings, you said the code is:

If Sh1.Cells(I, 1) = "P-YOY-01" Then Sh2.[B5] = Sh1.Cells(I, 2) Else [B5] = "NA"
If Sh1.Cells(I, 1) = "P-YOY-02" Then Sh2.[B7] = Sh1.Cells(I, 2) Else [B7] = "NA"

which refers to column A.

But in the Excel file, we see that the code is:

If Sh1.Cells(I, 2) = "P-YOY-01" Then Sh2.[B5] = Sh1.Cells(I, 3) Else [B5] = "NA"
If Sh1.Cells(I, 2) = "P-YOY-01" Then Sh2.[B7] = Sh1.Cells(I, 3) Else [B7] = "NA"

which refers to column B.

That is your mistake.

As we see in your previous posting and in the Excel file, column B contains percentages.

It is indeed column A that contains strings like "P-YOY-01".

Also note Cells(I, 2) v. Cells(I, 3) in the Then statement. You need to decide which is right.

Some suggestions:

1. Write Cells(I,"a") and Cells(I,"b") instead of Cells(I,1) and Cells(I,2). Then such typos might be more obvious.

2. As I said before, be consistent with Sh2.[B5] v. simply [B5]. The latter is the same only if Sheets("Calc") is the active worksheet.

3. As I said before, if Sh2.Range("B2") does not change during the loop (it seems not to), change the code to the following:

If Sh2.Range("B2") = "YOY1" Then
For I = 2 To 20
        If Sh1.Cells(I, 2) = "P-YOY-01" Then Sh2.[B5] = Sh1.Cells(I, 3) Else [B5] = "NA"
        If Sh1.Cells(I, 2) = "P-YOY-01" Then Sh2.[B7] = Sh1.Cells(I, 3) Else [B7] = "NA"
Next I
End If

 

[joeu2004]

2. As I said before, be consistent with Sh2.[B5] v. simply [B5]. The latter is the same only if Sheets("Calc") is the active worksheet.

And previously, I suggested that you use Sh2.[B5], if only for consistency and to avoid depending on what the active worksheet is.

But in fact, that seems to be required(!) -- at least in your file.

Without the "Sh2." qualifier, [B5] = "NA" raises a runtime error.

I do not understand why. When I try it in a new workbook, there is no problem with [B5] = "NA", even if B5:B6 are merged, as is the case in your file.

-----

in the Excel file, we see that the code is:
If Sh1.Cells(I, 2) = "P-YOY-01" Then Sh2.[B5] = Sh1.Cells(I, 3) Else [B5] = "NA"
If Sh1.Cells(I, 2) = "P-YOY-01" Then Sh2.[B7] = Sh1.Cells(I, 3) Else [B7] = "NA"
which refers to column B.
That is your mistake.

That is certainly __one__ mistake.

But Sh2.[B5] and Sh2.[B7] will still be "NA", in the end.

The reason is faulty logic.

During the loop, when i=2 and Sh1.Cells(I,1) = "P-YOY-01" is True, Sh2.[B5] and Sh2.[B7] are indeed set to Sh1.Cells(I,3) -- or Sh1.Cells(I,2), if you correct that.

But on the next iteration, when i=3, Sh2.[B5] and Sh2.[B7] will be overwritten with "NA" because Sh1.Cells(I,1) = "P-YOY-01" is now False.

I do not know what you are trying to do. But perhaps the following does it.

Option Explicit

Sub Mod2()

Dim Sh1 As Worksheet, Sh2 As Worksheet, I As Long

Set Sh1 = Worksheets("Calcul"): Set Sh2 = Worksheets("Calc")

If Sh2.Range("B2") = "YOY1" Then
    Sh2.[B5] = "NA"
    Sh2.[B7] = "NA"
    For I = 2 To 20
        If Sh1.Cells(I, 1) = "P-YOY-01" Then
            Sh2.[B5] = Sh1.Cells(I, 2)
            Sh2.[B7] = Sh1.Cells(I, 2)
            Exit For
        End If
    Next I
End If

End Sub

And as you said, perhaps simply using VLOOKUP is the better way to go.

 

[Shenzar]

thank you so much

Your code is working fine

It is true that Vlookup is better and simpler, but I wanted to have the mastery of for and understand what happens in the event that this kind of thing happens in the future. loops still drive me crazy even in Python

It's a shame that the else affection crush the if assignment

Thank you for your advice

 

[joeu2004]

It's a shame that the else affection crush the if assignment

To be clear, the problem had nothing to do with the Else statements. I moved them out of the loop for efficiency. The following would work equally well.

If Sh2.Range("B2") = "YOY1" Then
    For I = 2 To 20
        If Sh1.Cells(I, 1) = "P-YOY-01" Then
            Sh2.[B5] = Sh1.Cells(I, 2)
            Sh2.[B7] = Sh1.Cells(I, 2)
            Exit For
        Else
            Sh2.[B5] = "NA"
            Sh2.[B7] = "NA"
        End If
    Next I
End If

It is just unnecessary to assign the same value to Sh2.[B5] and Sh2.[B7] each time the If condition fails.

Just as it unnecessary to test Sh2.[B2] each iteration, assuming its value is not changed as side-effect of the loop. (It does not appear to.)