How to simplify If / ElseIf statement

K

Ksu

Hi


I am using the following If and ElseIf statement to compare cell's.

'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

'3a
If [B5] = [F2] Then
[F5].Value = "1a"
ElseIf [C5] = [F2] Then
[F5].Value = "1b"
ElseIf [D5] = [F2] Then
[F5].Value = "2a"
ElseIf [E5] = [F2] Then
[F5].Value = "2b"
ElseIf [G5] = [F2] Then
[F5].Value = "3b"
ElseIf [H5] = [F2] Then
[F5].Value = "4a"
ElseIf [I5] = [F2] Then
[F5].Value = "4b"
ElseIf [J5] = [F2] Then
[F5].Value = "5a"
ElseIf [K5] = [F2] Then
[F5].Value = "5b"
ElseIf [L5] = [F2] Then
[F5].Value = "6a"
ElseIf [M5] = [F2] Then
[F5].Value = "6b"
ElseIf [N5] = [F2] Then
[F5].Value = "7a"
ElseIf [O5] = [F2] Then
[F5].Value = "7b"
ElseIf [P5] = [F2] Then
[F5].Value = "8a"
ElseIf [Q5] = [F2] Then
[F5].Value = "8b"
ElseIf [R5] = [F2] Then
[F5].Value = "9a"
ElseIf [S5] = [F2] Then
[F5].Value = "9b"
ElseIf [T5] = [F2] Then
[F5].Value = "10a"
ElseIf [U5] = [F2] Then
[F5].Value = "10b"
End If
End Sub

And my problem is that I need to compare cell's B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on all the way to B299 to U299 so is there better /
smarter way to do this.

HELP AND ADVICE'S WOULD BE APPRECIATED
 
J

JNW

It appears by what you have below that you are comparing cells C5 through U5
to B2 only (etc...)

Can you clarify? If it was simply comparing "B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on" this would be a simple task using a For each
statement But your code suggests you are doing something different. Please
clarify.



--
JNW


Ksu said:
Hi


I am using the following If and ElseIf statement to compare cell's.

'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

'3a
If [B5] = [F2] Then
[F5].Value = "1a"
ElseIf [C5] = [F2] Then
[F5].Value = "1b"
ElseIf [D5] = [F2] Then
[F5].Value = "2a"
ElseIf [E5] = [F2] Then
[F5].Value = "2b"
ElseIf [G5] = [F2] Then
[F5].Value = "3b"
ElseIf [H5] = [F2] Then
[F5].Value = "4a"
ElseIf [I5] = [F2] Then
[F5].Value = "4b"
ElseIf [J5] = [F2] Then
[F5].Value = "5a"
ElseIf [K5] = [F2] Then
[F5].Value = "5b"
ElseIf [L5] = [F2] Then
[F5].Value = "6a"
ElseIf [M5] = [F2] Then
[F5].Value = "6b"
ElseIf [N5] = [F2] Then
[F5].Value = "7a"
ElseIf [O5] = [F2] Then
[F5].Value = "7b"
ElseIf [P5] = [F2] Then
[F5].Value = "8a"
ElseIf [Q5] = [F2] Then
[F5].Value = "8b"
ElseIf [R5] = [F2] Then
[F5].Value = "9a"
ElseIf [S5] = [F2] Then
[F5].Value = "9b"
ElseIf [T5] = [F2] Then
[F5].Value = "10a"
ElseIf [U5] = [F2] Then
[F5].Value = "10b"
End If
End Sub

And my problem is that I need to compare cell's B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on all the way to B299 to U299 so is there better /
smarter way to do this.

HELP AND ADVICE'S WOULD BE APPRECIATED
Click to expand...
 
K

Ksu

Here's 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

link to my file

http://www.mytempdir.com/977368


JNW said:
It appears by what you have below that you are comparing cells C5 through U5
to B2 only (etc...)

Can you clarify? If it was simply comparing "B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on" this would be a simple task using a For each
statement But your code suggests you are doing something different. Please
clarify.



--
JNW


Ksu said:
Hi


I am using the following If and ElseIf statement to compare cell's.

'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

'3a
If [B5] = [F2] Then
[F5].Value = "1a"
ElseIf [C5] = [F2] Then
[F5].Value = "1b"
ElseIf [D5] = [F2] Then
[F5].Value = "2a"
ElseIf [E5] = [F2] Then
[F5].Value = "2b"
ElseIf [G5] = [F2] Then
[F5].Value = "3b"
ElseIf [H5] = [F2] Then
[F5].Value = "4a"
ElseIf [I5] = [F2] Then
[F5].Value = "4b"
ElseIf [J5] = [F2] Then
[F5].Value = "5a"
ElseIf [K5] = [F2] Then
[F5].Value = "5b"
ElseIf [L5] = [F2] Then
[F5].Value = "6a"
ElseIf [M5] = [F2] Then
[F5].Value = "6b"
ElseIf [N5] = [F2] Then
[F5].Value = "7a"
ElseIf [O5] = [F2] Then
[F5].Value = "7b"
ElseIf [P5] = [F2] Then
[F5].Value = "8a"
ElseIf [Q5] = [F2] Then
[F5].Value = "8b"
ElseIf [R5] = [F2] Then
[F5].Value = "9a"
ElseIf [S5] = [F2] Then
[F5].Value = "9b"
ElseIf [T5] = [F2] Then
[F5].Value = "10a"
ElseIf [U5] = [F2] Then
[F5].Value = "10b"
End If
End Sub

