Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How to simplify If / ElseIf statement
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ksu Ksu is offline
external usenet poster
  
Posts: 9
Default How to simplify If / ElseIf statement
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
JNW JNW is offline
external usenet poster
  
Posts: 480
Default How to simplify If / ElseIf statement
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" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Ksu Ksu is offline
external usenet poster
  
Posts: 9
Default How to simplify If / ElseIf statement
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" wrote:

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" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
handle error in IF ELSEIF statement Sabosis Excel Worksheet Functions 1 April 30th 09 05:06 AM
Elseif? ibrokit Excel Worksheet Functions 5 November 25th 08 04:28 PM
How to simplify If / ElseIf statement Ksu Excel Programming 4 October 8th 06 09:26 AM
ElseIf Statement problem Erik K via OfficeKB.com[_2_] Excel Programming 5 December 23rd 05 12:51 PM
if elseif flow23 Excel Discussion (Misc queries) 0 November 14th 05 02:09 PM


All times are GMT +1. The time now is 12:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement