Need Help with code

[Farisha]

I have written following code and it is working fine. However macro is not writing the Value ( Unit 654 ) of Last and First Case of Lines below.

For example, it is not giving me the value of "56-P1505-13" and "56-P1505-14" in Column 6 of my spreadsheet. 56-MS445-1 and 56-MS445-2 and so on....

Please see the Code below and help me to find out what I am missing.

Thanksin advance for your help.

-----------------------------------------------------------------------

Sub Help1()
FinalRow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 1 To FinalRow
Select Case Cells(i, 4)
'CTP stands for ..........
Case "56-P3223-1", "56-P3231-1", "40-MR200-2", "56-P1505-10", "56-P1505-12", "56-P1505-13" & _
"56-P1505-14", "56-MS409-1", "56-MS409-2", "56-MS441-1", "56-MS442-2", "56-MS445-1" & _
"56-MS445-2", "56-MS446-1", "56-MS446-2", "56-MS447-1", "56-MS447-2", "56-MS448-1" & _
"56-MS448-2", "56-MS482-1", "56-MS482-2", "56-MS486-1", "56-MS486-2", "56-MS487-1" & _
"56-MS489-1", "56-MS489-2", "56-MS490-1", "56-MS490-2", "56-MS491-1", "56-MS492-1" & _
"56-MS492-2", "56-MS493-1", "56-MS494-1", "56-MS495-1", "56-MS495-2", "56-MS498-1" & _
"56-MS500-1", "56-MS501-1", "56-MS501-2", "56-MS504-1", "56-MS505-1", "56-MS505-2" & _
"56-MS506-1", "56-MS507-1", "56-MS507-2", "56-MS508-1", "56-MS509-1", "56-MS510-1" & _
"56-MS511-1", "56-MS600-1", "56-MS600-2", "56-P1505-1", "56-P1505-3", "56-P1505-5" & _
"56-SC492-1", "56-SC492-2", "56-SC493-1", "56-SC493-2", "56-SC494-1", "56-SC494-2" & _
"56-SC495-1", "56-SC495-2", "56-SC496-1", "56-SC496-2", "56-SC497-1", "56-SC497-2" & _
"56-SC524-1", "56-SC524-2", "56-SC526-1", "56-SC526-2", "56-SC527-1", "56-SC527-2" & _
"56-SC528-1", "56-SC528-2", "56-SC530-1", "56-SC530-2", "56-SC531-1", "56-SC531-2" & _
"56-SC533-1", "56-SC533-2", "56-SC534-1", "56-SC534-2", "56-SC536-1", "56-SC547-1" & _
"56-SC547-2", "56-N551-1", "56-P1500-7", "56-P1500-17", "56-P1500-18", "56-P1505-4" & _
"56-P1505-6", "56-P1505-7", "56-P1505-8", "56-P1505-9", "56-P1743-5", "56-P1743-6" & _
"56-P1743-7", "56-P1743-8", "56-P1743-9", "56-P1743-10", "56-P1743-11", "56-P1743-12" & _
"56-P1743-13", "56-P1743-14", "56-P1743-15", "56-P1743-16", "56-P1749-8", "56-P1749-9" & _
"56-P1749-10", "56-P1749-11", "56-P1749-12", "56-P1749-13", "56-P1749-15", "56-P1749-16" & _
"56-P1749-17", "56-OD001-1", "56-OD003-1", "56-OD004-1", "56-OD104-1", "56-OD105-1" & _
"56-OD110-1", "56-OD111-1", "56-OD114-1", "56-OD115-1", "56-P1509-1", "56-P1509-2" & _
"56-P1509-3", "56-P1509-4", "56-P1512-1", "56-P1512-6", "56-P1706-1", "56-P1708-1" & _
"56-P1712-7", "56-P1712-8", "56-P1716-1", "56-P1881-1", "56-OD002-1"
Cells(i, 6) = "Unit 654"

End Select
Next i
End Sub

 

[hiker95]

Farisha,

You are missing some , characters.

At the end of each line of code, after the " and before the &_

'Change this:
Case "56-P3223-1", "56-P3231-1", "40-MR200-2", "56-P1505-10", "56-P1505-12", "56-P1505-13" & _


'To this:
Case "56-P3223-1", "56-P3231-1", "40-MR200-2", "56-P1505-10", "56-P1505-12", "56-P1505-13", & _

 

[Ruddles]

You need to lose all those ampersands otherwise your Case statement is checking "56-P1505-1356-P1505-14","56-MS445-156-MS445-2","56-MS448-156-MS448-2", etc.

Try:-

[SIZE=1]Case "56-P3223-1", "56-P3231-1", "40-MR200-2", "56-P1505-10", "56-P1505-12", "56-P1505-13" & _
"56-P1505-14", "56-MS409-1", "56-MS409-2", "56-MS441-1", "56-MS442-2", "56-MS445-1" _[/SIZE]
[SIZE=1]"56-MS445-2", "56-MS446-1", "56-MS446-2", "56-MS447-1", "56-MS447-2", "56-MS448-1" _[/SIZE]
[SIZE=1]"56-MS448-2", "56-MS482-1", "56-MS482-2", "56-MS486-1", "56-MS486-2", "56-MS487-1" _[/SIZE]
[SIZE=1]"56-MS489-1", "56-MS489-2", "56-MS490-1", "56-MS490-2", "56-MS491-1", "56-MS492-1" _[/SIZE]
[SIZE=1]"56-MS492-2", "56-MS493-1", "56-MS494-1", "56-MS495-1", "56-MS495-2", "56-MS498-1" _[/SIZE]
[SIZE=1]"56-MS500-1", "56-MS501-1", "56-MS501-2", "56-MS504-1", "56-MS505-1", "56-MS505-2" _[/SIZE]
[SIZE=1]"56-MS506-1", "56-MS507-1", "56-MS507-2", "56-MS508-1", "56-MS509-1", "56-MS510-1" _[/SIZE]
[SIZE=1]"56-MS511-1", "56-MS600-1", "56-MS600-2", "56-P1505-1", "56-P1505-3", "56-P1505-5" _[/SIZE]
[SIZE=1]"56-SC492-1", "56-SC492-2", "56-SC493-1", "56-SC493-2", "56-SC494-1", "56-SC494-2" _[/SIZE]
[SIZE=1]"56-SC495-1", "56-SC495-2", "56-SC496-1", "56-SC496-2", "56-SC497-1", "56-SC497-2", _[/SIZE]
[SIZE=1]"56-SC524-1", "56-SC524-2", "56-SC526-1", "56-SC526-2", "56-SC527-1", "56-SC527-2", _[/SIZE]
[SIZE=1]"56-SC528-1", "56-SC528-2", "56-SC530-1", "56-SC530-2", "56-SC531-1", "56-SC531-2" , _[/SIZE]
[SIZE=1]"56-SC533-1", "56-SC533-2", "56-SC534-1", "56-SC534-2", "56-SC536-1", "56-SC547-1", _[/SIZE]
[SIZE=1]"56-SC547-2", "56-N551-1", "56-P1500-7", "56-P1500-17", "56-P1500-18", "56-P1505-4" , _[/SIZE]
[SIZE=1]"56-P1505-6", "56-P1505-7", "56-P1505-8", "56-P1505-9", "56-P1743-5", "56-P1743-6", _[/SIZE]
[SIZE=1]"56-P1743-7", "56-P1743-8", "56-P1743-9", "56-P1743-10", "56-P1743-11", "56-P1743-12", _[/SIZE]
[SIZE=1]"56-P1743-13", "56-P1743-14", "56-P1743-15", "56-P1743-16", "56-P1749-8", "56-P1749-9", _[/SIZE]
[SIZE=1]"56-P1749-10", "56-P1749-11", "56-P1749-12", "56-P1749-13", "56-P1749-15", "56-P1749-16", _[/SIZE]
[SIZE=1]"56-P1749-17", "56-OD001-1", "56-OD003-1", "56-OD004-1", "56-OD104-1", "56-OD105-1", _[/SIZE]
[SIZE=1]"56-OD110-1", "56-OD111-1", "56-OD114-1", "56-OD115-1", "56-P1509-1", "56-P1509-2", _[/SIZE]
[SIZE=1]"56-P1509-3", "56-P1509-4", "56-P1512-1", "56-P1512-6", "56-P1706-1", "56-P1708-1", _[/SIZE]
[SIZE=1]"56-P1712-7", "56-P1712-8", "56-P1716-1", "56-P1881-1", "56-OD002-1"[/SIZE]
[SIZE=1]Cells(i, 6) = "Unit 654"[/SIZE]

 

[Farisha]

Thanks for your reply. However
I tried that but it is giving me A syntex Error