And my problem is that I need to compare cell's B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on all the way to B299 to U299 so is there better /
smarter way to do this.

HELP AND ADVICE'S WOULD BE APPRECIATED
Click to expand...
Click to expand...
 
J

JNW

I'll have to apologize, but I've seen your other posts and even looked at
your workbook. There seems to be no logic in what you are doing.

I can't grasp the connection between the cells you want to copy to. Unless
there is a pattern of what you want to copy and to where (i.e. copy cell down
2 and over 5) every time then there is little you can do except write code
for every cell on the sheet. This will make the If / ElseIf complex. It
will also make the file size unbearable.

If you don't find another solution you may want to consider redesigning the
workbook.
--
JNW


Ksu said:
Here's 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

link to my file

http://www.mytempdir.com/977368


JNW said:
It appears by what you have below that you are comparing cells C5 through U5
to B2 only (etc...)

Can you clarify? If it was simply comparing "B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on" this would be a simple task using a For each
statement But your code suggests you are doing something different. Please
clarify.



--
JNW


Ksu said:
Hi


I am using the following If and ElseIf statement to compare cell's.

'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

'3a
If [B5] = [F2] Then
[F5].Value = "1a"
ElseIf [C5] = [F2] Then
[F5].Value = "1b"
ElseIf [D5] = [F2] Then
[F5].Value = "2a"
ElseIf [E5] = [F2] Then
[F5].Value = "2b"
ElseIf [G5] = [F2] Then
[F5].Value = "3b"
ElseIf [H5] = [F2] Then
[F5].Value = "4a"
ElseIf [I5] = [F2] Then
[F5].Value = "4b"
ElseIf [J5] = [F2] Then
[F5].Value = "5a"
ElseIf [K5] = [F2] Then
[F5].Value = "5b"
ElseIf [L5] = [F2] Then
[F5].Value = "6a"
ElseIf [M5] = [F2] Then
[F5].Value = "6b"
ElseIf [N5] = [F2] Then
[F5].Value = "7a"
ElseIf [O5] = [F2] Then
[F5].Value = "7b"
ElseIf [P5] = [F2] Then
[F5].Value = "8a"
ElseIf [Q5] = [F2] Then
[F5].Value = "8b"
ElseIf [R5] = [F2] Then
[F5].Value = "9a"
ElseIf [S5] = [F2] Then
[F5].Value = "9b"
ElseIf [T5] = [F2] Then
[F5].Value = "10a"
ElseIf [U5] = [F2] Then
[F5].Value = "10b"
End If
End Sub

And my problem is that I need to compare cell's B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on all the way to B299 to U299 so is there better /
smarter way to do this.

HELP AND ADVICE'S WOULD BE APPRECIATED
Click to expand...
Click to expand...
Click to expand...
 
J

JNW

Ksu-

I've seen your other posts and I can't follow the logic of what you are
attempting to do. The cells you are using in examples keep jumping around
the worksheet without any specific order or justification.

Try giving 4 examples using the following as an example:
Change A1 > copy value of B5 to C20



--
JNW


Ksu said:
Here's 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

link to my file

http://www.mytempdir.com/977368


JNW said:
It appears by what you have below that you are comparing cells C5 through U5
to B2 only (etc...)

Can you clarify? If it was simply comparing "B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on" this would be a simple task using a For each
statement But your code suggests you are doing something different. Please
clarify.



--
JNW


Ksu said:
Hi


I am using the following If and ElseIf statement to compare cell's.

'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

'3a
If [B5] = [F2] Then
[F5].Value = "1a"
ElseIf [C5] = [F2] Then
[F5].Value = "1b"
ElseIf [D5] = [F2] Then
[F5].Value = "2a"
ElseIf [E5] = [F2] Then
[F5].Value = "2b"
ElseIf [G5] = [F2] Then
[F5].Value = "3b"
ElseIf [H5] = [F2] Then
[F5].Value = "4a"
ElseIf [I5] = [F2] Then
[F5].Value = "4b"
ElseIf [J5] = [F2] Then
[F5].Value = "5a"
ElseIf [K5] = [F2] Then
[F5].Value = "5b"
ElseIf [L5] = [F2] Then
[F5].Value = "6a"
ElseIf [M5] = [F2] Then
[F5].Value = "6b"
ElseIf [N5] = [F2] Then
[F5].Value = "7a"
ElseIf [O5] = [F2] Then
[F5].Value = "7b"
ElseIf [P5] = [F2] Then
[F5].Value = "8a"
ElseIf [Q5] = [F2] Then
[F5].Value = "8b"
ElseIf [R5] = [F2] Then
[F5].Value = "9a"
ElseIf [S5] = [F2] Then
[F5].Value = "9b"
ElseIf [T5] = [F2] Then
[F5].Value = "10a"
ElseIf [U5] = [F2] Then
[F5].Value = "10b"
End If
End Sub

And my problem is that I need to compare cell's B5 to U5, B11 to U11, B17
to U17, B23 to U23 and so on all the way to B299 to U299 so is there better /
smarter way to do this.

HELP AND ADVICE'S WOULD BE APPRECIATED
Click to expand...
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
Combine columns from seperate sheets into new sheet- macro improve 4
Insert columns and values programmatically with IF statement 9
Sum serie with condition 5
Rows in macros 1

Top