# Finding issue in If elseif else with For each Loop

#### Arunachaljois

##### Board Regular
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

#### Attachments

• 40.8 KB Views: 8

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
VBA Code:
``````   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``````

#### Arunachaljois

##### Board Regular
Hi Fluff,
Thanks for the response #### Fluff

##### MrExcel MVP, Moderator
What error? You never mentioned anything about an error.

#### Arunachaljois

##### Board Regular
Sorry Fluff. It is working fine now.
Thank you so much for your help #### Fluff

##### MrExcel MVP, Moderator
You can also do it without a loop like
VBA Code:
``   Range("Y2:Y" & lr).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],if(rc[-16]=""W100"",""W004"",if(rc[-16]=""W102"",""W005"",rc[-16])))"``

#### Arunachaljois

##### Board Regular
Thank you
And i have one more question
in next column i have filled with success and error
if any cell find error and then i want to run with this code
Example - if Z2 find error and I2 has W100 then i need to change
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

#### Arunachaljois

##### Board Regular
Now i'm using whatever code you have given me
But i'm getting #N/A in Z column
So please tell me how to use code if i'm getting #N/a in Z column with loop

#### Attachments

• 41.5 KB Views: 4

#### Arunachaljois

##### Board Regular
In simple if i get #N/A in column Z then i want to change with code
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