For i = 1 To FinalRow
Select Case Cells(i, 4)
'CTP stands for ..........
Case "56-P3223-1", "56-P3231-1", "40-MR200-2", "56-P1505-10", "56-P1505-12", "56-P1505-13", & _
"56-P1505-14", "56-MS409-1", "56-MS409-2", "56-MS441-1", "56-MS442-2", "56-MS445-1", & _
"56-MS445-2", "56-MS446-1", "56-MS446-2", "56-MS447-1", "56-MS447-2", "56-MS448-1", & _
"56-MS448-2", "56-MS482-1", "56-MS482-2", "56-MS486-1", "56-MS486-2", "56-MS487-1", & _
"56-MS489-1", "56-MS489-2", "56-MS490-1", "56-MS490-2", "56-MS491-1", "56-MS492-1", & _
"56-MS492-2", "56-MS493-1", "56-MS494-1", "56-MS495-1", "56-MS495-2", "56-MS498-1", & _
"56-MS500-1", "56-MS501-1", "56-MS501-2", "56-MS504-1", "56-MS505-1", "56-MS505-2", & _
"56-MS506-1", "56-MS507-1", "56-MS507-2", "56-MS508-1", "56-MS509-1", "56-MS510-1", & _
"56-MS511-1", "56-MS600-1", "56-MS600-2", "56-P1505-1", "56-P1505-3", "56-P1505-5", & _
"56-SC492-1", "56-SC492-2", "56-SC493-1", "56-SC493-2", "56-SC494-1", "56-SC494-2", & _
"56-SC495-1", "56-SC495-2", "56-SC496-1", "56-SC496-2", "56-SC497-1", "56-SC497-2", & _
"56-SC524-1", "56-SC524-2", "56-SC526-1", "56-SC526-2", "56-SC527-1", "56-SC527-2", & _
"56-SC528-1", "56-SC528-2", "56-SC530-1", "56-SC530-2", "56-SC531-1", "56-SC531-2", & _
"56-SC533-1", "56-SC533-2", "56-SC534-1", "56-SC534-2", "56-SC536-1", "56-SC547-1", & _
"56-SC547-2", "56-N551-1", "56-P1500-7", "56-P1500-17", "56-P1500-18", "56-P1505-4", & _
"56-P1505-6", "56-P1505-7", "56-P1505-8", "56-P1505-9", "56-P1743-5", "56-P1743-6", & _
"56-P1743-7", "56-P1743-8", "56-P1743-9", "56-P1743-10", "56-P1743-11", "56-P1743-12", & _
"56-P1743-13", "56-P1743-14", "56-P1743-15", "56-P1743-16", "56-P1749-8", "56-P1749-9", & _
"56-P1749-10", "56-P1749-11", "56-P1749-12", "56-P1749-13", "56-P1749-15", "56-P1749-16", & _
"56-P1749-17", "56-OD001-1", "56-OD003-1", "56-OD004-1", "56-OD104-1", "56-OD105-1", & _
"56-OD110-1", "56-OD111-1", "56-OD114-1", "56-OD115-1", "56-P1509-1", "56-P1509-2", & _
"56-P1509-3", "56-P1509-4", "56-P1512-1", "56-P1512-6", "56-P1706-1", "56-P1708-1", & _
"56-P1712-7", "56-P1712-8", "56-P1716-1", "56-P1881-1", "56-OD002-1"
Cells(i, 6) = "Unit 654"
End Select
Next i
End Sub

 

[Farisha]

Thanks Raddles,

I have removed all those ampersands and it works well. Thanks a lot for both of you for your time.

Farisha

 

[Ruddles]

You understand what the problem was, yes? The ampersands were concatenating some of the values you were checking.

You code was equivalent to:-

Case "56-P3223-1", "56-P3231-1", "40-MR200-2", "56-P1505-10", "56-P1505-12", _
[COLOR=red][B]"56-P1505-13" & "56-P1505-14"[/B][/COLOR], "56-MS409-1", "56-MS409-2", "56-MS441-1", _
"56-MS442-2", [COLOR=red][B]"56-MS445-1" & "56-MS445-2"[/B][/COLOR], "56-MS446-1", "56-MS446-2", _
"56-MS447-1", "56-MS447-2", [COLOR=red][B]"56-MS448-1" & "56-MS448-2"[/B][/COLOR], "56-MS482-1"... etc.

in other words:-

Case "56-P3223-1", "56-P3231-1", "40-MR200-2", "56-P1505-10", "56-P1505-12", _
[COLOR=red][B]"56-P1505-1356-P1505-14"[/B][/COLOR], "56-MS409-1", "56-MS409-2", "56-MS441-1", _
"56-MS442-2", [COLOR=red][B]"56-MS445-156-MS445-2"[/B][/COLOR], "56-MS446-1", "56-MS446-2", _
"56-MS447-1", "56-MS447-2", [COLOR=red][B]"56-MS448-156-MS448-2"[/B][/COLOR], "56-MS482-1"... etc.

 

[Farisha]

Yes, I got it.

It was funny and I could not figure out for at least 3 hours where the problem was, till I releazied that it is last and the very first value of the second line.

Anyway. Thanks a lot.