How to simplify If / ElseIf statement

K

Ksu

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
 
N

Nigel

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 said:
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
Click to expand...
 
K

Ksu

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 said:
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
Click to expand...

Nigel said:
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 said:
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
Click to expand...
Click to expand...
 
M

Michel Pierron

Hi Ksu,
Something like (by adapting the value of i ):

Dim i&, u&, y&: y = 1
For i = 3 To 21
u = 1
If i Mod 2 = 0 Then y = y + 1: u = 2
If Cells(5, i) = [B2] Then
[b5].Value = y & Choose(u, "b", "a")
Exit For
End If
Next i

MP

Ksu said:
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
Click to expand...
 
K

Ksu

Thanks Michel for your answers but I think that I didn't manage to explane
what
I'am trying to do with my spreadsheet so there is link to mytempdir where
you can look my spreadsheet

and what I try to do with my file is e.g if I write to cell (T41) 21a it
copies to (B17) 70a or if I write to (F161) to 6b it copies to (M5) 263a and
so on ( I
want that is if I write to some cell the value of 1a, 1b . . . 500a,
500b e.g 12a to cell B23 it copies the cell B20 to D11 )
through the whole spreadsheet from B2 to U299
hopefully this will clarify what I'm trying to do

http://www.mytempdir.com/977368


Michel Pierron said:
Hi Ksu,
Something like (by adapting the value of i ):

Dim i&, u&, y&: y = 1
For i = 3 To 21
u = 1
If i Mod 2 = 0 Then y = y + 1: u = 2
If Cells(5, i) = [B2] Then
[b5].Value = y & Choose(u, "b", "a")
Exit For
End If
Next i

MP

Ksu said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to simplify If / ElseIf statement 4
How to simplify macro 6
If Condition 0
Recorded macro selection widened 4
Payment calculation 1
Insert columns and values programmatically with IF statement 9
LOOPING STRUCTURE DESIRED 4 TWO RANGES 2
Rows in macros 1

Top