Finding issue in If elseif else with For each Loop

Arunachaljois

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

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

Some videos you may like

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
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. 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
  1. 365
Platform
  1. 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
46,134
Office Version
  1. 365
Platform
  1. Windows
What error? You never mentioned anything about an error.
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry Fluff. It is working fine now.
Thank you so much for your help :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. 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
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
46,134
Office Version
  1. 365
Platform
  1. Windows
I'm sorry but I don't understand what your asking.
 

Arunachaljois

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

  • 2020-08-01_203527.png
    2020-08-01_203527.png
    41.5 KB · Views: 4

Arunachaljois

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

Watch MrExcel Video

Forum statistics

Threads
1,108,722
Messages
5,524,467
Members
409,582
Latest member
riibuildersinc

This Week's Hot Topics

Top