# 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

• 2020-08-01_195537.png
40.8 KB · Views: 8

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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

• 2020-08-01_203527.png
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

Replies
10
Views
110
Replies
3
Views
52
Replies
2
Views
83
Replies
6
Views
47
Replies
1
Views
48