Finding issue in If elseif else with For each Loop

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
365
Platform
Windows
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

Some videos you may like

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
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
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
Joined
Aug 1, 2020
Messages
57
Office Version
365
Platform
Windows
Hi Fluff,
Thanks for the response :)
Still i'm getting same error. Please help me out on this
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
What error? You never mentioned anything about an error.
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
365
Platform
Windows
Sorry Fluff. It is working fine now.
Thank you so much for your help :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
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
Joined
Aug 1, 2020
Messages
57
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
I'm sorry but I don't understand what your asking.
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
365
Platform
Windows
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

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
365
Platform
Windows
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,964
Messages
5,508,427
Members
408,685
Latest member
kyrendaniel

This Week's Hot Topics

Top