# 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

#### Fluff

##### MrExcel MVP, Moderator
What formula do you have in col Z?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

It is a vlookup

#### Arunachaljois

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

##### MrExcel MVP, Moderator
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

##### Board Regular

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

##### MrExcel MVP, Moderator
Why do you want to run the code again?
You will just get the same result.

#### Arunachaljois

##### Board Regular

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

##### MrExcel MVP, Moderator
Is this what you mean
VBA Code:
``````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

##### Board Regular
Thank you so much for your help
Means a lot for me

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

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