Finding issue in If elseif else with For each Loop

[Arunachaljois]

Finding issue in If elseif else with For each Loop
I have tried this below code but i'm getting in Y column filled with W005. Colud you please help me on this ? i have uploaded a images also.
For Each cell In Range("I2:I" & LR)
If cell.Value = "W100" Then
Range("Y2:Y" & LR).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W004"")"
ElseIf cell.Value = "W102" Then
Range("Y2:Y" & LR).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W005"")"
Else
Range("Y2:Y" & LR).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],RC[-16])"
End If
Next cell

 

[Fluff]

What formula do you have in col Z?

 

[Arunachaljois]

It is a vlookup

 

[Arunachaljois]

It is a vlookup taking base as column Y
Once Vlookup done then i need to run code if col Z find #N/A

 

[Fluff]

Sorry but you are not making any sense, please remember I know nothing about your sheet, or what you are trying to do.
So you need to clearly explain, what you are trying to do.

 

[Arunachaljois]

Column Y has one unique key its a combination of col B,E and I
Then in col Z there is vlookup from another sheet with taking lookup value in col Y
after Vlookup done if any row finds #N/A in col Z then i want to run below code with each row in col Y
For Each cell In Range("I2:I" & LR)
If cell.Value = "W100" Then
Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W004"")"
ElseIf cell.Value = "W102" Then
Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W005"")"
Else
Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],RC[-16])"
End If
Next cell

 

[Fluff]

Why do you want to run the code again?
You will just get the same result.

 

[Arunachaljois]

But i'm still getting #N/A in colum Z
I mean if col Z find #N/A then only i want to run this code thats it
Otherwise not

 

[Fluff]

Is this what you mean

For Each Cell In Range("I2:I" & lr)
   If IsError(Range("Z" & Cell.Row).Value) Then
      If Cell.Value = "W100" Then
         Range("Y" & Cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W004"")"
      ElseIf Cell.Value = "W102" Then
         Range("Y" & Cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W005"")"
      Else
         Range("Y" & Cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],RC[-16])"
      End If
   End If
Next Cell

 

[Arunachaljois]

Thank you so much for your help
Means a lot for me

 

[Fluff]

You're welcome & thanks for the feedback.