| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to simplify If / ElseIf statement
Hi
I am using a macro with IF and ElseIF statement to do comparison off the cell's for example '1a If [C5] = [B2] Then [B5].Value = "1b" ElseIf [D5] = [B2] Then [B5].Value = "2a" ElseIf [E5] = [B2] Then [B5].Value = "2b" ElseIf [F5] = [B2] Then [B5].Value = "3a" ElseIf [G5] = [B2] Then [B5].Value = "3b" ElseIf [H5] = [B2] Then [B5].Value = "4a" ElseIf [I5] = [B2] Then [B5].Value = "4b" ElseIf [J5] = [B2] Then [B5].Value = "5a" ElseIf [K5] = [B2] Then [B5].Value = "5b" ElseIf [L5] = [B2] Then [B5].Value = "6a" ElseIf [M5] = [B2] Then [B5].Value = "6b" ElseIf [N5] = [B2] Then [B5].Value = "7a" ElseIf [O5] = [B2] Then [B5].Value = "7b" ElseIf [P5] = [B2] Then [B5].Value = "8a" ElseIf [Q5] = [B2] Then [B5].Value = "8b" ElseIf [R5] = [B2] Then [B5].Value = "9a" ElseIf [S5] = [B2] Then [B5].Value = "9b" ElseIf [T5] = [B2] Then [B5].Value = "10a" ElseIf [U5] = [B2] Then [B5].Value = "10b" End If '1b If [B5] = [C2] Then [C5].Value = "1a" ElseIf [D5] = [C2] Then [C5].Value = "2a" ElseIf [E5] = [C2] Then [C5].Value = "2b" ElseIf [F5] = [C2] Then [C5].Value = "3a" ElseIf [G5] = [C2] Then [C5].Value = "3b" ElseIf [H5] = [C2] Then [C5].Value = "4a" ElseIf [I5] = [C2] Then [C5].Value = "4b" ElseIf [J5] = [C2] Then [C5].Value = "5a" ElseIf [K5] = [C2] Then [C5].Value = "5b" ElseIf [L5] = [C2] Then [C5].Value = "6a" ElseIf [M5] = [C2] Then [C5].Value = "6b" ElseIf [N5] = [C2] Then [C5].Value = "7a" ElseIf [O5] = [C2] Then [C5].Value = "7b" ElseIf [P5] = [C2] Then [C5].Value = "8a" ElseIf [Q5] = [C2] Then [C5].Value = "8b" ElseIf [R5] = [C2] Then [C5].Value = "9a" ElseIf [S5] = [C2] Then [C5].Value = "9b" ElseIf [T5] = [C2] Then [C5].Value = "10a" ElseIf [U5] = [C2] Then [C5].Value = "10b" End If '2a If [B5] = [D2] Then [D5].Value = "1a" ElseIf [C5] = [D2] Then [D5].Value = "1b" ElseIf [E5] = [D2] Then [D5].Value = "2b" ElseIf [F5] = [D2] Then [D5].Value = "3a" ElseIf [G5] = [D2] Then [D5].Value = "3b" ElseIf [H5] = [D2] Then [D5].Value = "4a" ElseIf [I5] = [D2] Then [D5].Value = "4b" ElseIf [J5] = [D2] Then [D5].Value = "5a" ElseIf [K5] = [D2] Then [D5].Value = "5b" ElseIf [L5] = [D2] Then [D5].Value = "6a" ElseIf [M5] = [D2] Then [D5].Value = "6b" ElseIf [N5] = [D2] Then [D5].Value = "7a" ElseIf [O5] = [D2] Then [D5].Value = "7b" ElseIf [P5] = [D2] Then [D5].Value = "8a" ElseIf [Q5] = [D2] Then [D5].Value = "8b" ElseIf [R5] = [D2] Then [D5].Value = "9a" ElseIf [S5] = [D2] Then [D5].Value = "9b" ElseIf [T5] = [D2] Then [D5].Value = "10a" ElseIf [U5] = [D2] Then [D5].Value = "10b" End If '2b If [B5] = [E2] Then [E5].Value = "1a" ElseIf [C5] = [E2] Then [E5].Value = "1b" ElseIf [D5] = [E2] Then [E5].Value = "2a" ElseIf [F5] = [E2] Then [E5].Value = "3a" ElseIf [G5] = [E2] Then [E5].Value = "3b" ElseIf [H5] = [E2] Then [E5].Value = "4a" ElseIf [I5] = [E2] Then [E5].Value = "4b" ElseIf [J5] = [E2] Then [E5].Value = "5a" ElseIf [K5] = [E2] Then [E5].Value = "5b" ElseIf [L5] = [E2] Then [E5].Value = "6a" ElseIf [M5] = [E2] Then [E5].Value = "6b" ElseIf [N5] = [E2] Then [E5].Value = "7a" ElseIf [O5] = [E2] Then [E5].Value = "7b" ElseIf [P5] = [E2] Then [E5].Value = "8a" ElseIf [Q5] = [E2] Then [E5].Value = "8b" ElseIf [R5] = [E2] Then [E5].Value = "9a" ElseIf [S5] = [E2] Then [E5].Value = "9b" ElseIf [T5] = [E2] Then [E5].Value = "10a" ElseIf [U5] = [E2] Then [E5].Value = "10b" End If End Sub but I need to compare cell's from B5 to U5, B11 to U11, B17 to U17, B23 to U23 and so on to B299 to U299 so is there a better way to do that help and advice would be appreciate |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to simplify If / ElseIf statement
How about using a select case construct?
Select Case Cells(2,2) Case is = Cells(5,3): Cells(5,2) = "1b" Case is = Cells(5,4): Cells(5,2) = "2a" '..... etc End Select Then by replacing each cell row/column reference with variables, you can construct a loop to apply this statement for each cell grouping so Dim sourceCol as integer, sourceRow as Long Dim targetCol as integer, targetRow as Long Dim testCol as Integer, testRow as Long Select Case Cells(sourceRow,sourceCol) Case is = Cells(testRow,testCol): Cells(targetRow,targetCol) = "1b" Case is = Cells(testRow,testCol): Cells(targetRow,targetCol) = "2a" '..... etc End Select the loops to control the sequence need to be defined by the ranges you are acting upon -- Cheers Nigel "Ksu" wrote in message ... Hi I am using a macro with IF and ElseIF statement to do comparison off the cell's for example '1a If [C5] = [B2] Then [B5].Value = "1b" ElseIf [D5] = [B2] Then [B5].Value = "2a" ElseIf [E5] = [B2] Then [B5].Value = "2b" ElseIf [F5] = [B2] Then [B5].Value = "3a" ElseIf [G5] = [B2] Then [B5].Value = "3b" ElseIf [H5] = [B2] Then [B5].Value = "4a" ElseIf [I5] = [B2] Then [B5].Value = "4b" ElseIf [J5] = [B2] Then [B5].Value = "5a" ElseIf [K5] = [B2] Then [B5].Value = "5b" ElseIf [L5] = [B2] Then [B5].Value = "6a" ElseIf [M5] = [B2] Then [B5].Value = "6b" ElseIf [N5] = [B2] Then [B5].Value = "7a" ElseIf [O5] = [B2] Then [B5].Value = "7b" ElseIf [P5] = [B2] Then [B5].Value = "8a" ElseIf [Q5] = [B2] Then [B5].Value = "8b" ElseIf [R5] = [B2] Then [B5].Value = "9a" ElseIf [S5] = [B2] Then [B5].Value = "9b" ElseIf [T5] = [B2] Then [B5].Value = "10a" ElseIf [U5] = [B2] Then [B5].Value = "10b" End If '1b If [B5] = [C2] Then [C5].Value = "1a" ElseIf [D5] = [C2] Then [C5].Value = "2a" ElseIf [E5] = [C2] Then [C5].Value = "2b" ElseIf [F5] = [C2] Then [C5].Value = "3a" ElseIf [G5] = [C2] Then [C5].Value = "3b" ElseIf [H5] = [C2] Then [C5].Value = "4a" ElseIf [I5] = [C2] Then [C5].Value = "4b" ElseIf [J5] = [C2] Then [C5].Value = "5a" ElseIf [K5] = [C2] Then [C5].Value = "5b" ElseIf [L5] = [C2] Then [C5].Value = "6a" ElseIf [M5] = [C2] Then [C5].Value = "6b" ElseIf [N5] = [C2] Then [C5].Value = "7a" ElseIf [O5] = [C2] Then [C5].Value = "7b" ElseIf [P5] = [C2] Then [C5].Value = "8a" ElseIf [Q5] = [C2] Then [C5].Value = "8b" ElseIf [R5] = [C2] Then [C5].Value = "9a" ElseIf [S5] = [C2] Then [C5].Value = "9b" ElseIf [T5] = [C2] Then [C5].Value = "10a" ElseIf [U5] = [C2] Then [C5].Value = "10b" End If '2a If [B5] = [D2] Then [D5].Value = "1a" ElseIf [C5] = [D2] Then [D5].Value = "1b" ElseIf [E5] = [D2] Then [D5].Value = "2b" ElseIf [F5] = [D2] Then [D5].Value = "3a" ElseIf [G5] = [D2] Then [D5].Value = "3b" ElseIf [H5] = [D2] Then [D5].Value = "4a" ElseIf [I5] = [D2] Then [D5].Value = "4b" ElseIf [J5] = [D2] Then [D5].Value = "5a" ElseIf [K5] = [D2] Then [D5].Value = "5b" ElseIf [L5] = [D2] Then [D5].Value = "6a" ElseIf [M5] = [D2] Then [D5].Value = "6b" ElseIf [N5] = [D2] Then [D5].Value = "7a" ElseIf [O5] = [D2] Then [D5].Value = "7b" ElseIf [P5] = [D2] Then [D5].Value = "8a" ElseIf [Q5] = [D2] Then [D5].Value = "8b" ElseIf [R5] = [D2] Then [D5].Value = "9a" ElseIf [S5] = [D2] Then [D5].Value = "9b" ElseIf [T5] = [D2] Then [D5].Value = "10a" ElseIf [U5] = [D2] Then [D5].Value = "10b" End If '2b If [B5] = [E2] Then [E5].Value = "1a" ElseIf [C5] = [E2] Then [E5].Value = "1b" ElseIf [D5] = [E2] Then [E5].Value = "2a" ElseIf [F5] = [E2] Then [E5].Value = "3a" ElseIf [G5] = [E2] Then [E5].Value = "3b" ElseIf [H5] = [E2] Then [E5].Value = "4a" ElseIf [I5] = [E2] Then [E5].Value = "4b" ElseIf [J5] = [E2] Then [E5].Value = "5a" ElseIf [K5] = [E2] Then [E5].Value = "5b" ElseIf [L5] = [E2] Then [E5].Value = "6a" ElseIf [M5] = [E2] Then [E5].Value = "6b" ElseIf [N5] = [E2] Then [E5].Value = "7a" ElseIf [O5] = [E2] Then [E5].Value = "7b" ElseIf [P5] = [E2] Then [E5].Value = "8a" ElseIf [Q5] = [E2] Then [E5].Value = "8b" ElseIf [R5] = [E2] Then [E5].Value = "9a" ElseIf [S5] = [E2] Then [E5].Value = "9b" ElseIf [T5] = [E2] Then [E5].Value = "10a" ElseIf [U5] = [E2] Then [E5].Value = "10b" End If End Sub but I need to compare cell's from B5 to U5, B11 to U11, B17 to U17, B23 to U23 and so on to B299 to U299 so is there a better way to do that help and advice would be appreciate |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to simplify If / ElseIf statement
Thanks for help the Select Case statement is quite clear but
I didn't quite understant how use variable because with Select Case statement I would have to Write 200 macros like below with (Makro_1a_1_250 and Makro_1a_251_500 ) e.g Select Case Cells(2, 3), Select Case Cells(2, 4) Select Case Cells(2, 5) Select Case Cells(2, 6) Select Case Cells(2, 7) Select Case Cells(2, 8) Select Case Cells(2, 9) Select Case Cells(2, 10) Select Case Cells(2, 11) Select Case Cells(2, 12) Select Case Cells(2, 13) Select Case Cells(2, 14) Select Case Cells(2, 15) Select Case Cells(2, 16) Select Case Cells(2, 17) Select Case Cells(2, 18) Select Case Cells(2, 19) Select Case Cells(2, 20) Select Case Cells(2, 21) and Select Case Cells(8, 2) to Select Case Cells(8, 21) and Select Case Cells(14, 2) to Select Case Cells(14, 21) like that with 6 row interval to row 299 Sub Makro_1a_1_250() ' ' 1a 1a_250b ' Select Case Cells(2, 2) Case Is = Cells(5, 3): Cells(5, 2) = "1b" Case Is = Cells(5, 4): Cells(5, 2) = "2a" Case Is = Cells(5, 5): Cells(5, 2) = "2b" Case Is = Cells(5, 6): Cells(5, 2) = "3a" Case Is = Cells(5, 7): Cells(5, 2) = "3b" Case Is = Cells(5, 8): Cells(5, 2) = "4a" Case Is = Cells(5, 9): Cells(5, 2) = "4b" Case Is = Cells(5, 10): Cells(5, 2) = "5a" Case Is = Cells(5, 11): Cells(5, 2) = "5b" Case Is = Cells(5, 12): Cells(5, 2) = "6a" Case Is = Cells(5, 13): Cells(5, 2) = "6b" Case Is = Cells(5, 14): Cells(5, 2) = "7a" Case Is = Cells(5, 15): Cells(5, 2) = "7b" Case Is = Cells(5, 16): Cells(5, 2) = "8a" Case Is = Cells(5, 17): Cells(5, 2) = "8b" Case Is = Cells(5, 18): Cells(5, 2) = "9a" Case Is = Cells(5, 19): Cells(5, 2) = "9b" Case Is = Cells(5, 20): Cells(5, 2) = "10a" Case Is = Cells(5, 21): Cells(5, 2) = "10b" ' Case Is = Cells(11, 2): Cells(5, 2) = "11a" Case Is = Cells(11, 3): Cells(5, 2) = "11b" Case Is = Cells(11, 4): Cells(5, 2) = "12a" Case Is = Cells(11, 5): Cells(5, 2) = "12b" Case Is = Cells(11, 6): Cells(5, 2) = "13a" Case Is = Cells(11, 7): Cells(5, 2) = "13b" Case Is = Cells(11, 8): Cells(5, 2) = "14a" Case Is = Cells(11, 9): Cells(5, 2) = "14b" Case Is = Cells(11, 10): Cells(5, 2) = "15a" Case Is = Cells(11, 11): Cells(5, 2) = "15b" Case Is = Cells(11, 12): Cells(5, 2) = "16a" Case Is = Cells(11, 13): Cells(5, 2) = "16b" Case Is = Cells(11, 14): Cells(5, 2) = "17a" Case Is = Cells(11, 15): Cells(5, 2) = "17b" Case Is = Cells(11, 16): Cells(5, 2) = "18a" Case Is = Cells(11, 17): Cells(5, 2) = "18b" Case Is = Cells(11, 18): Cells(5, 2) = "19a" Case Is = Cells(11, 19): Cells(5, 2) = "19b" Case Is = Cells(11, 20): Cells(5, 2) = "20a" Case Is = Cells(11, 21): Cells(5, 2) = "20b" ' Case Is = Cells(17, 2): Cells(5, 2) = "21a" Case Is = Cells(17, 3): Cells(5, 2) = "21b" Case Is = Cells(17, 4): Cells(5, 2) = "22a" Case Is = Cells(17, 5): Cells(5, 2) = "22b" Case Is = Cells(17, 6): Cells(5, 2) = "23a" Case Is = Cells(17, 7): Cells(5, 2) = "23b" Case Is = Cells(17, 8): Cells(5, 2) = "24a" Case Is = Cells(47, 5): Cells(5, 2) = "72b" Case Is = Cells(47, 6): Cells(5, 2) = "73a" Case Is = Cells(47, 7): Cells(5, 2) = "73b" Case Is = Cells(47, 8): Cells(5, 2) = "74a" Case Is = Cells(47, 9): Cells(5, 2) = "74b" Case Is = Cells(47, 10): Cells(5, 2) = "75a" Case Is = Cells(47, 11): Cells(5, 2) = "75b" Case Is = Cells(47, 12): Cells(5, 2) = "76a" Case Is = Cells(299, 17): Cells(5, 2) = "498b" Case Is = Cells(299, 18): Cells(5, 2) = "499a" Case Is = Cells(299, 19): Cells(5, 2) = "499b" Case Is = Cells(299, 20): Cells(5, 2) = "500a" Case Is = Cells(299, 21): Cells(5, 2) = "500b" ' End Select End Sub "Nigel" wrote: How about using a select case construct? Select Case Cells(2,2) Case is = Cells(5,3): Cells(5,2) = "1b" Case is = Cells(5,4): Cells(5,2) = "2a" '..... etc End Select Then by replacing each cell row/column reference with variables, you can construct a loop to apply this statement for each cell grouping so Dim sourceCol as integer, sourceRow as Long Dim targetCol as integer, targetRow as Long Dim testCol as Integer, testRow as Long Select Case Cells(sourceRow,sourceCol) Case is = Cells(testRow,testCol): Cells(targetRow,targetCol) = "1b" Case is = Cells(testRow,testCol): Cells(targetRow,targetCol) = "2a" '..... etc End Select the loops to control the sequence need to be defined by the ranges you are acting upon -- Cheers Nigel "Ksu" wrote in message ... "Nigel" wrote: How about using a select case construct? Select Case Cells(2,2) Case is = Cells(5,3): Cells(5,2) = "1b" Case is = Cells(5,4): Cells(5,2) = "2a" '..... etc End Select Then by replacing each cell row/column reference with variables, you can construct a loop to apply this statement for each cell grouping so Dim sourceCol as integer, sourceRow as Long Dim targetCol as integer, targetRow as Long Dim testCol as Integer, testRow as Long Select Case Cells(sourceRow,sourceCol) Case is = Cells(testRow,testCol): Cells(targetRow,targetCol) = "1b" Case is = Cells(testRow,testCol): Cells(targetRow,targetCol) = "2a" '..... etc End Select the loops to control the sequence need to be defined by the ranges you are acting upon -- Cheers Nigel "Ksu" wrote in message ... Hi I am using a macro with IF and ElseIF statement to do comparison off the cell's for example '1a If [C5] = [B2] Then [B5].Value = "1b" ElseIf [D5] = [B2] Then [B5].Value = "2a" ElseIf [E5] = [B2] Then [B5].Value = "2b" ElseIf [F5] = [B2] Then [B5].Value = "3a" ElseIf [G5] = [B2] Then [B5].Value = "3b" ElseIf [H5] = [B2] Then [B5].Value = "4a" ElseIf [I5] = [B2] Then [B5].Value = "4b" ElseIf [J5] = [B2] Then [B5].Value = "5a" ElseIf [K5] = [B2] Then [B5].Value = "5b" ElseIf [L5] = [B2] Then [B5].Value = "6a" ElseIf [M5] = [B2] Then [B5].Value = "6b" ElseIf [N5] = [B2] Then [B5].Value = "7a" ElseIf [O5] = [B2] Then [B5].Value = "7b" ElseIf [P5] = [B2] Then [B5].Value = "8a" ElseIf [Q5] = [B2] Then [B5].Value = "8b" ElseIf [R5] = [B2] Then [B5].Value = "9a" ElseIf [S5] = [B2] Then [B5].Value = "9b" ElseIf [T5] = [B2] Then [B5].Value = "10a" ElseIf [U5] = [B2] Then [B5].Value = "10b" End If '1b If [B5] = [C2] Then [C5].Value = "1a" ElseIf [D5] = [C2] Then [C5].Value = "2a" ElseIf [E5] = [C2] Then [C5].Value = "2b" ElseIf [F5] = [C2] Then [C5].Value = "3a" ElseIf [G5] = [C2] Then [C5].Value = "3b" ElseIf [H5] = [C2] Then [C5].Value = "4a" ElseIf [I5] = [C2] Then [C5].Value = "4b" ElseIf [J5] = [C2] Then [C5].Value = "5a" ElseIf [K5] = [C2] Then [C5].Value = "5b" ElseIf [L5] = [C2] Then [C5].Value = "6a" ElseIf [M5] = [C2] Then [C5].Value = "6b" ElseIf [N5] = [C2] Then [C5].Value = "7a" ElseIf [O5] = [C2] Then [C5].Value = "7b" ElseIf [P5] = [C2] Then [C5].Value = "8a" ElseIf [Q5] = [C2] Then [C5].Value = "8b" ElseIf [R5] = [C2] Then [C5].Value = "9a" ElseIf [S5] = [C2] Then [C5].Value = "9b" ElseIf [T5] = [C2] Then [C5].Value = "10a" ElseIf [U5] = [C2] Then [C5].Value = "10b" End If '2a If [B5] = [D2] Then [D5].Value = "1a" ElseIf [C5] = [D2] Then [D5].Value = "1b" ElseIf [E5] = [D2] Then [D5].Value = "2b" ElseIf [F5] = [D2] Then [D5].Value = "3a" ElseIf [G5] = [D2] Then [D5].Value = "3b" ElseIf [H5] = [D2] Then [D5].Value = "4a" ElseIf [I5] = [D2] Then [D5].Value = "4b" ElseIf [J5] = [D2] Then [D5].Value = "5a" ElseIf [K5] = [D2] Then [D5].Value = "5b" ElseIf [L5] = [D2] Then [D5].Value = "6a" ElseIf [M5] = [D2] Then [D5].Value = "6b" ElseIf [N5] = [D2] Then [D5].Value = "7a" ElseIf [O5] = [D2] Then [D5].Value = "7b" ElseIf [P5] = [D2] Then [D5].Value = "8a" ElseIf [Q5] = [D2] Then [D5].Value = "8b" ElseIf [R5] = [D2] Then [D5].Value = "9a" ElseIf [S5] = [D2] Then [D5].Value = "9b" ElseIf [T5] = [D2] Then [D5].Value = "10a" ElseIf [U5] = [D2] Then [D5].Value = "10b" End If '2b If [B5] = [E2] Then [E5].Value = "1a" ElseIf [C5] = [E2] Then [E5].Value = "1b" ElseIf [D5] = [E2] Then [E5].Value = "2a" ElseIf [F5] = [E2] Then [E5].Value = "3a" ElseIf [G5] = [E2] Then [E5].Value = "3b" ElseIf [H5] = [E2] Then [E5].Value = "4a" ElseIf [I5] = [E2] Then [E5].Value = "4b" ElseIf [J5] = [E2] Then [E5].Value = "5a" ElseIf [K5] = [E2] Then [E5].Value = "5b" ElseIf [L5] = [E2] Then [E5].Value = "6a" ElseIf [M5] = [E2] Then [E5].Value = "6b" ElseIf [N5] = [E2] Then [E5].Value = "7a" ElseIf [O5] = [E2] Then [E5].Value = "7b" ElseIf [P5] = [E2] Then [E5].Value = "8a" ElseIf [Q5] = [E2] Then [E5].Value = "8b" ElseIf [R5] = [E2] Then [E5].Value = "9a" ElseIf [S5] = [E2] Then [E5].Value = "9b" ElseIf [T5] = [E2] Then [E5].Value = "10a" ElseIf [U5] = [E2] Then [E5].Value = "10b" End If End Sub but I need to compare cell's from B5 to U5, B11 to U11, B17 to U17, B23 to U23 and so on to B299 to U299 so is there a better way to do that help and advice would be appreciate |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to simplify If / ElseIf statement
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| handle error in IF ELSEIF statement | Excel Worksheet Functions | |||
| ElseIf Statement problem | Excel Programming | |||
| If, ElseIf | Excel Programming | |||
| ElseIf | Excel Programming | |||
| If...Elseif...End If | Excel Programming | |||
Linear Mode
