How to simplify macro

Guest · Oct 9, 2006

Hi
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
hopefully this will clarify what I'm trying to do so far I have been able to
get it working with macros like below but I would need to make 250 macros
like that
and now I have 10 macros ready and when I run them it takes approximately 1
min to complete the 10 macro so is there some other way to do this / smarter
/faster

Sub tÃ¤yttÃ¶_1_ja_2pari()

' 1 pari
Dim i&, u&, y&:
y = 0
For i = 2 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")
ElseIf Cells(11, i) = [b2] Then
[b5].Value = y + 10 & Choose(u, "b", "a")
ElseIf Cells(17, i) = [b2] Then
[b5].Value = y + 20 & Choose(u, "b", "a")
ElseIf Cells(23, i) = [b2] Then
[b5].Value = y + 30 & Choose(u, "b", "a")
ElseIf Cells(29, i) = [b2] Then
[b5].Value = y + 40 & Choose(u, "b", "a")
ElseIf Cells(35, i) = [b2] Then
[b5].Value = y + 50 & Choose(u, "b", "a")
ElseIf Cells(41, i) = [b2] Then
[b5].Value = y + 60 & Choose(u, "b", "a")
ElseIf Cells(47, i) = [b2] Then
[b5].Value = y + 70 & Choose(u, "b", "a")
ElseIf Cells(53, i) = [b2] Then
[b5].Value = y + 80 & Choose(u, "b", "a")
ElseIf Cells(59, i) = [b2] Then
[b5].Value = y + 90 & Choose(u, "b", "a")
ElseIf Cells(65, i) = [b2] Then
[b5].Value = y + 100 & Choose(u, "b", "a")
ElseIf Cells(71, i) = [b2] Then
[b5].Value = y + 110 & Choose(u, "b", "a")
ElseIf Cells(77, i) = [b2] Then
[b5].Value = y + 120 & Choose(u, "b", "a")
ElseIf Cells(83, i) = [b2] Then
[b5].Value = y + 130 & Choose(u, "b", "a")
ElseIf Cells(89, i) = [b2] Then
[b5].Value = y + 140 & Choose(u, "b", "a")
ElseIf Cells(95, i) = [b2] Then
[b5].Value = y + 150 & Choose(u, "b", "a")
ElseIf Cells(101, i) = [b2] Then
[b5].Value = y + 160 & Choose(u, "b", "a")
ElseIf Cells(107, i) = [b2] Then
[b5].Value = y + 170 & Choose(u, "b", "a")
ElseIf Cells(113, i) = [b2] Then
[b5].Value = y + 180 & Choose(u, "b", "a")
ElseIf Cells(119, i) = [b2] Then
[b5].Value = y + 190 & Choose(u, "b", "a")
ElseIf Cells(125, i) = [b2] Then
[b5].Value = y + 200 & Choose(u, "b", "a")
ElseIf Cells(131, i) = [b2] Then
[b5].Value = y + 210 & Choose(u, "b", "a")
ElseIf Cells(137, i) = [b2] Then
[b5].Value = y + 220 & Choose(u, "b", "a")
ElseIf Cells(143, i) = [b2] Then
[b5].Value = y + 230 & Choose(u, "b", "a")
ElseIf Cells(149, i) = [b2] Then
[b5].Value = y + 240 & Choose(u, "b", "a")
ElseIf Cells(155, i) = [b2] Then
[b5].Value = y + 250 & Choose(u, "b", "a")
ElseIf Cells(161, i) = [b2] Then
[b5].Value = y + 260 & Choose(u, "b", "a")
ElseIf Cells(167, i) = [b2] Then
[b5].Value = y + 270 & Choose(u, "b", "a")
ElseIf Cells(173, i) = [b2] Then
[b5].Value = y + 280 & Choose(u, "b", "a")
ElseIf Cells(179, i) = [b2] Then
[b5].Value = y + 290 & Choose(u, "b", "a")
ElseIf Cells(185, i) = [b2] Then
[b5].Value = y + 300 & Choose(u, "b", "a")
ElseIf Cells(191, i) = [b2] Then
[b5].Value = y + 310 & Choose(u, "b", "a")
ElseIf Cells(197, i) = [b2] Then
[b5].Value = y + 320 & Choose(u, "b", "a")
ElseIf Cells(203, i) = [b2] Then
[b5].Value = y + 330 & Choose(u, "b", "a")
ElseIf Cells(209, i) = [b2] Then
[b5].Value = y + 340 & Choose(u, "b", "a")
ElseIf Cells(215, i) = [b2] Then
[b5].Value = y + 350 & Choose(u, "b", "a")
ElseIf Cells(221, i) = [b2] Then
[b5].Value = y + 360 & Choose(u, "b", "a")
ElseIf Cells(227, i) = [b2] Then
[b5].Value = y + 370 & Choose(u, "b", "a")
ElseIf Cells(233, i) = [b2] Then
[b5].Value = y + 380 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(245, i) = [b2] Then
[b5].Value = y + 400 & Choose(u, "b", "a")
ElseIf Cells(251, i) = [b2] Then
[b5].Value = y + 410 & Choose(u, "b", "a")
ElseIf Cells(257, i) = [b2] Then
[b5].Value = y + 420 & Choose(u, "b", "a")
ElseIf Cells(263, i) = [b2] Then
[b5].Value = y + 430 & Choose(u, "b", "a")
ElseIf Cells(269, i) = [b2] Then
[b5].Value = y + 440 & Choose(u, "b", "a")
ElseIf Cells(275, i) = [b2] Then
[b5].Value = y + 450 & Choose(u, "b", "a")
ElseIf Cells(281, i) = [b2] Then
[b5].Value = y + 460 & Choose(u, "b", "a")
ElseIf Cells(287, i) = [b2] Then
[b5].Value = y + 470 & Choose(u, "b", "a")
ElseIf Cells(293, i) = [b2] Then
[b5].Value = y + 480 & Choose(u, "b", "a")
ElseIf Cells(299, i) = [b2] Then
[b5].Value = y + 490 & Choose(u, "b", "a")
Exit For
End If
Next i
'
' ----------------------------------------------------
'
Dim ii&, uu&, yy&:
yy = 0
For ii = 2 To 21
uu = 1
If ii Mod 2 = 0 Then yy = yy + 1: uu = 2
If Cells(5, ii) = [c2] Then
[c5].Value = yy & Choose(uu, "b", "a")
ElseIf Cells(11, ii) = [c2] Then
[c5].Value = yy + 10 & Choose(uu, "b", "a")
ElseIf Cells(17, ii) = [c2] Then
[c5].Value = yy + 20 & Choose(uu, "b", "a")
ElseIf Cells(23, ii) = [c2] Then
[c5].Value = yy + 30 & Choose(uu, "b", "a")
ElseIf Cells(29, ii) = [c2] Then
[c5].Value = yy + 40 & Choose(uu, "b", "a")
ElseIf Cells(35, ii) = [c2] Then
[c5].Value = yy + 50 & Choose(uu, "b", "a")
ElseIf Cells(41, ii) = [c2] Then
[c5].Value = yy + 60 & Choose(uu, "b", "a")
ElseIf Cells(47, ii) = [c2] Then
[c5].Value = yy + 70 & Choose(uu, "b", "a")
ElseIf Cells(53, ii) = [c2] Then
[c5].Value = yy + 80 & Choose(uu, "b", "a")
ElseIf Cells(59, ii) = [c2] Then
[c5].Value = yy + 90 & Choose(uu, "b", "a")
ElseIf Cells(65, ii) = [c2] Then
[c5].Value = yy + 100 & Choose(uu, "b", "a")
ElseIf Cells(71, ii) = [c2] Then
[c5].Value = yy + 110 & Choose(uu, "b", "a")
ElseIf Cells(77, ii) = [c2] Then
[c5].Value = yy + 120 & Choose(uu, "b", "a")
ElseIf Cells(83, ii) = [c2] Then
[c5].Value = yy + 130 & Choose(uu, "b", "a")
ElseIf Cells(89, ii) = [c2] Then
[c5].Value = yy + 140 & Choose(uu, "b", "a")
ElseIf Cells(95, ii) = [c2] Then
[c5].Value = yy + 150 & Choose(uu, "b", "a")
ElseIf Cells(101, ii) = [c2] Then
[c5].Value = yy + 160 & Choose(uu, "b", "a")
ElseIf Cells(107, ii) = [c2] Then
[c5].Value = yy + 170 & Choose(uu, "b", "a")
ElseIf Cells(113, ii) = [c2] Then
[c5].Value = yy + 180 & Choose(uu, "b", "a")
ElseIf Cells(119, ii) = [c2] Then
[c5].Value = yy + 190 & Choose(uu, "b", "a")
ElseIf Cells(125, ii) = [c2] Then
[c5].Value = yy + 200 & Choose(uu, "b", "a")
ElseIf Cells(131, ii) = [c2] Then
[c5].Value = yy + 210 & Choose(uu, "b", "a")
ElseIf Cells(137, ii) = [c2] Then
[c5].Value = yy + 220 & Choose(uu, "b", "a")
ElseIf Cells(143, ii) = [c2] Then
[c5].Value = yy + 230 & Choose(uu, "b", "a")
ElseIf Cells(149, ii) = [c2] Then
[c5].Value = yy + 240 & Choose(uu, "b", "a")
ElseIf Cells(155, ii) = [c2] Then
[c5].Value = yy + 250 & Choose(uu, "b", "a")
ElseIf Cells(161, ii) = [c2] Then
[c5].Value = yy + 260 & Choose(uu, "b", "a")
ElseIf Cells(167, ii) = [c2] Then
[c5].Value = yy + 270 & Choose(uu, "b", "a")
ElseIf Cells(173, ii) = [c2] Then
[c5].Value = yy + 280 & Choose(uu, "b", "a")
ElseIf Cells(179, ii) = [c2] Then
[c5].Value = yy + 290 & Choose(uu, "b", "a")
ElseIf Cells(185, ii) = [c2] Then
[c5].Value = yy + 300 & Choose(uu, "b", "a")
ElseIf Cells(191, ii) = [c2] Then
[c5].Value = yy + 310 & Choose(uu, "b", "a")
ElseIf Cells(197, ii) = [c2] Then
[c5].Value = yy + 320 & Choose(uu, "b", "a")
ElseIf Cells(203, ii) = [c2] Then
[c5].Value = yy + 330 & Choose(uu, "b", "a")
ElseIf Cells(209, ii) = [c2] Then
[c5].Value = yy + 340 & Choose(uu, "b", "a")
ElseIf Cells(215, ii) = [c2] Then
[c5].Value = yy + 350 & Choose(uu, "b", "a")
ElseIf Cells(221, ii) = [c2] Then
[c5].Value = yy + 360 & Choose(uu, "b", "a")
ElseIf Cells(227, ii) = [c2] Then
[c5].Value = yy + 370 & Choose(uu, "b", "a")
ElseIf Cells(233, ii) = [c2] Then
[c5].Value = yy + 380 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(245, ii) = [c2] Then
[c5].Value = yy + 400 & Choose(uu, "b", "a")
ElseIf Cells(251, ii) = [c2] Then
[c5].Value = yy + 410 & Choose(uu, "b", "a")
ElseIf Cells(257, ii) = [c2] Then
[c5].Value = yy + 420 & Choose(uu, "b", "a")
ElseIf Cells(263, ii) = [c2] Then
[c5].Value = yy + 430 & Choose(uu, "b", "a")
ElseIf Cells(269, ii) = [c2] Then
[c5].Value = yy + 440 & Choose(uu, "b", "a")
ElseIf Cells(275, ii) = [c2] Then
[c5].Value = yy + 450 & Choose(uu, "b", "a")
ElseIf Cells(281, ii) = [c2] Then
[c5].Value = yy + 460 & Choose(uu, "b", "a")
ElseIf Cells(287, ii) = [c2] Then
[c5].Value = yy + 470 & Choose(uu, "b", "a")
ElseIf Cells(293, ii) = [c2] Then
[c5].Value = yy + 480 & Choose(uu, "b", "a")
ElseIf Cells(299, ii) = [c2] Then
[c5].Value = yy + 490 & Choose(uu, "b", "a")
Exit For
End If
Next ii
'
' ----------------------------------------------------
Dim iii&, uuu&, yyy&:
yyy = 0
For iii = 2 To 21
uuu = 1
If iii Mod 2 = 0 Then yyy = yyy + 1: uuu = 2
If Cells(5, iii) = [d2] Then
[d5].Value = yyy & Choose(uuu, "b", "a")
ElseIf Cells(11, iii) = [d2] Then
[d5].Value = yyy + 10 & Choose(uuu, "b", "a")
ElseIf Cells(17, iii) = [d2] Then
[d5].Value = yyy + 20 & Choose(uuu, "b", "a")
ElseIf Cells(23, iii) = [d2] Then
[d5].Value = yyy + 30 & Choose(uuu, "b", "a")
ElseIf Cells(29, iii) = [d2] Then
[d5].Value = yyy + 40 & Choose(uuu, "b", "a")
ElseIf Cells(35, iii) = [d2] Then
[d5].Value = yyy + 50 & Choose(uuu, "b", "a")
ElseIf Cells(41, iii) = [d2] Then
[d5].Value = yyy + 60 & Choose(uuu, "b", "a")
ElseIf Cells(47, iii) = [d2] Then
[d5].Value = yyy + 70 & Choose(uuu, "b", "a")
ElseIf Cells(53, iii) = [d2] Then
[d5].Value = yyy + 80 & Choose(uuu, "b", "a")
ElseIf Cells(59, iii) = [d2] Then
[d5].Value = yyy + 90 & Choose(uuu, "b", "a")
ElseIf Cells(65, iii) = [d2] Then
[d5].Value = yyy + 100 & Choose(uuu, "b", "a")
ElseIf Cells(71, iii) = [d2] Then
[d5].Value = yyy + 110 & Choose(uuu, "b", "a")
ElseIf Cells(77, iii) = [d2] Then
[d5].Value = yyy + 120 & Choose(uuu, "b", "a")
ElseIf Cells(83, iii) = [d2] Then
[d5].Value = yyy + 130 & Choose(uuu, "b", "a")
ElseIf Cells(89, iii) = [d2] Then
[d5].Value = yyy + 140 & Choose(uuu, "b", "a")
ElseIf Cells(95, iii) = [d2] Then
[d5].Value = yyy + 150 & Choose(uuu, "b", "a")
ElseIf Cells(101, iii) = [d2] Then
[d5].Value = yyy + 160 & Choose(uuu, "b", "a")
ElseIf Cells(107, iii) = [d2] Then
[d5].Value = yyy + 170 & Choose(uuu, "b", "a")
ElseIf Cells(113, iii) = [d2] Then
[d5].Value = yyy + 180 & Choose(uuu, "b", "a")
ElseIf Cells(119, iii) = [d2] Then
[d5].Value = yyy + 190 & Choose(uuu, "b", "a")
ElseIf Cells(125, iii) = [d2] Then
[d5].Value = yyy + 200 & Choose(uuu, "b", "a")
ElseIf Cells(131, iii) = [d2] Then
[d5].Value = yyy + 210 & Choose(uuu, "b", "a")
ElseIf Cells(137, iii) = [d2] Then
[d5].Value = yyy + 220 & Choose(uuu, "b", "a")
ElseIf Cells(143, iii) = [d2] Then
[d5].Value = yyy + 230 & Choose(uuu, "b", "a")
ElseIf Cells(149, iii) = [d2] Then
[d5].Value = yyy + 240 & Choose(uuu, "b", "a")
ElseIf Cells(155, iii) = [d2] Then
[d5].Value = yyy + 250 & Choose(uuu, "b", "a")
ElseIf Cells(161, iii) = [d2] Then
[d5].Value = yyy + 260 & Choose(uuu, "b", "a")
ElseIf Cells(167, iii) = [d2] Then
[d5].Value = yyy + 270 & Choose(uuu, "b", "a")
ElseIf Cells(173, iii) = [d2] Then
[d5].Value = yyy + 280 & Choose(uuu, "b", "a")
ElseIf Cells(179, iii) = [d2] Then
[d5].Value = yyy + 290 & Choose(uuu, "b", "a")
ElseIf Cells(185, iii) = [d2] Then
[d5].Value = yyy + 300 & Choose(uuu, "b", "a")
ElseIf Cells(191, iii) = [d2] Then
[d5].Value = yyy + 310 & Choose(uuu, "b", "a")
ElseIf Cells(197, iii) = [d2] Then
[d5].Value = yyy + 320 & Choose(uuu, "b", "a")
ElseIf Cells(203, iii) = [d2] Then
[d5].Value = yyy + 330 & Choose(uuu, "b", "a")
ElseIf Cells(209, iii) = [d2] Then
[d5].Value = yyy + 340 & Choose(uuu, "b", "a")
ElseIf Cells(215, iii) = [d2] Then
[d5].Value = yyy + 350 & Choose(uuu, "b", "a")
ElseIf Cells(221, iii) = [d2] Then
[d5].Value = yyy + 360 & Choose(uuu, "b", "a")
ElseIf Cells(227, iii) = [d2] Then
[d5].Value = yyy + 370 & Choose(uuu, "b", "a")
ElseIf Cells(233, iii) = [d2] Then
[d5].Value = yyy + 380 & Choose(uuu, "b", "a")
ElseIf Cells(239, iii) = [d2] Then
[d5].Value = yyy + 390 & Choose(uuu, "b", "a")
ElseIf Cells(239, iii) = [d2] Then
[d5].Value = yyy + 390 & Choose(uuu, "b", "a")
ElseIf Cells(245, iii) = [d2] Then
[d5].Value = yyy + 400 & Choose(uuu, "b", "a")
ElseIf Cells(251, iii) = [d2] Then
[d5].Value = yyy + 410 & Choose(uuu, "b", "a")
ElseIf Cells(257, iii) = [d2] Then
[d5].Value = yyy + 420 & Choose(uuu, "b", "a")
ElseIf Cells(263, iii) = [d2] Then
[d5].Value = yyy + 430 & Choose(uuu, "b", "a")
ElseIf Cells(269, iii) = [d2] Then
[d5].Value = yyy + 440 & Choose(uuu, "b", "a")
ElseIf Cells(275, iii) = [d2] Then
[d5].Value = yyy + 450 & Choose(uuu, "b", "a")
ElseIf Cells(281, iii) = [d2] Then
[d5].Value = yyy + 460 & Choose(uuu, "b", "a")
ElseIf Cells(287, iii) = [d2] Then
[d5].Value = yyy + 470 & Choose(uuu, "b", "a")
ElseIf Cells(293, iii) = [d2] Then
[d5].Value = yyy + 480 & Choose(uuu, "b", "a")
ElseIf Cells(299, iii) = [d2] Then
[d5].Value = yyy + 490 & Choose(uuu, "b", "a")
Exit For
End If
Next iii
'
' ----------------------------------------------------
Dim iiii&, uuuu&, yyyy&:
yyyy = 0
For iiii = 2 To 21
uuuu = 1
If iiii Mod 2 = 0 Then yyyy = yyyy + 1: uuuu = 2
If Cells(5, iiii) = [e2] Then
[e5].Value = yyyy & Choose(uuuu, "b", "a")
ElseIf Cells(11, iiii) = [e2] Then
[e5].Value = yyyy + 10 & Choose(uuuu, "b", "a")
ElseIf Cells(17, iiii) = [e2] Then
[e5].Value = yyyy + 20 & Choose(uuuu, "b", "a")
ElseIf Cells(23, iiii) = [e2] Then
[e5].Value = yyyy + 30 & Choose(uuuu, "b", "a")
ElseIf Cells(29, iiii) = [e2] Then
[e5].Value = yyyy + 40 & Choose(uuuu, "b", "a")
ElseIf Cells(35, iiii) = [e2] Then
[e5].Value = yyyy + 50 & Choose(uuuu, "b", "a")
ElseIf Cells(41, iiii) = [e2] Then
[e5].Value = yyyy + 60 & Choose(uuuu, "b", "a")
ElseIf Cells(47, iiii) = [e2] Then
[e5].Value = yyyy + 70 & Choose(uuuu, "b", "a")
ElseIf Cells(53, iiii) = [e2] Then
[e5].Value = yyyy + 80 & Choose(uuuu, "b", "a")
ElseIf Cells(59, iiii) = [e2] Then
[e5].Value = yyyy + 90 & Choose(uuuu, "b", "a")
ElseIf Cells(65, iiii) = [e2] Then
[e5].Value = yyyy + 100 & Choose(uuuu, "b", "a")
ElseIf Cells(71, iiii) = [e2] Then
[e5].Value = yyyy + 110 & Choose(uuuu, "b", "a")
ElseIf Cells(77, iiii) = [e2] Then
[e5].Value = yyyy + 120 & Choose(uuuu, "b", "a")
ElseIf Cells(83, iiii) = [e2] Then
[e5].Value = yyyy + 130 & Choose(uuuu, "b", "a")
ElseIf Cells(89, iiii) = [e2] Then
[e5].Value = yyyy + 140 & Choose(uuuu, "b", "a")
ElseIf Cells(95, iiii) = [e2] Then
[e5].Value = yyyy + 150 & Choose(uuuu, "b", "a")
ElseIf Cells(101, iiii) = [e2] Then
[e5].Value = yyyy + 160 & Choose(uuuu, "b", "a")
ElseIf Cells(107, iiii) = [e2] Then
[e5].Value = yyyy + 170 & Choose(uuuu, "b", "a")
ElseIf Cells(113, iiii) = [e2] Then
[e5].Value = yyyy + 180 & Choose(uuuu, "b", "a")
ElseIf Cells(119, iiii) = [e2] Then
[e5].Value = yyyy + 190 & Choose(uuuu, "b", "a")
ElseIf Cells(125, iiii) = [e2] Then
[e5].Value = yyyy + 200 & Choose(uuuu, "b", "a")
ElseIf Cells(131, iiii) = [e2] Then
[e5].Value = yyyy + 210 & Choose(uuuu, "b", "a")
ElseIf Cells(137, iiii) = [e2] Then
[e5].Value = yyyy + 220 & Choose(uuuu, "b", "a")
ElseIf Cells(143, iiii) = [e2] Then
[e5].Value = yyyy + 230 & Choose(uuuu, "b", "a")
ElseIf Cells(149, iiii) = [e2] Then
[e5].Value = yyyy + 240 & Choose(uuuu, "b", "a")
ElseIf Cells(155, iiii) = [e2] Then
[e5].Value = yyyy + 250 & Choose(uuuu, "b", "a")
ElseIf Cells(161, iiii) = [e2] Then
[e5].Value = yyyy + 260 & Choose(uuuu, "b", "a")
ElseIf Cells(167, iiii) = [e2] Then
[e5].Value = yyyy + 270 & Choose(uuuu, "b", "a")
ElseIf Cells(173, iiii) = [e2] Then
[e5].Value = yyyy + 280 & Choose(uuuu, "b", "a")
ElseIf Cells(179, iiii) = [e2] Then
[e5].Value = yyyy + 290 & Choose(uuuu, "b", "a")
ElseIf Cells(185, iiii) = [e2] Then
[e5].Value = yyyy + 300 & Choose(uuuu, "b", "a")
ElseIf Cells(191, iiii) = [e2] Then
[e5].Value = yyyy + 310 & Choose(uuuu, "b", "a")
ElseIf Cells(197, iiii) = [e2] Then
[e5].Value = yyyy + 320 & Choose(uuuu, "b", "a")
ElseIf Cells(203, iiii) = [e2] Then
[e5].Value = yyyy + 330 & Choose(uuuu, "b", "a")
ElseIf Cells(209, iiii) = [e2] Then
[e5].Value = yyyy + 340 & Choose(uuuu, "b", "a")
ElseIf Cells(215, iiii) = [e2] Then
[e5].Value = yyyy + 350 & Choose(uuuu, "b", "a")
ElseIf Cells(221, iiii) = [e2] Then
[e5].Value = yyyy + 360 & Choose(uuuu, "b", "a")
ElseIf Cells(227, iiii) = [e2] Then
[e5].Value = yyyy + 370 & Choose(uuuu, "b", "a")
ElseIf Cells(233, iiii) = [e2] Then
[e5].Value = yyyy + 380 & Choose(uuuu, "b", "a")
ElseIf Cells(239, iiii) = [e2] Then
[e5].Value = yyyy + 390 & Choose(uuuu, "b", "a")
ElseIf Cells(239, iiii) = [e2] Then
[e5].Value = yyyy + 390 & Choose(uuuu, "b", "a")
ElseIf Cells(245, iiii) = [e2] Then
[e5].Value = yyyy + 400 & Choose(uuuu, "b", "a")
ElseIf Cells(251, iiii) = [e2] Then
[e5].Value = yyyy + 410 & Choose(uuuu, "b", "a")
ElseIf Cells(257, iiii) = [e2] Then
[e5].Value = yyyy + 420 & Choose(uuuu, "b", "a")
ElseIf Cells(263, iiii) = [e2] Then
[e5].Value = yyyy + 430 & Choose(uuuu, "b", "a")
ElseIf Cells(269, iiii) = [e2] Then
[e5].Value = yyyy + 440 & Choose(uuuu, "b", "a")
ElseIf Cells(275, iiii) = [e2] Then
[e5].Value = yyyy + 450 & Choose(uuuu, "b", "a")
ElseIf Cells(281, iiii) = [e2] Then
[e5].Value = yyyy + 460 & Choose(uuuu, "b", "a")
ElseIf Cells(287, iiii) = [e2] Then
[e5].Value = yyyy + 470 & Choose(uuuu, "b", "a")
ElseIf Cells(293, iiii) = [e2] Then
[e5].Value = yyyy + 480 & Choose(uuuu, "b", "a")
ElseIf Cells(299, iiii) = [e2] Then
[e5].Value = yyyy + 490 & Choose(uuuu, "b", "a")
Exit For
End If
Next iiii
End Sub

John Coleman · Oct 9, 2006

Hi

Long block of virtually identical code can usually be placed in loops.
Something like:

Sub simplified()

Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, y As Long
Dim endString As Long
Dim found As Boolean

For i = 2 To 5
y = 0
For j = 2 To 21
If j Mod 2 = 0 Then
y = y + 1
endString = "a"
Else
endString = "b"
End If
k = 0
found = False
Do While Not found And k <= 49
If Cells(5 + 6 * k, j).Value = Cells(2, i).Value Then
Cells(5, i).Value = (y + 10 * k) & endString
found = True
Else
k = k + 1
End If
Loop
Next j
Next i
Application.ScreenUpdating = True
End Sub

If performance continues to be a problem you can consider maybe
transfering all of the data into an array at the begginng of the sub
and processing the array directly.

Hope that helps

-John Coleman

Hi
Here's what I try to do with my file is e.g. if I write to cell (T41) 21ait
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 so far I have been ableto
get it working with macros like below but I would need to make 250 macros
like that
and now I have 10 macros ready and when I run them it takes approximately1
min to complete the 10 macro so is there some other way to do this / smarter
/faster

Sub täyttö_1_ja_2pari()

' 1 pari
Dim i&, u&, y&:
y = 0
For i = 2 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")
ElseIf Cells(11, i) = [b2] Then
[b5].Value = y + 10 & Choose(u, "b", "a")
ElseIf Cells(17, i) = [b2] Then
[b5].Value = y + 20 & Choose(u, "b", "a")
ElseIf Cells(23, i) = [b2] Then
[b5].Value = y + 30 & Choose(u, "b", "a")
ElseIf Cells(29, i) = [b2] Then
[b5].Value = y + 40 & Choose(u, "b", "a")
ElseIf Cells(35, i) = [b2] Then
[b5].Value = y + 50 & Choose(u, "b", "a")
ElseIf Cells(41, i) = [b2] Then
[b5].Value = y + 60 & Choose(u, "b", "a")
ElseIf Cells(47, i) = [b2] Then
[b5].Value = y + 70 & Choose(u, "b", "a")
ElseIf Cells(53, i) = [b2] Then
[b5].Value = y + 80 & Choose(u, "b", "a")
ElseIf Cells(59, i) = [b2] Then
[b5].Value = y + 90 & Choose(u, "b", "a")
ElseIf Cells(65, i) = [b2] Then
[b5].Value = y + 100 & Choose(u, "b", "a")
ElseIf Cells(71, i) = [b2] Then
[b5].Value = y + 110 & Choose(u, "b", "a")
ElseIf Cells(77, i) = [b2] Then
[b5].Value = y + 120 & Choose(u, "b", "a")
ElseIf Cells(83, i) = [b2] Then
[b5].Value = y + 130 & Choose(u, "b", "a")
ElseIf Cells(89, i) = [b2] Then
[b5].Value = y + 140 & Choose(u, "b", "a")
ElseIf Cells(95, i) = [b2] Then
[b5].Value = y + 150 & Choose(u, "b", "a")
ElseIf Cells(101, i) = [b2] Then
[b5].Value = y + 160 & Choose(u, "b", "a")
ElseIf Cells(107, i) = [b2] Then
[b5].Value = y + 170 & Choose(u, "b", "a")
ElseIf Cells(113, i) = [b2] Then
[b5].Value = y + 180 & Choose(u, "b", "a")
ElseIf Cells(119, i) = [b2] Then
[b5].Value = y + 190 & Choose(u, "b", "a")
ElseIf Cells(125, i) = [b2] Then
[b5].Value = y + 200 & Choose(u, "b", "a")
ElseIf Cells(131, i) = [b2] Then
[b5].Value = y + 210 & Choose(u, "b", "a")
ElseIf Cells(137, i) = [b2] Then
[b5].Value = y + 220 & Choose(u, "b", "a")
ElseIf Cells(143, i) = [b2] Then
[b5].Value = y + 230 & Choose(u, "b", "a")
ElseIf Cells(149, i) = [b2] Then
[b5].Value = y + 240 & Choose(u, "b", "a")
ElseIf Cells(155, i) = [b2] Then
[b5].Value = y + 250 & Choose(u, "b", "a")
ElseIf Cells(161, i) = [b2] Then
[b5].Value = y + 260 & Choose(u, "b", "a")
ElseIf Cells(167, i) = [b2] Then
[b5].Value = y + 270 & Choose(u, "b", "a")
ElseIf Cells(173, i) = [b2] Then
[b5].Value = y + 280 & Choose(u, "b", "a")
ElseIf Cells(179, i) = [b2] Then
[b5].Value = y + 290 & Choose(u, "b", "a")
ElseIf Cells(185, i) = [b2] Then
[b5].Value = y + 300 & Choose(u, "b", "a")
ElseIf Cells(191, i) = [b2] Then
[b5].Value = y + 310 & Choose(u, "b", "a")
ElseIf Cells(197, i) = [b2] Then
[b5].Value = y + 320 & Choose(u, "b", "a")
ElseIf Cells(203, i) = [b2] Then
[b5].Value = y + 330 & Choose(u, "b", "a")
ElseIf Cells(209, i) = [b2] Then
[b5].Value = y + 340 & Choose(u, "b", "a")
ElseIf Cells(215, i) = [b2] Then
[b5].Value = y + 350 & Choose(u, "b", "a")
ElseIf Cells(221, i) = [b2] Then
[b5].Value = y + 360 & Choose(u, "b", "a")
ElseIf Cells(227, i) = [b2] Then
[b5].Value = y + 370 & Choose(u, "b", "a")
ElseIf Cells(233, i) = [b2] Then
[b5].Value = y + 380 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(245, i) = [b2] Then
[b5].Value = y + 400 & Choose(u, "b", "a")
ElseIf Cells(251, i) = [b2] Then
[b5].Value = y + 410 & Choose(u, "b", "a")
ElseIf Cells(257, i) = [b2] Then
[b5].Value = y + 420 & Choose(u, "b", "a")
ElseIf Cells(263, i) = [b2] Then
[b5].Value = y + 430 & Choose(u, "b", "a")
ElseIf Cells(269, i) = [b2] Then
[b5].Value = y + 440 & Choose(u, "b", "a")
ElseIf Cells(275, i) = [b2] Then
[b5].Value = y + 450 & Choose(u, "b", "a")
ElseIf Cells(281, i) = [b2] Then
[b5].Value = y + 460 & Choose(u, "b", "a")
ElseIf Cells(287, i) = [b2] Then
[b5].Value = y + 470 & Choose(u, "b", "a")
ElseIf Cells(293, i) = [b2] Then
[b5].Value = y + 480 & Choose(u, "b", "a")
ElseIf Cells(299, i) = [b2] Then
[b5].Value = y + 490 & Choose(u, "b", "a")
Exit For
End If
Next i
'
' ----------------------------------------------------
'
Dim ii&, uu&, yy&:
yy = 0
For ii = 2 To 21
uu = 1
If ii Mod 2 = 0 Then yy = yy + 1: uu = 2
If Cells(5, ii) = [c2] Then
[c5].Value = yy & Choose(uu, "b", "a")
ElseIf Cells(11, ii) = [c2] Then
[c5].Value = yy + 10 & Choose(uu, "b", "a")
ElseIf Cells(17, ii) = [c2] Then
[c5].Value = yy + 20 & Choose(uu, "b", "a")
ElseIf Cells(23, ii) = [c2] Then
[c5].Value = yy + 30 & Choose(uu, "b", "a")
ElseIf Cells(29, ii) = [c2] Then
[c5].Value = yy + 40 & Choose(uu, "b", "a")
ElseIf Cells(35, ii) = [c2] Then
[c5].Value = yy + 50 & Choose(uu, "b", "a")
ElseIf Cells(41, ii) = [c2] Then
[c5].Value = yy + 60 & Choose(uu, "b", "a")
ElseIf Cells(47, ii) = [c2] Then
[c5].Value = yy + 70 & Choose(uu, "b", "a")
ElseIf Cells(53, ii) = [c2] Then
[c5].Value = yy + 80 & Choose(uu, "b", "a")
ElseIf Cells(59, ii) = [c2] Then
[c5].Value = yy + 90 & Choose(uu, "b", "a")
ElseIf Cells(65, ii) = [c2] Then
[c5].Value = yy + 100 & Choose(uu, "b", "a")
ElseIf Cells(71, ii) = [c2] Then
[c5].Value = yy + 110 & Choose(uu, "b", "a")
ElseIf Cells(77, ii) = [c2] Then
[c5].Value = yy + 120 & Choose(uu, "b", "a")
ElseIf Cells(83, ii) = [c2] Then
[c5].Value = yy + 130 & Choose(uu, "b", "a")
ElseIf Cells(89, ii) = [c2] Then
[c5].Value = yy + 140 & Choose(uu, "b", "a")
ElseIf Cells(95, ii) = [c2] Then
[c5].Value = yy + 150 & Choose(uu, "b", "a")
ElseIf Cells(101, ii) = [c2] Then
[c5].Value = yy + 160 & Choose(uu, "b", "a")
ElseIf Cells(107, ii) = [c2] Then
[c5].Value = yy + 170 & Choose(uu, "b", "a")
ElseIf Cells(113, ii) = [c2] Then
[c5].Value = yy + 180 & Choose(uu, "b", "a")
ElseIf Cells(119, ii) = [c2] Then
[c5].Value = yy + 190 & Choose(uu, "b", "a")
ElseIf Cells(125, ii) = [c2] Then
[c5].Value = yy + 200 & Choose(uu, "b", "a")
ElseIf Cells(131, ii) = [c2] Then
[c5].Value = yy + 210 & Choose(uu, "b", "a")
ElseIf Cells(137, ii) = [c2] Then
[c5].Value = yy + 220 & Choose(uu, "b", "a")
ElseIf Cells(143, ii) = [c2] Then
[c5].Value = yy + 230 & Choose(uu, "b", "a")
ElseIf Cells(149, ii) = [c2] Then
[c5].Value = yy + 240 & Choose(uu, "b", "a")
ElseIf Cells(155, ii) = [c2] Then
[c5].Value = yy + 250 & Choose(uu, "b", "a")
ElseIf Cells(161, ii) = [c2] Then
[c5].Value = yy + 260 & Choose(uu, "b", "a")
ElseIf Cells(167, ii) = [c2] Then
[c5].Value = yy + 270 & Choose(uu, "b", "a")
ElseIf Cells(173, ii) = [c2] Then
[c5].Value = yy + 280 & Choose(uu, "b", "a")
ElseIf Cells(179, ii) = [c2] Then
[c5].Value = yy + 290 & Choose(uu, "b", "a")
ElseIf Cells(185, ii) = [c2] Then
[c5].Value = yy + 300 & Choose(uu, "b", "a")
ElseIf Cells(191, ii) = [c2] Then
[c5].Value = yy + 310 & Choose(uu, "b", "a")
ElseIf Cells(197, ii) = [c2] Then
[c5].Value = yy + 320 & Choose(uu, "b", "a")
ElseIf Cells(203, ii) = [c2] Then
[c5].Value = yy + 330 & Choose(uu, "b", "a")
ElseIf Cells(209, ii) = [c2] Then
[c5].Value = yy + 340 & Choose(uu, "b", "a")
ElseIf Cells(215, ii) = [c2] Then
[c5].Value = yy + 350 & Choose(uu, "b", "a")
ElseIf Cells(221, ii) = [c2] Then
[c5].Value = yy + 360 & Choose(uu, "b", "a")
ElseIf Cells(227, ii) = [c2] Then
[c5].Value = yy + 370 & Choose(uu, "b", "a")
ElseIf Cells(233, ii) = [c2] Then
[c5].Value = yy + 380 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(245, ii) = [c2] Then
[c5].Value = yy + 400 & Choose(uu, "b", "a")
ElseIf Cells(251, ii) = [c2] Then
[c5].Value = yy + 410 & Choose(uu, "b", "a")
ElseIf Cells(257, ii) = [c2] Then
[c5].Value = yy + 420 & Choose(uu, "b", "a")
ElseIf Cells(263, ii) = [c2] Then
[c5].Value = yy + 430 & Choose(uu, "b", "a")
ElseIf Cells(269, ii) = [c2] Then
[c5].Value = yy + 440 & Choose(uu, "b", "a")
ElseIf Cells(275, ii) = [c2] Then
[c5].Value = yy + 450 & Choose(uu, "b", "a")
ElseIf Cells(281, ii) = [c2] Then
[c5].Value = yy + 460 & Choose(uu, "b", "a")
ElseIf Cells(287, ii) = [c2] Then
[c5].Value = yy + 470 & Choose(uu, "b", "a")
ElseIf Cells(293, ii) = [c2] Then
[c5].Value = yy + 480 & Choose(uu, "b", "a")
ElseIf Cells(299, ii) = [c2] Then
[c5].Value = yy + 490 & Choose(uu, "b", "a")
Exit For
End If
Next ii
'
' ----------------------------------------------------
Dim iii&, uuu&, yyy&:
yyy = 0
For iii = 2 To 21
uuu = 1
If iii Mod 2 = 0 Then yyy = yyy + 1: uuu = 2
If Cells(5, iii) = [d2] Then
[d5].Value = yyy & Choose(uuu, "b", "a")
ElseIf Cells(11, iii) = [d2] Then
[d5].Value = yyy + 10 & Choose(uuu, "b", "a")
ElseIf Cells(17, iii) = [d2] Then
[d5].Value = yyy + 20 & Choose(uuu, "b", "a")
ElseIf Cells(23, iii) = [d2] Then
[d5].Value = yyy + 30 & Choose(uuu, "b", "a")
ElseIf Cells(29, iii) = [d2] Then
[d5].Value = yyy + 40 & Choose(uuu, "b", "a")
ElseIf Cells(35, iii) = [d2] Then
[d5].Value = yyy + 50 & Choose(uuu, "b", "a")
ElseIf Cells(41, iii) = [d2] Then
[d5].Value = yyy + 60 & Choose(uuu, "b", "a")
ElseIf Cells(47, iii) = [d2] Then
[d5].Value = yyy + 70 & Choose(uuu, "b", "a")
ElseIf Cells(53, iii) = [d2] Then
[d5].Value = yyy + 80 & Choose(uuu, "b", "a")
ElseIf Cells(59, iii) = [d2] Then
[d5].Value = yyy + 90 & Choose(uuu, "b", "a")
ElseIf Cells(65, iii) = [d2] Then
[d5].Value = yyy + 100 & Choose(uuu, "b", "a")
ElseIf Cells(71, iii) = [d2] Then
[d5].Value = yyy + 110 & Choose(uuu, "b", "a")
ElseIf Cells(77, iii) = [d2] Then
[d5].Value = yyy + 120 & Choose(uuu, "b", "a")
ElseIf Cells(83, iii) = [d2] Then
[d5].Value = yyy + 130 & Choose(uuu, "b", "a")
ElseIf Cells(89, iii) = [d2] Then
[d5].Value = yyy + 140 & Choose(uuu, "b", "a")
ElseIf Cells(95, iii) = [d2] Then
[d5].Value = yyy + 150 & Choose(uuu, "b", "a")
ElseIf Cells(101, iii) = [d2] Then
[d5].Value = yyy + 160 & Choose(uuu, "b", "a")
ElseIf Cells(107, iii) = [d2] Then
[d5].Value = yyy + 170 & Choose(uuu, "b", "a")
ElseIf Cells(113, iii) = [d2] Then
[d5].Value = yyy + 180 & Choose(uuu, "b", "a")
ElseIf Cells(119, iii) = [d2] Then
[d5].Value = yyy + 190 & Choose(uuu, "b", "a")
ElseIf Cells(125, iii) = [d2] Then
[d5].Value = yyy + 200 & Choose(uuu, "b", "a")
ElseIf Cells(131, iii) = [d2] Then
[d5].Value = yyy + 210 & Choose(uuu, "b", "a")
ElseIf Cells(137, iii) = [d2] Then
[d5].Value = yyy + 220 & Choose(uuu, "b", "a")
ElseIf Cells(143, iii) = [d2] Then
[d5].Value = yyy + 230 & Choose(uuu, "b", "a")
ElseIf Cells(149, iii) = [d2] Then
[d5].Value = yyy + 240 & Choose(uuu, "b", "a")
ElseIf Cells(155, iii) = [d2] Then
[d5].Value = yyy + 250 & Choose(uuu, "b", "a")
ElseIf Cells(161, iii) = [d2] Then
[d5].Value = yyy + 260 & Choose(uuu, "b", "a")
ElseIf Cells(167, iii) = [d2] Then
[d5].Value = yyy + 270 & Choose(uuu, "b", "a")
ElseIf Cells(173, iii) = [d2] Then
[d5].Value = yyy + 280 & Choose(uuu, "b", "a")
ElseIf Cells(179, iii) = [d2] Then
[d5].Value = yyy + 290 & Choose(uuu, "b", "a")
ElseIf Cells(185, iii) = [d2] Then
[d5].Value = yyy + 300 & Choose(uuu, "b", "a")
ElseIf Cells(191, iii) = [d2] Then
[d5].Value = yyy + 310 & Choose(uuu, "b", "a")
ElseIf Cells(197, iii) = [d2] Then
[d5].Value = yyy + 320 & Choose(uuu, "b", "a")
ElseIf Cells(203, iii) = [d2] Then
[d5].Value = yyy + 330 & Choose(uuu, "b", "a")
ElseIf Cells(209, iii) = [d2] Then
[d5].Value = yyy + 340 & Choose(uuu, "b", "a")
ElseIf Cells(215, iii) = [d2] Then
[d5].Value = yyy + 350 & Choose(uuu, "b", "a")
ElseIf Cells(221, iii) = [d2] Then
[d5].Value = yyy + 360 & Choose(uuu, "b", "a")
ElseIf Cells(227, iii) = [d2] Then
[d5].Value = yyy + 370 & Choose(uuu, "b", "a")
ElseIf Cells(233, iii) = [d2] Then
[d5].Value = yyy + 380 & Choose(uuu, "b", "a")
ElseIf Cells(239, iii) = [d2] Then
[d5].Value = yyy + 390 & Choose(uuu, "b", "a")
ElseIf Cells(239, iii) = [d2] Then
[d5].Value = yyy + 390 & Choose(uuu, "b", "a")
ElseIf Cells(245, iii) = [d2] Then
[d5].Value = yyy + 400 & Choose(uuu, "b", "a")
ElseIf Cells(251, iii) = [d2] Then
[d5].Value = yyy + 410 & Choose(uuu, "b", "a")
ElseIf Cells(257, iii) = [d2] Then
[d5].Value = yyy + 420 & Choose(uuu, "b", "a")
ElseIf Cells(263, iii) = [d2] Then
[d5].Value = yyy + 430 & Choose(uuu, "b", "a")
ElseIf Cells(269, iii) = [d2] Then
[d5].Value = yyy + 440 & Choose(uuu, "b", "a")
ElseIf Cells(275, iii) = [d2] Then
[d5].Value = yyy + 450 & Choose(uuu, "b", "a")
ElseIf Cells(281, iii) = [d2] Then
[d5].Value = yyy + 460 & Choose(uuu, "b", "a")
ElseIf Cells(287, iii) = [d2] Then
[d5].Value = yyy + 470 & Choose(uuu, "b", "a")
ElseIf Cells(293, iii) = [d2] Then
[d5].Value = yyy + 480 & Choose(uuu, "b", "a")
ElseIf Cells(299, iii) = [d2] Then
[d5].Value = yyy + 490 & Choose(uuu, "b", "a")
Exit For
End If
Next iii
'
' ----------------------------------------------------
Dim iiii&, uuuu&, yyyy&:
yyyy = 0
For iiii = 2 To 21
uuuu = 1
If iiii Mod 2 = 0 Then yyyy = yyyy + 1: uuuu = 2
If Cells(5, iiii) = [e2] Then
[e5].Value = yyyy & Choose(uuuu, "b", "a")
ElseIf Cells(11, iiii) = [e2] Then
[e5].Value = yyyy + 10 & Choose(uuuu, "b", "a")
ElseIf Cells(17, iiii) = [e2] Then
[e5].Value = yyyy + 20 & Choose(uuuu, "b", "a")
ElseIf Cells(23, iiii) = [e2] Then
[e5].Value = yyyy + 30 & Choose(uuuu, "b", "a")
ElseIf Cells(29, iiii) = [e2] Then
[e5].Value = yyyy + 40 & Choose(uuuu, "b", "a")
ElseIf Cells(35, iiii) = [e2] Then
[e5].Value = yyyy + 50 & Choose(uuuu, "b", "a")
ElseIf Cells(41, iiii) = [e2] Then
[e5].Value = yyyy + 60 & Choose(uuuu, "b", "a")
ElseIf Cells(47, iiii) = [e2] Then
[e5].Value = yyyy + 70 & Choose(uuuu, "b", "a")
ElseIf Cells(53, iiii) = [e2] Then
[e5].Value = yyyy + 80 & Choose(uuuu, "b", "a")
ElseIf Cells(59, iiii) = [e2] Then
[e5].Value = yyyy + 90 & Choose(uuuu, "b", "a")
ElseIf Cells(65, iiii) = [e2] Then
[e5].Value = yyyy + 100 & Choose(uuuu, "b", "a")
ElseIf Cells(71, iiii) = [e2] Then
[e5].Value = yyyy + 110 & Choose(uuuu, "b", "a")
ElseIf Cells(77, iiii) = [e2] Then
[e5].Value = yyyy + 120 & Choose(uuuu, "b", "a")
ElseIf Cells(83, iiii) = [e2] Then
[e5].Value = yyyy + 130 & Choose(uuuu, "b", "a")
ElseIf Cells(89, iiii) = [e2] Then
[e5].Value = yyyy + 140 & Choose(uuuu, "b", "a")
ElseIf Cells(95, iiii) = [e2] Then
[e5].Value = yyyy + 150 & Choose(uuuu, "b", "a")
ElseIf Cells(101, iiii) = [e2] Then
[e5].Value = yyyy + 160 & Choose(uuuu, "b", "a")
ElseIf Cells(107, iiii) = [e2] Then
[e5].Value = yyyy + 170 & Choose(uuuu, "b", "a")
ElseIf Cells(113, iiii) = [e2] Then
[e5].Value = yyyy + 180 & Choose(uuuu, "b", "a")
ElseIf Cells(119, iiii) = [e2] Then
[e5].Value = yyyy + 190 & Choose(uuuu, "b", "a")
ElseIf Cells(125, iiii) = [e2] Then
[e5].Value = yyyy + 200 & Choose(uuuu, "b", "a")
ElseIf Cells(131, iiii) = [e2] Then
[e5].Value = yyyy + 210 & Choose(uuuu, "b", "a")
ElseIf Cells(137, iiii) = [e2] Then
[e5].Value = yyyy + 220 & Choose(uuuu, "b", "a")
ElseIf Cells(143, iiii) = [e2] Then
[e5].Value = yyyy + 230 & Choose(uuuu, "b", "a")
ElseIf Cells(149, iiii) = [e2] Then
[e5].Value = yyyy + 240 & Choose(uuuu, "b", "a")
ElseIf Cells(155, iiii) = [e2] Then
[e5].Value = yyyy + 250 & Choose(uuuu, "b", "a")
ElseIf Cells(161, iiii) = [e2] Then
[e5].Value = yyyy + 260 & Choose(uuuu, "b", "a")
ElseIf Cells(167, iiii) = [e2] Then
[e5].Value = yyyy + 270 & Choose(uuuu, "b", "a")
ElseIf Cells(173, iiii) = [e2] Then
[e5].Value = yyyy + 280 & Choose(uuuu, "b", "a")
ElseIf Cells(179, iiii) = [e2] Then
[e5].Value = yyyy + 290 & Choose(uuuu, "b", "a")
ElseIf Cells(185, iiii) = [e2] Then
[e5].Value = yyyy + 300 & Choose(uuuu, "b", "a")
ElseIf Cells(191, iiii) = [e2] Then
[e5].Value = yyyy + 310 & Choose(uuuu, "b", "a")
ElseIf Cells(197, iiii) = [e2] Then
[e5].Value = yyyy + 320 & Choose(uuuu, "b", "a")
ElseIf Cells(203, iiii) = [e2] Then
[e5].Value = yyyy + 330 & Choose(uuuu, "b", "a")
ElseIf Cells(209, iiii) = [e2] Then
[e5].Value = yyyy + 340 & Choose(uuuu, "b", "a")
ElseIf Cells(215, iiii) = [e2] Then
[e5].Value = yyyy + 350 & Choose(uuuu, "b", "a")
ElseIf Cells(221, iiii) = [e2] Then
[e5].Value = yyyy + 360 & Choose(uuuu, "b", "a")
ElseIf Cells(227, iiii) = [e2] Then
[e5].Value = yyyy + 370 & Choose(uuuu, "b", "a")
ElseIf Cells(233, iiii) = [e2] Then
[e5].Value = yyyy + 380 & Choose(uuuu, "b", "a")
ElseIf Cells(239, iiii) = [e2] Then
[e5].Value = yyyy + 390 & Choose(uuuu, "b", "a")
ElseIf Cells(239, iiii) = [e2] Then
[e5].Value = yyyy + 390 & Choose(uuuu, "b", "a")
ElseIf Cells(245, iiii) = [e2] Then
[e5].Value = yyyy + 400 & Choose(uuuu, "b", "a")
ElseIf Cells(251, iiii) = [e2] Then
[e5].Value = yyyy + 410 & Choose(uuuu, "b", "a")
ElseIf Cells(257, iiii) = [e2] Then
[e5].Value = yyyy + 420 & Choose(uuuu, "b", "a")
ElseIf Cells(263, iiii) = [e2] Then
[e5].Value = yyyy + 430 & Choose(uuuu, "b", "a")
ElseIf Cells(269, iiii) = [e2] Then
[e5].Value = yyyy + 440 & Choose(uuuu, "b", "a")
ElseIf Cells(275, iiii) = [e2] Then
[e5].Value = yyyy + 450 & Choose(uuuu, "b", "a")
ElseIf Cells(281, iiii) = [e2] Then
[e5].Value = yyyy + 460 & Choose(uuuu, "b", "a")
ElseIf Cells(287, iiii) = [e2] Then
[e5].Value = yyyy + 470 & Choose(uuuu, "b", "a")
ElseIf Cells(293, iiii) = [e2] Then
[e5].Value = yyyy + 480 & Choose(uuuu, "b", "a")
ElseIf Cells(299, iiii) = [e2] Then
[e5].Value = yyyy + 490 & Choose(uuuu, "b", "a")
Exit For
End If
Next iiii
End Sub

Michel Pierron · Oct 9, 2006

Hi Ksu;
Test with:

Sub täyttö_1_ja_2pari()
Dim i&, u&, y&, x&
For x = 5 To 299 Step 6
For i = 2 To 21
u = 1
If i Mod 2 = 0 Then y = y + 1: u = 2
If Cells(x, i) = [b2] Then
[b5].Value = y & Choose(u, "b", "a")
Exit Sub
End If
Next i
Next x
End Sub

MP

Ksu said:
Hi
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
hopefully this will clarify what I'm trying to do so far I have been able
to
get it working with macros like below but I would need to make 250 macros
like that
and now I have 10 macros ready and when I run them it takes approximately
1
min to complete the 10 macro so is there some other way to do this /
smarter
/faster

Sub täyttö_1_ja_2pari()

' 1 pari
Dim i&, u&, y&:
y = 0
For i = 2 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")
ElseIf Cells(11, i) = [b2] Then
[b5].Value = y + 10 & Choose(u, "b", "a")
ElseIf Cells(17, i) = [b2] Then
[b5].Value = y + 20 & Choose(u, "b", "a")
ElseIf Cells(23, i) = [b2] Then
[b5].Value = y + 30 & Choose(u, "b", "a")
ElseIf Cells(29, i) = [b2] Then
[b5].Value = y + 40 & Choose(u, "b", "a")
ElseIf Cells(35, i) = [b2] Then
[b5].Value = y + 50 & Choose(u, "b", "a")
ElseIf Cells(41, i) = [b2] Then
[b5].Value = y + 60 & Choose(u, "b", "a")
ElseIf Cells(47, i) = [b2] Then
[b5].Value = y + 70 & Choose(u, "b", "a")
ElseIf Cells(53, i) = [b2] Then
[b5].Value = y + 80 & Choose(u, "b", "a")
ElseIf Cells(59, i) = [b2] Then
[b5].Value = y + 90 & Choose(u, "b", "a")
ElseIf Cells(65, i) = [b2] Then
[b5].Value = y + 100 & Choose(u, "b", "a")
ElseIf Cells(71, i) = [b2] Then
[b5].Value = y + 110 & Choose(u, "b", "a")
ElseIf Cells(77, i) = [b2] Then
[b5].Value = y + 120 & Choose(u, "b", "a")
ElseIf Cells(83, i) = [b2] Then
[b5].Value = y + 130 & Choose(u, "b", "a")
ElseIf Cells(89, i) = [b2] Then
[b5].Value = y + 140 & Choose(u, "b", "a")
ElseIf Cells(95, i) = [b2] Then
[b5].Value = y + 150 & Choose(u, "b", "a")
ElseIf Cells(101, i) = [b2] Then
[b5].Value = y + 160 & Choose(u, "b", "a")
ElseIf Cells(107, i) = [b2] Then
[b5].Value = y + 170 & Choose(u, "b", "a")
ElseIf Cells(113, i) = [b2] Then
[b5].Value = y + 180 & Choose(u, "b", "a")
ElseIf Cells(119, i) = [b2] Then
[b5].Value = y + 190 & Choose(u, "b", "a")
ElseIf Cells(125, i) = [b2] Then
[b5].Value = y + 200 & Choose(u, "b", "a")
ElseIf Cells(131, i) = [b2] Then
[b5].Value = y + 210 & Choose(u, "b", "a")
ElseIf Cells(137, i) = [b2] Then
[b5].Value = y + 220 & Choose(u, "b", "a")
ElseIf Cells(143, i) = [b2] Then
[b5].Value = y + 230 & Choose(u, "b", "a")
ElseIf Cells(149, i) = [b2] Then
[b5].Value = y + 240 & Choose(u, "b", "a")
ElseIf Cells(155, i) = [b2] Then
[b5].Value = y + 250 & Choose(u, "b", "a")
ElseIf Cells(161, i) = [b2] Then
[b5].Value = y + 260 & Choose(u, "b", "a")
ElseIf Cells(167, i) = [b2] Then
[b5].Value = y + 270 & Choose(u, "b", "a")
ElseIf Cells(173, i) = [b2] Then
[b5].Value = y + 280 & Choose(u, "b", "a")
ElseIf Cells(179, i) = [b2] Then
[b5].Value = y + 290 & Choose(u, "b", "a")
ElseIf Cells(185, i) = [b2] Then
[b5].Value = y + 300 & Choose(u, "b", "a")
ElseIf Cells(191, i) = [b2] Then
[b5].Value = y + 310 & Choose(u, "b", "a")
ElseIf Cells(197, i) = [b2] Then
[b5].Value = y + 320 & Choose(u, "b", "a")
ElseIf Cells(203, i) = [b2] Then
[b5].Value = y + 330 & Choose(u, "b", "a")
ElseIf Cells(209, i) = [b2] Then
[b5].Value = y + 340 & Choose(u, "b", "a")
ElseIf Cells(215, i) = [b2] Then
[b5].Value = y + 350 & Choose(u, "b", "a")
ElseIf Cells(221, i) = [b2] Then
[b5].Value = y + 360 & Choose(u, "b", "a")
ElseIf Cells(227, i) = [b2] Then
[b5].Value = y + 370 & Choose(u, "b", "a")
ElseIf Cells(233, i) = [b2] Then
[b5].Value = y + 380 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(245, i) = [b2] Then
[b5].Value = y + 400 & Choose(u, "b", "a")
ElseIf Cells(251, i) = [b2] Then
[b5].Value = y + 410 & Choose(u, "b", "a")
ElseIf Cells(257, i) = [b2] Then
[b5].Value = y + 420 & Choose(u, "b", "a")
ElseIf Cells(263, i) = [b2] Then
[b5].Value = y + 430 & Choose(u, "b", "a")
ElseIf Cells(269, i) = [b2] Then
[b5].Value = y + 440 & Choose(u, "b", "a")
ElseIf Cells(275, i) = [b2] Then
[b5].Value = y + 450 & Choose(u, "b", "a")
ElseIf Cells(281, i) = [b2] Then
[b5].Value = y + 460 & Choose(u, "b", "a")
ElseIf Cells(287, i) = [b2] Then
[b5].Value = y + 470 & Choose(u, "b", "a")
ElseIf Cells(293, i) = [b2] Then
[b5].Value = y + 480 & Choose(u, "b", "a")
ElseIf Cells(299, i) = [b2] Then
[b5].Value = y + 490 & Choose(u, "b", "a")
Exit For
End If
Next i
'
' ----------------------------------------------------
'
Dim ii&, uu&, yy&:
yy = 0
For ii = 2 To 21
uu = 1
If ii Mod 2 = 0 Then yy = yy + 1: uu = 2
If Cells(5, ii) = [c2] Then
[c5].Value = yy & Choose(uu, "b", "a")
ElseIf Cells(11, ii) = [c2] Then
[c5].Value = yy + 10 & Choose(uu, "b", "a")
ElseIf Cells(17, ii) = [c2] Then
[c5].Value = yy + 20 & Choose(uu, "b", "a")
ElseIf Cells(23, ii) = [c2] Then
[c5].Value = yy + 30 & Choose(uu, "b", "a")
ElseIf Cells(29, ii) = [c2] Then
[c5].Value = yy + 40 & Choose(uu, "b", "a")
ElseIf Cells(35, ii) = [c2] Then
[c5].Value = yy + 50 & Choose(uu, "b", "a")
ElseIf Cells(41, ii) = [c2] Then
[c5].Value = yy + 60 & Choose(uu, "b", "a")
ElseIf Cells(47, ii) = [c2] Then
[c5].Value = yy + 70 & Choose(uu, "b", "a")
ElseIf Cells(53, ii) = [c2] Then
[c5].Value = yy + 80 & Choose(uu, "b", "a")
ElseIf Cells(59, ii) = [c2] Then
[c5].Value = yy + 90 & Choose(uu, "b", "a")
ElseIf Cells(65, ii) = [c2] Then
[c5].Value = yy + 100 & Choose(uu, "b", "a")
ElseIf Cells(71, ii) = [c2] Then
[c5].Value = yy + 110 & Choose(uu, "b", "a")
ElseIf Cells(77, ii) = [c2] Then
[c5].Value = yy + 120 & Choose(uu, "b", "a")
ElseIf Cells(83, ii) = [c2] Then
[c5].Value = yy + 130 & Choose(uu, "b", "a")
ElseIf Cells(89, ii) = [c2] Then
[c5].Value = yy + 140 & Choose(uu, "b", "a")
ElseIf Cells(95, ii) = [c2] Then
[c5].Value = yy + 150 & Choose(uu, "b", "a")
ElseIf Cells(101, ii) = [c2] Then
[c5].Value = yy + 160 & Choose(uu, "b", "a")
ElseIf Cells(107, ii) = [c2] Then
[c5].Value = yy + 170 & Choose(uu, "b", "a")
ElseIf Cells(113, ii) = [c2] Then
[c5].Value = yy + 180 & Choose(uu, "b", "a")
ElseIf Cells(119, ii) = [c2] Then
[c5].Value = yy + 190 & Choose(uu, "b", "a")
ElseIf Cells(125, ii) = [c2] Then
[c5].Value = yy + 200 & Choose(uu, "b", "a")
ElseIf Cells(131, ii) = [c2] Then
[c5].Value = yy + 210 & Choose(uu, "b", "a")
ElseIf Cells(137, ii) = [c2] Then
[c5].Value = yy + 220 & Choose(uu, "b", "a")
ElseIf Cells(143, ii) = [c2] Then
[c5].Value = yy + 230 & Choose(uu, "b", "a")
ElseIf Cells(149, ii) = [c2] Then
[c5].Value = yy + 240 & Choose(uu, "b", "a")
ElseIf Cells(155, ii) = [c2] Then
[c5].Value = yy + 250 & Choose(uu, "b", "a")
ElseIf Cells(161, ii) = [c2] Then
[c5].Value = yy + 260 & Choose(uu, "b", "a")
ElseIf Cells(167, ii) = [c2] Then
[c5].Value = yy + 270 & Choose(uu, "b", "a")
ElseIf Cells(173, ii) = [c2] Then
[c5].Value = yy + 280 & Choose(uu, "b", "a")
ElseIf Cells(179, ii) = [c2] Then
[c5].Value = yy + 290 & Choose(uu, "b", "a")
ElseIf Cells(185, ii) = [c2] Then
[c5].Value = yy + 300 & Choose(uu, "b", "a")
ElseIf Cells(191, ii) = [c2] Then
[c5].Value = yy + 310 & Choose(uu, "b", "a")
ElseIf Cells(197, ii) = [c2] Then
[c5].Value = yy + 320 & Choose(uu, "b", "a")
ElseIf Cells(203, ii) = [c2] Then
[c5].Value = yy + 330 & Choose(uu, "b", "a")
ElseIf Cells(209, ii) = [c2] Then
[c5].Value = yy + 340 & Choose(uu, "b", "a")
ElseIf Cells(215, ii) = [c2] Then
[c5].Value = yy + 350 & Choose(uu, "b", "a")
ElseIf Cells(221, ii) = [c2] Then
[c5].Value = yy + 360 & Choose(uu, "b", "a")
ElseIf Cells(227, ii) = [c2] Then
[c5].Value = yy + 370 & Choose(uu, "b", "a")
ElseIf Cells(233, ii) = [c2] Then
[c5].Value = yy + 380 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(245, ii) = [c2] Then
[c5].Value = yy + 400 & Choose(uu, "b", "a")
ElseIf Cells(251, ii) = [c2] Then
[c5].Value = yy + 410 & Choose(uu, "b", "a")
ElseIf Cells(257, ii) = [c2] Then
[c5].Value = yy + 420 & Choose(uu, "b", "a")
ElseIf Cells(263, ii) = [c2] Then
[c5].Value = yy + 430 & Choose(uu, "b", "a")
ElseIf Cells(269, ii) = [c2] Then
[c5].Value = yy + 440 & Choose(uu, "b", "a")
ElseIf Cells(275, ii) = [c2] Then
[c5].Value = yy + 450 & Choose(uu, "b", "a")
ElseIf Cells(281, ii) = [c2] Then
[c5].Value = yy + 460 & Choose(uu, "b", "a")
ElseIf Cells(287, ii) = [c2] Then
[c5].Value = yy + 470 & Choose(uu, "b", "a")
ElseIf Cells(293, ii) = [c2] Then
[c5].Value = yy + 480 & Choose(uu, "b", "a")
ElseIf Cells(299, ii) = [c2] Then
[c5].Value = yy + 490 & Choose(uu, "b", "a")
Exit For
End If
Next ii
'
' ----------------------------------------------------
Dim iii&, uuu&, yyy&:
yyy = 0
For iii = 2 To 21
uuu = 1
If iii Mod 2 = 0 Then yyy = yyy + 1: uuu = 2
If Cells(5, iii) = [d2] Then
[d5].Value = yyy & Choose(uuu, "b", "a")
ElseIf Cells(11, iii) = [d2] Then
[d5].Value = yyy + 10 & Choose(uuu, "b", "a")
ElseIf Cells(17, iii) = [d2] Then
[d5].Value = yyy + 20 & Choose(uuu, "b", "a")
ElseIf Cells(23, iii) = [d2] Then
[d5].Value = yyy + 30 & Choose(uuu, "b", "a")
ElseIf Cells(29, iii) = [d2] Then
[d5].Value = yyy + 40 & Choose(uuu, "b", "a")
ElseIf Cells(35, iii) = [d2] Then
[d5].Value = yyy + 50 & Choose(uuu, "b", "a")
ElseIf Cells(41, iii) = [d2] Then
[d5].Value = yyy + 60 & Choose(uuu, "b", "a")
ElseIf Cells(47, iii) = [d2] Then
[d5].Value = yyy + 70 & Choose(uuu, "b", "a")
ElseIf Cells(53, iii) = [d2] Then
[d5].Value = yyy + 80 & Choose(uuu, "b", "a")
ElseIf Cells(59, iii) = [d2] Then
[d5].Value = yyy + 90 & Choose(uuu, "b", "a")
ElseIf Cells(65, iii) = [d2] Then
[d5].Value = yyy + 100 & Choose(uuu, "b", "a")
ElseIf Cells(71, iii) = [d2] Then
[d5].Value = yyy + 110 & Choose(uuu, "b", "a")
ElseIf Cells(77, iii) = [d2] Then
[d5].Value = yyy + 120 & Choose(uuu, "b", "a")
ElseIf Cells(83, iii) = [d2] Then
[d5].Value = yyy + 130 & Choose(uuu, "b", "a")
ElseIf Cells(89, iii) = [d2] Then
[d5].Value = yyy + 140 & Choose(uuu, "b", "a")
ElseIf Cells(95, iii) = [d2] Then
[d5].Value = yyy + 150 & Choose(uuu, "b", "a")
ElseIf Cells(101, iii) = [d2] Then
[d5].Value = yyy + 160 & Choose(uuu, "b", "a")
ElseIf Cells(107, iii) = [d2] Then
[d5].Value = yyy + 170 & Choose(uuu, "b", "a")
ElseIf Cells(113, iii) = [d2] Then
[d5].Value = yyy + 180 & Choose(uuu, "b", "a")
ElseIf Cells(119, iii) = [d2] Then
[d5].Value = yyy + 190 & Choose(uuu, "b", "a")
ElseIf Cells(125, iii) = [d2] Then
[d5].Value = yyy + 200 & Choose(uuu, "b", "a")
ElseIf Cells(131, iii) = [d2] Then
[d5].Value = yyy + 210 & Choose(uuu, "b", "a")
ElseIf Cells(137, iii) = [d2] Then
[d5].Value = yyy + 220 & Choose(uuu, "b", "a")
ElseIf Cells(143, iii) = [d2] Then
[d5].Value = yyy + 230 & Choose(uuu, "b", "a")
ElseIf Cells(149, iii) = [d2] Then
[d5].Value = yyy + 240 & Choose(uuu, "b", "a")
ElseIf Cells(155, iii) = [d2] Then
[d5].Value = yyy + 250 & Choose(uuu, "b", "a")
ElseIf Cells(161, iii) = [d2] Then
[d5].Value = yyy + 260 & Choose(uuu, "b", "a")
ElseIf Cells(167, iii) = [d2] Then
[d5].Value = yyy + 270 & Choose(uuu, "b", "a")
ElseIf Cells(173, iii) = [d2] Then
[d5].Value = yyy + 280 & Choose(uuu, "b", "a")
ElseIf Cells(179, iii) = [d2] Then
[d5].Value = yyy + 290 & Choose(uuu, "b", "a")
ElseIf Cells(185, iii) = [d2] Then
[d5].Value = yyy + 300 & Choose(uuu, "b", "a")
ElseIf Cells(191, iii) = [d2] Then
[d5].Value = yyy + 310 & Choose(uuu, "b", "a")
ElseIf Cells(197, iii) = [d2] Then
[d5].Value = yyy + 320 & Choose(uuu, "b", "a")
ElseIf Cells(203, iii) = [d2] Then
[d5].Value = yyy + 330 & Choose(uuu, "b", "a")
ElseIf Cells(209, iii) = [d2] Then
[d5].Value = yyy + 340 & Choose(uuu, "b", "a")
ElseIf Cells(215, iii) = [d2] Then
[d5].Value = yyy + 350 & Choose(uuu, "b", "a")
ElseIf Cells(221, iii) = [d2] Then
[d5].Value = yyy + 360 & Choose(uuu, "b", "a")
ElseIf Cells(227, iii) = [d2] Then
[d5].Value = yyy + 370 & Choose(uuu, "b", "a")
ElseIf Cells(233, iii) = [d2] Then
[d5].Value = yyy + 380 & Choose(uuu, "b", "a")
ElseIf Cells(239, iii) = [d2] Then
[d5].Value = yyy + 390 & Choose(uuu, "b", "a")
ElseIf Cells(239, iii) = [d2] Then
[d5].Value = yyy + 390 & Choose(uuu, "b", "a")
ElseIf Cells(245, iii) = [d2] Then
[d5].Value = yyy + 400 & Choose(uuu, "b", "a")
ElseIf Cells(251, iii) = [d2] Then
[d5].Value = yyy + 410 & Choose(uuu, "b", "a")
ElseIf Cells(257, iii) = [d2] Then
[d5].Value = yyy + 420 & Choose(uuu, "b", "a")
ElseIf Cells(263, iii) = [d2] Then
[d5].Value = yyy + 430 & Choose(uuu, "b", "a")
ElseIf Cells(269, iii) = [d2] Then
[d5].Value = yyy + 440 & Choose(uuu, "b", "a")
ElseIf Cells(275, iii) = [d2] Then
[d5].Value = yyy + 450 & Choose(uuu, "b", "a")
ElseIf Cells(281, iii) = [d2] Then
[d5].Value = yyy + 460 & Choose(uuu, "b", "a")
ElseIf Cells(287, iii) = [d2] Then
[d5].Value = yyy + 470 & Choose(uuu, "b", "a")
ElseIf Cells(293, iii) = [d2] Then
[d5].Value = yyy + 480 & Choose(uuu, "b", "a")
ElseIf Cells(299, iii) = [d2] Then
[d5].Value = yyy + 490 & Choose(uuu, "b", "a")
Exit For
End If
Next iii
'
' ----------------------------------------------------
Dim iiii&, uuuu&, yyyy&:
yyyy = 0
For iiii = 2 To 21
uuuu = 1
If iiii Mod 2 = 0 Then yyyy = yyyy + 1: uuuu = 2
If Cells(5, iiii) = [e2] Then
[e5].Value = yyyy & Choose(uuuu, "b", "a")
ElseIf Cells(11, iiii) = [e2] Then
[e5].Value = yyyy + 10 & Choose(uuuu, "b", "a")
ElseIf Cells(17, iiii) = [e2] Then
[e5].Value = yyyy + 20 & Choose(uuuu, "b", "a")
ElseIf Cells(23, iiii) = [e2] Then
[e5].Value = yyyy + 30 & Choose(uuuu, "b", "a")
ElseIf Cells(29, iiii) = [e2] Then
[e5].Value = yyyy + 40 & Choose(uuuu, "b", "a")
ElseIf Cells(35, iiii) = [e2] Then
[e5].Value = yyyy + 50 & Choose(uuuu, "b", "a")
ElseIf Cells(41, iiii) = [e2] Then
[e5].Value = yyyy + 60 & Choose(uuuu, "b", "a")
ElseIf Cells(47, iiii) = [e2] Then
[e5].Value = yyyy + 70 & Choose(uuuu, "b", "a")
ElseIf Cells(53, iiii) = [e2] Then
[e5].Value = yyyy + 80 & Choose(uuuu, "b", "a")
ElseIf Cells(59, iiii) = [e2] Then
[e5].Value = yyyy + 90 & Choose(uuuu, "b", "a")
ElseIf Cells(65, iiii) = [e2] Then
[e5].Value = yyyy + 100 & Choose(uuuu, "b", "a")
ElseIf Cells(71, iiii) = [e2] Then
[e5].Value = yyyy + 110 & Choose(uuuu, "b", "a")
ElseIf Cells(77, iiii) = [e2] Then
[e5].Value = yyyy + 120 & Choose(uuuu, "b", "a")
ElseIf Cells(83, iiii) = [e2] Then
[e5].Value = yyyy + 130 & Choose(uuuu, "b", "a")
ElseIf Cells(89, iiii) = [e2] Then
[e5].Value = yyyy + 140 & Choose(uuuu, "b", "a")
ElseIf Cells(95, iiii) = [e2] Then
[e5].Value = yyyy + 150 & Choose(uuuu, "b", "a")
ElseIf Cells(101, iiii) = [e2] Then
[e5].Value = yyyy + 160 & Choose(uuuu, "b", "a")
ElseIf Cells(107, iiii) = [e2] Then
[e5].Value = yyyy + 170 & Choose(uuuu, "b", "a")
ElseIf Cells(113, iiii) = [e2] Then
[e5].Value = yyyy + 180 & Choose(uuuu, "b", "a")
ElseIf Cells(119, iiii) = [e2] Then
[e5].Value = yyyy + 190 & Choose(uuuu, "b", "a")
ElseIf Cells(125, iiii) = [e2] Then
[e5].Value = yyyy + 200 & Choose(uuuu, "b", "a")
ElseIf Cells(131, iiii) = [e2] Then
[e5].Value = yyyy + 210 & Choose(uuuu, "b", "a")
ElseIf Cells(137, iiii) = [e2] Then
[e5].Value = yyyy + 220 & Choose(uuuu, "b", "a")
ElseIf Cells(143, iiii) = [e2] Then
[e5].Value = yyyy + 230 & Choose(uuuu, "b", "a")
ElseIf Cells(149, iiii) = [e2] Then
[e5].Value = yyyy + 240 & Choose(uuuu, "b", "a")
ElseIf Cells(155, iiii) = [e2] Then
[e5].Value = yyyy + 250 & Choose(uuuu, "b", "a")
ElseIf Cells(161, iiii) = [e2] Then
[e5].Value = yyyy + 260 & Choose(uuuu, "b", "a")
ElseIf Cells(167, iiii) = [e2] Then
[e5].Value = yyyy + 270 & Choose(uuuu, "b", "a")
ElseIf Cells(173, iiii) = [e2] Then
[e5].Value = yyyy + 280 & Choose(uuuu, "b", "a")
ElseIf Cells(179, iiii) = [e2] Then
[e5].Value = yyyy + 290 & Choose(uuuu, "b", "a")
ElseIf Cells(185, iiii) = [e2] Then
[e5].Value = yyyy + 300 & Choose(uuuu, "b", "a")
ElseIf Cells(191, iiii) = [e2] Then
[e5].Value = yyyy + 310 & Choose(uuuu, "b", "a")
ElseIf Cells(197, iiii) = [e2] Then
[e5].Value = yyyy + 320 & Choose(uuuu, "b", "a")
ElseIf Cells(203, iiii) = [e2] Then
[e5].Value = yyyy + 330 & Choose(uuuu, "b", "a")
ElseIf Cells(209, iiii) = [e2] Then
[e5].Value = yyyy + 340 & Choose(uuuu, "b", "a")
ElseIf Cells(215, iiii) = [e2] Then
[e5].Value = yyyy + 350 & Choose(uuuu, "b", "a")
ElseIf Cells(221, iiii) = [e2] Then
[e5].Value = yyyy + 360 & Choose(uuuu, "b", "a")
ElseIf Cells(227, iiii) = [e2] Then
[e5].Value = yyyy + 370 & Choose(uuuu, "b", "a")
ElseIf Cells(233, iiii) = [e2] Then
[e5].Value = yyyy + 380 & Choose(uuuu, "b", "a")
ElseIf Cells(239, iiii) = [e2] Then
[e5].Value = yyyy + 390 & Choose(uuuu, "b", "a")
ElseIf Cells(239, iiii) = [e2] Then
[e5].Value = yyyy + 390 & Choose(uuuu, "b", "a")
ElseIf Cells(245, iiii) = [e2] Then
[e5].Value = yyyy + 400 & Choose(uuuu, "b", "a")
ElseIf Cells(251, iiii) = [e2] Then
[e5].Value = yyyy + 410 & Choose(uuuu, "b", "a")
ElseIf Cells(257, iiii) = [e2] Then
[e5].Value = yyyy + 420 & Choose(uuuu, "b", "a")
ElseIf Cells(263, iiii) = [e2] Then
[e5].Value = yyyy + 430 & Choose(uuuu, "b", "a")
ElseIf Cells(269, iiii) = [e2] Then
[e5].Value = yyyy + 440 & Choose(uuuu, "b", "a")
ElseIf Cells(275, iiii) = [e2] Then
[e5].Value = yyyy + 450 & Choose(uuuu, "b", "a")
ElseIf Cells(281, iiii) = [e2] Then
[e5].Value = yyyy + 460 & Choose(uuuu, "b", "a")
ElseIf Cells(287, iiii) = [e2] Then
[e5].Value = yyyy + 470 & Choose(uuuu, "b", "a")
ElseIf Cells(293, iiii) = [e2] Then
[e5].Value = yyyy + 480 & Choose(uuuu, "b", "a")
ElseIf Cells(299, iiii) = [e2] Then
[e5].Value = yyyy + 490 & Choose(uuuu, "b", "a")
Exit For
End If
Next iiii
End Sub

Guest · Oct 9, 2006

Hi
And thanks John for your answer but I didn't quite understand the code and
when I try to use it I get Ru-time error 13 Type mismatch (Error 13)

and there is link to my file if I would clarify what I am trying to do
http://www.mytempdir.com/980693

John Coleman said:
Hi

Long block of virtually identical code can usually be placed in loops.
Something like:

Sub simplified()

Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, y As Long
Dim endString As Long
Dim found As Boolean

For i = 2 To 5
y = 0
For j = 2 To 21
If j Mod 2 = 0 Then
y = y + 1
endString = "a"
Else
endString = "b"
End If
k = 0
found = False
Do While Not found And k <= 49
If Cells(5 + 6 * k, j).Value = Cells(2, i).Value Then
Cells(5, i).Value = (y + 10 * k) & endString
found = True
Else
k = k + 1
End If
Loop
Next j
Next i
Application.ScreenUpdating = True
End Sub

If performance continues to be a problem you can consider maybe
transfering all of the data into an array at the begginng of the sub
and processing the array directly.

Hope that helps

-John Coleman

Hi
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
hopefully this will clarify what I'm trying to do so far I have been able to
get it working with macros like below but I would need to make 250 macros
like that
and now I have 10 macros ready and when I run them it takes approximately 1
min to complete the 10 macro so is there some other way to do this / smarter
/faster

Sub tÃ¤yttÃ¶_1_ja_2pari()

' 1 pari
Dim i&, u&, y&:
y = 0
For i = 2 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")
ElseIf Cells(11, i) = [b2] Then
[b5].Value = y + 10 & Choose(u, "b", "a")
ElseIf Cells(17, i) = [b2] Then
[b5].Value = y + 20 & Choose(u, "b", "a")
ElseIf Cells(23, i) = [b2] Then
[b5].Value = y + 30 & Choose(u, "b", "a")
ElseIf Cells(29, i) = [b2] Then
[b5].Value = y + 40 & Choose(u, "b", "a")
ElseIf Cells(35, i) = [b2] Then
[b5].Value = y + 50 & Choose(u, "b", "a")
ElseIf Cells(41, i) = [b2] Then
[b5].Value = y + 60 & Choose(u, "b", "a")
ElseIf Cells(47, i) = [b2] Then
[b5].Value = y + 70 & Choose(u, "b", "a")
ElseIf Cells(53, i) = [b2] Then
[b5].Value = y + 80 & Choose(u, "b", "a")
ElseIf Cells(59, i) = [b2] Then
[b5].Value = y + 90 & Choose(u, "b", "a")
ElseIf Cells(65, i) = [b2] Then
[b5].Value = y + 100 & Choose(u, "b", "a")
ElseIf Cells(71, i) = [b2] Then
[b5].Value = y + 110 & Choose(u, "b", "a")
ElseIf Cells(77, i) = [b2] Then
[b5].Value = y + 120 & Choose(u, "b", "a")
ElseIf Cells(83, i) = [b2] Then
[b5].Value = y + 130 & Choose(u, "b", "a")
ElseIf Cells(89, i) = [b2] Then
[b5].Value = y + 140 & Choose(u, "b", "a")
ElseIf Cells(95, i) = [b2] Then
[b5].Value = y + 150 & Choose(u, "b", "a")
ElseIf Cells(101, i) = [b2] Then
[b5].Value = y + 160 & Choose(u, "b", "a")
ElseIf Cells(107, i) = [b2] Then
[b5].Value = y + 170 & Choose(u, "b", "a")
ElseIf Cells(113, i) = [b2] Then
[b5].Value = y + 180 & Choose(u, "b", "a")
ElseIf Cells(119, i) = [b2] Then
[b5].Value = y + 190 & Choose(u, "b", "a")
ElseIf Cells(125, i) = [b2] Then
[b5].Value = y + 200 & Choose(u, "b", "a")
ElseIf Cells(131, i) = [b2] Then
[b5].Value = y + 210 & Choose(u, "b", "a")
ElseIf Cells(137, i) = [b2] Then
[b5].Value = y + 220 & Choose(u, "b", "a")
ElseIf Cells(143, i) = [b2] Then
[b5].Value = y + 230 & Choose(u, "b", "a")
ElseIf Cells(149, i) = [b2] Then
[b5].Value = y + 240 & Choose(u, "b", "a")
ElseIf Cells(155, i) = [b2] Then
[b5].Value = y + 250 & Choose(u, "b", "a")
ElseIf Cells(161, i) = [b2] Then
[b5].Value = y + 260 & Choose(u, "b", "a")
ElseIf Cells(167, i) = [b2] Then
[b5].Value = y + 270 & Choose(u, "b", "a")
ElseIf Cells(173, i) = [b2] Then
[b5].Value = y + 280 & Choose(u, "b", "a")
ElseIf Cells(179, i) = [b2] Then
[b5].Value = y + 290 & Choose(u, "b", "a")
ElseIf Cells(185, i) = [b2] Then
[b5].Value = y + 300 & Choose(u, "b", "a")
ElseIf Cells(191, i) = [b2] Then
[b5].Value = y + 310 & Choose(u, "b", "a")
ElseIf Cells(197, i) = [b2] Then
[b5].Value = y + 320 & Choose(u, "b", "a")
ElseIf Cells(203, i) = [b2] Then
[b5].Value = y + 330 & Choose(u, "b", "a")
ElseIf Cells(209, i) = [b2] Then
[b5].Value = y + 340 & Choose(u, "b", "a")
ElseIf Cells(215, i) = [b2] Then
[b5].Value = y + 350 & Choose(u, "b", "a")
ElseIf Cells(221, i) = [b2] Then
[b5].Value = y + 360 & Choose(u, "b", "a")
ElseIf Cells(227, i) = [b2] Then
[b5].Value = y + 370 & Choose(u, "b", "a")
ElseIf Cells(233, i) = [b2] Then
[b5].Value = y + 380 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(245, i) = [b2] Then
[b5].Value = y + 400 & Choose(u, "b", "a")
ElseIf Cells(251, i) = [b2] Then
[b5].Value = y + 410 & Choose(u, "b", "a")
ElseIf Cells(257, i) = [b2] Then
[b5].Value = y + 420 & Choose(u, "b", "a")
ElseIf Cells(263, i) = [b2] Then
[b5].Value = y + 430 & Choose(u, "b", "a")
ElseIf Cells(269, i) = [b2] Then
[b5].Value = y + 440 & Choose(u, "b", "a")
ElseIf Cells(275, i) = [b2] Then
[b5].Value = y + 450 & Choose(u, "b", "a")
ElseIf Cells(281, i) = [b2] Then
[b5].Value = y + 460 & Choose(u, "b", "a")
ElseIf Cells(287, i) = [b2] Then
[b5].Value = y + 470 & Choose(u, "b", "a")
ElseIf Cells(293, i) = [b2] Then
[b5].Value = y + 480 & Choose(u, "b", "a")
ElseIf Cells(299, i) = [b2] Then
[b5].Value = y + 490 & Choose(u, "b", "a")
Exit For
End If
Next i
'
' ----------------------------------------------------
'
Dim ii&, uu&, yy&:
yy = 0
For ii = 2 To 21
uu = 1
If ii Mod 2 = 0 Then yy = yy + 1: uu = 2
If Cells(5, ii) = [c2] Then
[c5].Value = yy & Choose(uu, "b", "a")
ElseIf Cells(11, ii) = [c2] Then
[c5].Value = yy + 10 & Choose(uu, "b", "a")
ElseIf Cells(17, ii) = [c2] Then
[c5].Value = yy + 20 & Choose(uu, "b", "a")
ElseIf Cells(23, ii) = [c2] Then
[c5].Value = yy + 30 & Choose(uu, "b", "a")
ElseIf Cells(29, ii) = [c2] Then
[c5].Value = yy + 40 & Choose(uu, "b", "a")
ElseIf Cells(35, ii) = [c2] Then
[c5].Value = yy + 50 & Choose(uu, "b", "a")
ElseIf Cells(41, ii) = [c2] Then
[c5].Value = yy + 60 & Choose(uu, "b", "a")
ElseIf Cells(47, ii) = [c2] Then
[c5].Value = yy + 70 & Choose(uu, "b", "a")
ElseIf Cells(53, ii) = [c2] Then
[c5].Value = yy + 80 & Choose(uu, "b", "a")
ElseIf Cells(59, ii) = [c2] Then
[c5].Value = yy + 90 & Choose(uu, "b", "a")
ElseIf Cells(65, ii) = [c2] Then
[c5].Value = yy + 100 & Choose(uu, "b", "a")
ElseIf Cells(71, ii) = [c2] Then
[c5].Value = yy + 110 & Choose(uu, "b", "a")
ElseIf Cells(77, ii) = [c2] Then
[c5].Value = yy + 120 & Choose(uu, "b", "a")
ElseIf Cells(83, ii) = [c2] Then
[c5].Value = yy + 130 & Choose(uu, "b", "a")
ElseIf Cells(89, ii) = [c2] Then
[c5].Value = yy + 140 & Choose(uu, "b", "a")
ElseIf Cells(95, ii) = [c2] Then
[c5].Value = yy + 150 & Choose(uu, "b", "a")
ElseIf Cells(101, ii) = [c2] Then
[c5].Value = yy + 160 & Choose(uu, "b", "a")
ElseIf Cells(107, ii) = [c2] Then
[c5].Value = yy + 170 & Choose(uu, "b", "a")
ElseIf Cells(113, ii) = [c2] Then
[c5].Value = yy + 180 & Choose(uu, "b", "a")
ElseIf Cells(119, ii) = [c2] Then
[c5].Value = yy + 190 & Choose(uu, "b", "a")
ElseIf Cells(125, ii) = [c2] Then
[c5].Value = yy + 200 & Choose(uu, "b", "a")
ElseIf Cells(131, ii) = [c2] Then
[c5].Value = yy + 210 & Choose(uu, "b", "a")
ElseIf Cells(137, ii) = [c2] Then
[c5].Value = yy + 220 & Choose(uu, "b", "a")
ElseIf Cells(143, ii) = [c2] Then
[c5].Value = yy + 230 & Choose(uu, "b", "a")
ElseIf Cells(149, ii) = [c2] Then
[c5].Value = yy + 240 & Choose(uu, "b", "a")
ElseIf Cells(155, ii) = [c2] Then
[c5].Value = yy + 250 & Choose(uu, "b", "a")
ElseIf Cells(161, ii) = [c2] Then
[c5].Value = yy + 260 & Choose(uu, "b", "a")
ElseIf Cells(167, ii) = [c2] Then
[c5].Value = yy + 270 & Choose(uu, "b", "a")
ElseIf Cells(173, ii) = [c2] Then
[c5].Value = yy + 280 & Choose(uu, "b", "a")
ElseIf Cells(179, ii) = [c2] Then
[c5].Value = yy + 290 & Choose(uu, "b", "a")
ElseIf Cells(185, ii) = [c2] Then
[c5].Value = yy + 300 & Choose(uu, "b", "a")
ElseIf Cells(191, ii) = [c2] Then
[c5].Value = yy + 310 & Choose(uu, "b", "a")
ElseIf Cells(197, ii) = [c2] Then
[c5].Value = yy + 320 & Choose(uu, "b", "a")
ElseIf Cells(203, ii) = [c2] Then
[c5].Value = yy + 330 & Choose(uu, "b", "a")
ElseIf Cells(209, ii) = [c2] Then
[c5].Value = yy + 340 & Choose(uu, "b", "a")
ElseIf Cells(215, ii) = [c2] Then
[c5].Value = yy + 350 & Choose(uu, "b", "a")
ElseIf Cells(221, ii) = [c2] Then
[c5].Value = yy + 360 & Choose(uu, "b", "a")
ElseIf Cells(227, ii) = [c2] Then
[c5].Value = yy + 370 & Choose(uu, "b", "a")
ElseIf Cells(233, ii) = [c2] Then
[c5].Value = yy + 380 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(245, ii) = [c2] Then
[c5].Value = yy + 400 & Choose(uu, "b", "a")
ElseIf Cells(251, ii) = [c2] Then
[c5].Value = yy + 410 & Choose(uu, "b", "a")
ElseIf Cells(257, ii) = [c2] Then
[c5].Value = yy + 420 & Choose(uu, "b", "a")
ElseIf Cells(263, ii) = [c2] Then
[c5].Value = yy + 430 & Choose(uu, "b", "a")
ElseIf Cells(269, ii) = [c2] Then
[c5].Value = yy + 440 & Choose(uu, "b", "a")
ElseIf Cells(275, ii) = [c2] Then
[c5].Value = yy + 450 & Choose(uu, "b", "a")
ElseIf Cells(281, ii) = [c2] Then
[c5].Value = yy + 460 & Choose(uu, "b", "a")
ElseIf Cells(287, ii) = [c2] Then
[c5].Value = yy + 470 & Choose(uu, "b", "a")
ElseIf Cells(293, ii) = [c2] Then
[c5].Value = yy + 480 & Choose(uu, "b", "a")
ElseIf Cells(299, ii) = [c2] Then
[c5].Value = yy + 490 & Choose(uu, "b", "a")
Exit For
End If
Next ii
'
' ----------------------------------------------------
Dim iii&, uuu&, yyy&:
yyy = 0
For iii = 2 To 21
uuu = 1
If iii Mod 2 = 0 Then yyy = yyy + 1: uuu = 2
If Cells(5, iii) = [d2] Then
[d5].Value = yyy & Choose(uuu, "b", "a")
ElseIf Cells(11, iii) = [d2] Then
[d5].Value = yyy + 10 & Choose(uuu, "b", "a")

Click to expand...

Guest · Oct 9, 2006

Many thanks Michel this is pretty much what I am trying to do but
is possible to add variable to cell referens B2 and B5 so that it would go
like
b2 and b5, c2 and c5, d2 and d5 .... to u2 and u5
b8 and b11, c8 and c11, d8 and d11 .... to u8 and u11
b14 and b17, c14 and c17, d14 and d17 .... to u14 and u17
with same interval all the way to
b269 and b299, c269 and c299, d269 and d299 .... to u269 and u299

(50 different line)

Michel Pierron said:
Hi Ksu;
Test with:

Sub tÃ¤yttÃ¶_1_ja_2pari()
Dim i&, u&, y&, x&
For x = 5 To 299 Step 6
For i = 2 To 21
u = 1
If i Mod 2 = 0 Then y = y + 1: u = 2
If Cells(x, i) = [b2] Then
[b5].Value = y & Choose(u, "b", "a")
Exit Sub
End If
Next i
Next x
End Sub

MP

Ksu said:

Hi
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
hopefully this will clarify what I'm trying to do so far I have been able
to
get it working with macros like below but I would need to make 250 macros
like that
and now I have 10 macros ready and when I run them it takes approximately
1
min to complete the 10 macro so is there some other way to do this /
smarter
/faster

Sub tÃ¤yttÃ¶_1_ja_2pari()

' 1 pari
Dim i&, u&, y&:
y = 0
For i = 2 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")
ElseIf Cells(11, i) = [b2] Then
[b5].Value = y + 10 & Choose(u, "b", "a")
ElseIf Cells(17, i) = [b2] Then
[b5].Value = y + 20 & Choose(u, "b", "a")
ElseIf Cells(23, i) = [b2] Then
[b5].Value = y + 30 & Choose(u, "b", "a")
ElseIf Cells(29, i) = [b2] Then
[b5].Value = y + 40 & Choose(u, "b", "a")
ElseIf Cells(35, i) = [b2] Then
[b5].Value = y + 50 & Choose(u, "b", "a")
ElseIf Cells(41, i) = [b2] Then
[b5].Value = y + 60 & Choose(u, "b", "a")
ElseIf Cells(47, i) = [b2] Then
[b5].Value = y + 70 & Choose(u, "b", "a")
ElseIf Cells(53, i) = [b2] Then
[b5].Value = y + 80 & Choose(u, "b", "a")
ElseIf Cells(59, i) = [b2] Then
[b5].Value = y + 90 & Choose(u, "b", "a")
ElseIf Cells(65, i) = [b2] Then
[b5].Value = y + 100 & Choose(u, "b", "a")
ElseIf Cells(71, i) = [b2] Then
[b5].Value = y + 110 & Choose(u, "b", "a")
ElseIf Cells(77, i) = [b2] Then
[b5].Value = y + 120 & Choose(u, "b", "a")
ElseIf Cells(83, i) = [b2] Then
[b5].Value = y + 130 & Choose(u, "b", "a")
ElseIf Cells(89, i) = [b2] Then
[b5].Value = y + 140 & Choose(u, "b", "a")
ElseIf Cells(95, i) = [b2] Then
[b5].Value = y + 150 & Choose(u, "b", "a")
ElseIf Cells(101, i) = [b2] Then
[b5].Value = y + 160 & Choose(u, "b", "a")
ElseIf Cells(107, i) = [b2] Then
[b5].Value = y + 170 & Choose(u, "b", "a")
ElseIf Cells(113, i) = [b2] Then
[b5].Value = y + 180 & Choose(u, "b", "a")
ElseIf Cells(119, i) = [b2] Then
[b5].Value = y + 190 & Choose(u, "b", "a")
ElseIf Cells(125, i) = [b2] Then
[b5].Value = y + 200 & Choose(u, "b", "a")
ElseIf Cells(131, i) = [b2] Then
[b5].Value = y + 210 & Choose(u, "b", "a")
ElseIf Cells(137, i) = [b2] Then
[b5].Value = y + 220 & Choose(u, "b", "a")
ElseIf Cells(143, i) = [b2] Then
[b5].Value = y + 230 & Choose(u, "b", "a")
ElseIf Cells(149, i) = [b2] Then
[b5].Value = y + 240 & Choose(u, "b", "a")
ElseIf Cells(155, i) = [b2] Then
[b5].Value = y + 250 & Choose(u, "b", "a")
ElseIf Cells(161, i) = [b2] Then
[b5].Value = y + 260 & Choose(u, "b", "a")
ElseIf Cells(167, i) = [b2] Then
[b5].Value = y + 270 & Choose(u, "b", "a")
ElseIf Cells(173, i) = [b2] Then
[b5].Value = y + 280 & Choose(u, "b", "a")
ElseIf Cells(179, i) = [b2] Then
[b5].Value = y + 290 & Choose(u, "b", "a")
ElseIf Cells(185, i) = [b2] Then
[b5].Value = y + 300 & Choose(u, "b", "a")
ElseIf Cells(191, i) = [b2] Then
[b5].Value = y + 310 & Choose(u, "b", "a")
ElseIf Cells(197, i) = [b2] Then
[b5].Value = y + 320 & Choose(u, "b", "a")
ElseIf Cells(203, i) = [b2] Then
[b5].Value = y + 330 & Choose(u, "b", "a")
ElseIf Cells(209, i) = [b2] Then
[b5].Value = y + 340 & Choose(u, "b", "a")
ElseIf Cells(215, i) = [b2] Then
[b5].Value = y + 350 & Choose(u, "b", "a")
ElseIf Cells(221, i) = [b2] Then
[b5].Value = y + 360 & Choose(u, "b", "a")
ElseIf Cells(227, i) = [b2] Then
[b5].Value = y + 370 & Choose(u, "b", "a")
ElseIf Cells(233, i) = [b2] Then
[b5].Value = y + 380 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(245, i) = [b2] Then
[b5].Value = y + 400 & Choose(u, "b", "a")
ElseIf Cells(251, i) = [b2] Then
[b5].Value = y + 410 & Choose(u, "b", "a")
ElseIf Cells(257, i) = [b2] Then
[b5].Value = y + 420 & Choose(u, "b", "a")
ElseIf Cells(263, i) = [b2] Then
[b5].Value = y + 430 & Choose(u, "b", "a")
ElseIf Cells(269, i) = [b2] Then
[b5].Value = y + 440 & Choose(u, "b", "a")
ElseIf Cells(275, i) = [b2] Then
[b5].Value = y + 450 & Choose(u, "b", "a")
ElseIf Cells(281, i) = [b2] Then
[b5].Value = y + 460 & Choose(u, "b", "a")
ElseIf Cells(287, i) = [b2] Then
[b5].Value = y + 470 & Choose(u, "b", "a")
ElseIf Cells(293, i) = [b2] Then
[b5].Value = y + 480 & Choose(u, "b", "a")
ElseIf Cells(299, i) = [b2] Then
[b5].Value = y + 490 & Choose(u, "b", "a")
Exit For
End If
Next i
'
' ----------------------------------------------------
'
Dim ii&, uu&, yy&:
yy = 0
For ii = 2 To 21
uu = 1
If ii Mod 2 = 0 Then yy = yy + 1: uu = 2
If Cells(5, ii) = [c2] Then
[c5].Value = yy & Choose(uu, "b", "a")
ElseIf Cells(11, ii) = [c2] Then
[c5].Value = yy + 10 & Choose(uu, "b", "a")
ElseIf Cells(17, ii) = [c2] Then
[c5].Value = yy + 20 & Choose(uu, "b", "a")
ElseIf Cells(23, ii) = [c2] Then
[c5].Value = yy + 30 & Choose(uu, "b", "a")
ElseIf Cells(29, ii) = [c2] Then
[c5].Value = yy + 40 & Choose(uu, "b", "a")
ElseIf Cells(35, ii) = [c2] Then
[c5].Value = yy + 50 & Choose(uu, "b", "a")
ElseIf Cells(41, ii) = [c2] Then
[c5].Value = yy + 60 & Choose(uu, "b", "a")
ElseIf Cells(47, ii) = [c2] Then
[c5].Value = yy + 70 & Choose(uu, "b", "a")
ElseIf Cells(53, ii) = [c2] Then
[c5].Value = yy + 80 & Choose(uu, "b", "a")
ElseIf Cells(59, ii) = [c2] Then
[c5].Value = yy + 90 & Choose(uu, "b", "a")
ElseIf Cells(65, ii) = [c2] Then
[c5].Value = yy + 100 & Choose(uu, "b", "a")
ElseIf Cells(71, ii) = [c2] Then
[c5].Value = yy + 110 & Choose(uu, "b", "a")
ElseIf Cells(77, ii) = [c2] Then
[c5].Value = yy + 120 & Choose(uu, "b", "a")
ElseIf Cells(83, ii) = [c2] Then
[c5].Value = yy + 130 & Choose(uu, "b", "a")
ElseIf Cells(89, ii) = [c2] Then
[c5].Value = yy + 140 & Choose(uu, "b", "a")
ElseIf Cells(95, ii) = [c2] Then
[c5].Value = yy + 150 & Choose(uu, "b", "a")
ElseIf Cells(101, ii) = [c2] Then
[c5].Value = yy + 160 & Choose(uu, "b", "a")
ElseIf Cells(107, ii) = [c2] Then
[c5].Value = yy + 170 & Choose(uu, "b", "a")
ElseIf Cells(113, ii) = [c2] Then
[c5].Value = yy + 180 & Choose(uu, "b", "a")
ElseIf Cells(119, ii) = [c2] Then
[c5].Value = yy + 190 & Choose(uu, "b", "a")
ElseIf Cells(125, ii) = [c2] Then
[c5].Value = yy + 200 & Choose(uu, "b", "a")
ElseIf Cells(131, ii) = [c2] Then
[c5].Value = yy + 210 & Choose(uu, "b", "a")
ElseIf Cells(137, ii) = [c2] Then
[c5].Value = yy + 220 & Choose(uu, "b", "a")
ElseIf Cells(143, ii) = [c2] Then
[c5].Value = yy + 230 & Choose(uu, "b", "a")
ElseIf Cells(149, ii) = [c2] Then
[c5].Value = yy + 240 & Choose(uu, "b", "a")
ElseIf Cells(155, ii) = [c2] Then
[c5].Value = yy + 250 & Choose(uu, "b", "a")
ElseIf Cells(161, ii) = [c2] Then
[c5].Value = yy + 260 & Choose(uu, "b", "a")
ElseIf Cells(167, ii) = [c2] Then
[c5].Value = yy + 270 & Choose(uu, "b", "a")
ElseIf Cells(173, ii) = [c2] Then
[c5].Value = yy + 280 & Choose(uu, "b", "a")
ElseIf Cells(179, ii) = [c2] Then
[c5].Value = yy + 290 & Choose(uu, "b", "a")
ElseIf Cells(185, ii) = [c2] Then
[c5].Value = yy + 300 & Choose(uu, "b", "a")
ElseIf Cells(191, ii) = [c2] Then
[c5].Value = yy + 310 & Choose(uu, "b", "a")
ElseIf Cells(197, ii) = [c2] Then
[c5].Value = yy + 320 & Choose(uu, "b", "a")
ElseIf Cells(203, ii) = [c2] Then
[c5].Value = yy + 330 & Choose(uu, "b", "a")
ElseIf Cells(209, ii) = [c2] Then
[c5].Value = yy + 340 & Choose(uu, "b", "a")
ElseIf Cells(215, ii) = [c2] Then
[c5].Value = yy + 350 & Choose(uu, "b", "a")
ElseIf Cells(221, ii) = [c2] Then
[c5].Value = yy + 360 & Choose(uu, "b", "a")
ElseIf Cells(227, ii) = [c2] Then
[c5].Value = yy + 370 & Choose(uu, "b", "a")
ElseIf Cells(233, ii) = [c2] Then
[c5].Value = yy + 380 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(245, ii) = [c2] Then
[c5].Value = yy + 400 & Choose(uu, "b", "a")
ElseIf Cells(251, ii) = [c2] Then
[c5].Value = yy + 410 & Choose(uu, "b", "a")
ElseIf Cells(257, ii) = [c2] Then
[c5].Value = yy + 420 & Choose(uu, "b", "a")
ElseIf Cells(263, ii) = [c2] Then
[c5].Value = yy + 430 & Choose(uu, "b", "a")
ElseIf Cells(269, ii) = [c2] Then
[c5].Value = yy + 440 & Choose(uu, "b", "a")
ElseIf Cells(275, ii) = [c2] Then
[c5].Value = yy + 450 & Choose(uu, "b", "a")
ElseIf Cells(281, ii) = [c2] Then
[c5].Value = yy + 460 & Choose(uu, "b", "a")
ElseIf Cells(287, ii) = [c2] Then
[c5].Value = yy + 470 & Choose(uu, "b", "a")
ElseIf Cells(293, ii) = [c2] Then
[c5].Value = yy + 480 & Choose(uu, "b", "a")
ElseIf Cells(299, ii) = [c2] Then
[c5].Value = yy + 490 & Choose(uu, "b", "a")
Exit For
End If
Next ii
'
' ----------------------------------------------------
Dim iii&, uuu&, yyy&:
yyy = 0
For iii = 2 To 21
uuu = 1
If iii Mod 2 = 0 Then yyy = yyy + 1: uuu = 2
If Cells(5, iii) = [d2] Then
[d5].Value = yyy & Choose(uuu, "b", "a")
ElseIf Cells(11, iii) = [d2] Then
[d5].Value = yyy + 10 & Choose(uuu, "b", "a")
ElseIf Cells(17, iii) = [d2] Then
[d5].Value = yyy + 20 & Choose(uuu, "b", "a")
ElseIf Cells(23, iii) = [d2] Then
[d5].Value = yyy + 30 & Choose(uuu, "b", "a")
ElseIf Cells(29, iii) = [d2] Then
[d5].Value = yyy + 40 & Choose(uuu, "b", "a")
ElseIf Cells(35, iii) = [d2] Then
[d5].Value = yyy + 50 & Choose(uuu, "b", "a")
ElseIf Cells(41, iii) = [d2] Then
[d5].Value = yyy + 60 & Choose(uuu, "b", "a")
ElseIf Cells(47, iii) = [d2] Then
[d5].Value = yyy + 70 & Choose(uuu, "b", "a")
ElseIf Cells(53, iii) = [d2] Then
[d5].Value = yyy + 80 & Choose(uuu, "b", "a")
ElseIf Cells(59, iii) = [d2] Then
[d5].Value = yyy + 90 & Choose(uuu, "b", "a")
ElseIf Cells(65, iii) = [d2] Then
[d5].Value = yyy + 100 & Choose(uuu, "b", "a")
ElseIf Cells(71, iii) = [d2] Then

Click to expand...

John Coleman · Oct 9, 2006

I meant

Dim endString as String

In the declarations part. It was probably the line endString = "a" that
was throwing the type mismatch. I'm surprised it wasn't caught by Debug

Compile (which I did do before posting).

I wasn't quite sure what your spreadsheet looked like - so I wasn't
able to test the code.
There is something I need to get done at work in the next few hours -
so I won't be able to look at your spreadsheet (today at least)

Sorry about any confusion

-John Coleman

John Coleman said:
Hi
And thanks John for your answer but I didn't quite understand the code and
when I try to use it I get Ru-time error 13 Type mismatch (Error 13)

and there is link to my file if I would clarify what I am trying to do
http://www.mytempdir.com/980693

John Coleman said:

Hi

Long block of virtually identical code can usually be placed in loops.
Something like:

Sub simplified()

Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, y As Long
Dim endString As Long
Dim found As Boolean

For i = 2 To 5
y = 0
For j = 2 To 21
If j Mod 2 = 0 Then
y = y + 1
endString = "a"
Else
endString = "b"
End If
k = 0
found = False
Do While Not found And k <= 49
If Cells(5 + 6 * k, j).Value = Cells(2, i).Value Then
Cells(5, i).Value = (y + 10 * k) & endString
found = True
Else
k = k + 1
End If
Loop
Next j
Next i
Application.ScreenUpdating = True
End Sub

If performance continues to be a problem you can consider maybe
transfering all of the data into an array at the begginng of the sub
and processing the array directly.

Hope that helps

-John Coleman

Hi
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
hopefully this will clarify what I'm trying to do so far I have been able to
get it working with macros like below but I would need to make 250 macros
like that
and now I have 10 macros ready and when I run them it takes approximately 1
min to complete the 10 macro so is there some other way to do this / smarter
/faster

Sub täyttö_1_ja_2pari()

' 1 pari
Dim i&, u&, y&:
y = 0
For i = 2 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")
ElseIf Cells(11, i) = [b2] Then
[b5].Value = y + 10 & Choose(u, "b", "a")
ElseIf Cells(17, i) = [b2] Then
[b5].Value = y + 20 & Choose(u, "b", "a")
ElseIf Cells(23, i) = [b2] Then
[b5].Value = y + 30 & Choose(u, "b", "a")
ElseIf Cells(29, i) = [b2] Then
[b5].Value = y + 40 & Choose(u, "b", "a")
ElseIf Cells(35, i) = [b2] Then
[b5].Value = y + 50 & Choose(u, "b", "a")
ElseIf Cells(41, i) = [b2] Then
[b5].Value = y + 60 & Choose(u, "b", "a")
ElseIf Cells(47, i) = [b2] Then
[b5].Value = y + 70 & Choose(u, "b", "a")
ElseIf Cells(53, i) = [b2] Then
[b5].Value = y + 80 & Choose(u, "b", "a")
ElseIf Cells(59, i) = [b2] Then
[b5].Value = y + 90 & Choose(u, "b", "a")
ElseIf Cells(65, i) = [b2] Then
[b5].Value = y + 100 & Choose(u, "b", "a")
ElseIf Cells(71, i) = [b2] Then
[b5].Value = y + 110 & Choose(u, "b", "a")
ElseIf Cells(77, i) = [b2] Then
[b5].Value = y + 120 & Choose(u, "b", "a")
ElseIf Cells(83, i) = [b2] Then
[b5].Value = y + 130 & Choose(u, "b", "a")
ElseIf Cells(89, i) = [b2] Then
[b5].Value = y + 140 & Choose(u, "b", "a")
ElseIf Cells(95, i) = [b2] Then
[b5].Value = y + 150 & Choose(u, "b", "a")
ElseIf Cells(101, i) = [b2] Then
[b5].Value = y + 160 & Choose(u, "b", "a")
ElseIf Cells(107, i) = [b2] Then
[b5].Value = y + 170 & Choose(u, "b", "a")
ElseIf Cells(113, i) = [b2] Then
[b5].Value = y + 180 & Choose(u, "b", "a")
ElseIf Cells(119, i) = [b2] Then
[b5].Value = y + 190 & Choose(u, "b", "a")
ElseIf Cells(125, i) = [b2] Then
[b5].Value = y + 200 & Choose(u, "b", "a")
ElseIf Cells(131, i) = [b2] Then
[b5].Value = y + 210 & Choose(u, "b", "a")
ElseIf Cells(137, i) = [b2] Then
[b5].Value = y + 220 & Choose(u, "b", "a")
ElseIf Cells(143, i) = [b2] Then
[b5].Value = y + 230 & Choose(u, "b", "a")
ElseIf Cells(149, i) = [b2] Then
[b5].Value = y + 240 & Choose(u, "b", "a")
ElseIf Cells(155, i) = [b2] Then
[b5].Value = y + 250 & Choose(u, "b", "a")
ElseIf Cells(161, i) = [b2] Then
[b5].Value = y + 260 & Choose(u, "b", "a")
ElseIf Cells(167, i) = [b2] Then
[b5].Value = y + 270 & Choose(u, "b", "a")
ElseIf Cells(173, i) = [b2] Then
[b5].Value = y + 280 & Choose(u, "b", "a")
ElseIf Cells(179, i) = [b2] Then
[b5].Value = y + 290 & Choose(u, "b", "a")
ElseIf Cells(185, i) = [b2] Then
[b5].Value = y + 300 & Choose(u, "b", "a")
ElseIf Cells(191, i) = [b2] Then
[b5].Value = y + 310 & Choose(u, "b", "a")
ElseIf Cells(197, i) = [b2] Then
[b5].Value = y + 320 & Choose(u, "b", "a")
ElseIf Cells(203, i) = [b2] Then
[b5].Value = y + 330 & Choose(u, "b", "a")
ElseIf Cells(209, i) = [b2] Then
[b5].Value = y + 340 & Choose(u, "b", "a")
ElseIf Cells(215, i) = [b2] Then
[b5].Value = y + 350 & Choose(u, "b", "a")
ElseIf Cells(221, i) = [b2] Then
[b5].Value = y + 360 & Choose(u, "b", "a")
ElseIf Cells(227, i) = [b2] Then
[b5].Value = y + 370 & Choose(u, "b", "a")
ElseIf Cells(233, i) = [b2] Then
[b5].Value = y + 380 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(245, i) = [b2] Then
[b5].Value = y + 400 & Choose(u, "b", "a")
ElseIf Cells(251, i) = [b2] Then
[b5].Value = y + 410 & Choose(u, "b", "a")
ElseIf Cells(257, i) = [b2] Then
[b5].Value = y + 420 & Choose(u, "b", "a")
ElseIf Cells(263, i) = [b2] Then
[b5].Value = y + 430 & Choose(u, "b", "a")
ElseIf Cells(269, i) = [b2] Then
[b5].Value = y + 440 & Choose(u, "b", "a")
ElseIf Cells(275, i) = [b2] Then
[b5].Value = y + 450 & Choose(u, "b", "a")
ElseIf Cells(281, i) = [b2] Then
[b5].Value = y + 460 & Choose(u, "b", "a")
ElseIf Cells(287, i) = [b2] Then
[b5].Value = y + 470 & Choose(u, "b", "a")
ElseIf Cells(293, i) = [b2] Then
[b5].Value = y + 480 & Choose(u, "b", "a")
ElseIf Cells(299, i) = [b2] Then
[b5].Value = y + 490 & Choose(u, "b", "a")
Exit For
End If
Next i
'
' ----------------------------------------------------
'
Dim ii&, uu&, yy&:
yy = 0
For ii = 2 To 21
uu = 1
If ii Mod 2 = 0 Then yy = yy + 1: uu = 2
If Cells(5, ii) = [c2] Then
[c5].Value = yy & Choose(uu, "b", "a")
ElseIf Cells(11, ii) = [c2] Then
[c5].Value = yy + 10 & Choose(uu, "b", "a")
ElseIf Cells(17, ii) = [c2] Then
[c5].Value = yy + 20 & Choose(uu, "b", "a")
ElseIf Cells(23, ii) = [c2] Then
[c5].Value = yy + 30 & Choose(uu, "b", "a")
ElseIf Cells(29, ii) = [c2] Then
[c5].Value = yy + 40 & Choose(uu, "b", "a")
ElseIf Cells(35, ii) = [c2] Then
[c5].Value = yy + 50 & Choose(uu, "b", "a")
ElseIf Cells(41, ii) = [c2] Then
[c5].Value = yy + 60 & Choose(uu, "b", "a")
ElseIf Cells(47, ii) = [c2] Then
[c5].Value = yy + 70 & Choose(uu, "b", "a")
ElseIf Cells(53, ii) = [c2] Then
[c5].Value = yy + 80 & Choose(uu, "b", "a")
ElseIf Cells(59, ii) = [c2] Then
[c5].Value = yy + 90 & Choose(uu, "b", "a")
ElseIf Cells(65, ii) = [c2] Then
[c5].Value = yy + 100 & Choose(uu, "b", "a")
ElseIf Cells(71, ii) = [c2] Then
[c5].Value = yy + 110 & Choose(uu, "b", "a")
ElseIf Cells(77, ii) = [c2] Then
[c5].Value = yy + 120 & Choose(uu, "b", "a")
ElseIf Cells(83, ii) = [c2] Then
[c5].Value = yy + 130 & Choose(uu, "b", "a")
ElseIf Cells(89, ii) = [c2] Then
[c5].Value = yy + 140 & Choose(uu, "b", "a")
ElseIf Cells(95, ii) = [c2] Then
[c5].Value = yy + 150 & Choose(uu, "b", "a")
ElseIf Cells(101, ii) = [c2] Then
[c5].Value = yy + 160 & Choose(uu, "b", "a")
ElseIf Cells(107, ii) = [c2] Then
[c5].Value = yy + 170 & Choose(uu, "b", "a")
ElseIf Cells(113, ii) = [c2] Then
[c5].Value = yy + 180 & Choose(uu, "b", "a")
ElseIf Cells(119, ii) = [c2] Then
[c5].Value = yy + 190 & Choose(uu, "b", "a")
ElseIf Cells(125, ii) = [c2] Then
[c5].Value = yy + 200 & Choose(uu, "b", "a")
ElseIf Cells(131, ii) = [c2] Then
[c5].Value = yy + 210 & Choose(uu, "b", "a")
ElseIf Cells(137, ii) = [c2] Then
[c5].Value = yy + 220 & Choose(uu, "b", "a")
ElseIf Cells(143, ii) = [c2] Then
[c5].Value = yy + 230 & Choose(uu, "b", "a")
ElseIf Cells(149, ii) = [c2] Then
[c5].Value = yy + 240 & Choose(uu, "b", "a")
ElseIf Cells(155, ii) = [c2] Then
[c5].Value = yy + 250 & Choose(uu, "b", "a")
ElseIf Cells(161, ii) = [c2] Then
[c5].Value = yy + 260 & Choose(uu, "b", "a")
ElseIf Cells(167, ii) = [c2] Then
[c5].Value = yy + 270 & Choose(uu, "b", "a")
ElseIf Cells(173, ii) = [c2] Then
[c5].Value = yy + 280 & Choose(uu, "b", "a")
ElseIf Cells(179, ii) = [c2] Then
[c5].Value = yy + 290 & Choose(uu, "b", "a")
ElseIf Cells(185, ii) = [c2] Then
[c5].Value = yy + 300 & Choose(uu, "b", "a")
ElseIf Cells(191, ii) = [c2] Then
[c5].Value = yy + 310 & Choose(uu, "b", "a")
ElseIf Cells(197, ii) = [c2] Then
[c5].Value = yy + 320 & Choose(uu, "b", "a")
ElseIf Cells(203, ii) = [c2] Then
[c5].Value = yy + 330 & Choose(uu, "b", "a")
ElseIf Cells(209, ii) = [c2] Then
[c5].Value = yy + 340 & Choose(uu, "b", "a")
ElseIf Cells(215, ii) = [c2] Then
[c5].Value = yy + 350 & Choose(uu, "b", "a")
ElseIf Cells(221, ii) = [c2] Then
[c5].Value = yy + 360 & Choose(uu, "b", "a")
ElseIf Cells(227, ii) = [c2] Then
[c5].Value = yy + 370 & Choose(uu, "b", "a")
ElseIf Cells(233, ii) = [c2] Then
[c5].Value = yy + 380 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(245, ii) = [c2] Then
[c5].Value = yy + 400 & Choose(uu, "b", "a")
ElseIf Cells(251, ii) = [c2] Then
[c5].Value = yy + 410 & Choose(uu, "b", "a")
ElseIf Cells(257, ii) = [c2] Then
[c5].Value = yy + 420 & Choose(uu, "b", "a")
ElseIf Cells(263, ii) = [c2] Then
[c5].Value = yy + 430 & Choose(uu, "b", "a")
ElseIf Cells(269, ii) = [c2] Then
[c5].Value = yy + 440 & Choose(uu, "b", "a")
ElseIf Cells(275, ii) = [c2] Then
[c5].Value = yy + 450 & Choose(uu, "b", "a")
ElseIf Cells(281, ii) = [c2] Then
[c5].Value = yy + 460 & Choose(uu, "b", "a")
ElseIf Cells(287, ii) = [c2] Then
[c5].Value = yy + 470 & Choose(uu, "b", "a")
ElseIf Cells(293, ii) = [c2] Then
[c5].Value = yy + 480 & Choose(uu, "b", "a")
ElseIf Cells(299, ii) = [c2] Then
[c5].Value = yy + 490 & Choose(uu, "b", "a")
Exit For
End If
Next ii
'
' ----------------------------------------------------
Dim iii&, uuu&, yyy&:
yyy = 0
For iii = 2 To 21
uuu = 1
If iii Mod 2 = 0 Then yyy = yyy + 1: uuu = 2
If Cells(5, iii) = [d2] Then
[d5].Value = yyy & Choose(uuu, "b", "a")
ElseIf Cells(11, iii) = [d2] Then
[d5].Value = yyy + 10 & Choose(uuu, "b", "a")

Click to expand...

Click to expand...

Michel Pierron · Oct 9, 2006

Re Ksu,
Something like:

Sub täyttö_1_ja_2pari()
Const L$ = "BCDEFGHIJKLMNOPQRSTU"
Dim b&, t&, p&
For t = 1 To 3
p = Choose(t, 2, 8, 14)
For b = 1 To Len(L)
Call Parity(Mid$(L, b, 1) & p)
Next b
Next t
End Sub

Private Sub Parity(ByVal R$)
Dim i&, u&, y&, x&
For x = 5 To 299 Step 6
For i = 2 To 21
u = 1
If i Mod 2 = 0 Then y = y + 1: u = 2
If Cells(x, i) = Range(R).Value Then
Range(R).Offset(3, 0).Value = y & Mid$("ba", u, 1)
Exit Sub
End If
Next i
Next x
End Sub

MP

Ksu said:
Many thanks Michel this is pretty much what I am trying to do but
is possible to add variable to cell referens B2 and B5 so that it would go
like
b2 and b5, c2 and c5, d2 and d5 .... to u2 and u5
b8 and b11, c8 and c11, d8 and d11 .... to u8 and u11
b14 and b17, c14 and c17, d14 and d17 .... to u14 and u17
with same interval all the way to
b269 and b299, c269 and c299, d269 and d299 .... to u269 and u299

(50 different line)

Michel Pierron said:

Hi Ksu;
Test with:

Sub täyttö_1_ja_2pari()
Dim i&, u&, y&, x&
For x = 5 To 299 Step 6
For i = 2 To 21
u = 1
If i Mod 2 = 0 Then y = y + 1: u = 2
If Cells(x, i) = [b2] Then
[b5].Value = y & Choose(u, "b", "a")
Exit Sub
End If
Next i
Next x
End Sub

MP

Ksu said:

Hi
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
hopefully this will clarify what I'm trying to do so far I have been
able
to
get it working with macros like below but I would need to make 250
macros
like that
and now I have 10 macros ready and when I run them it takes
approximately
1
min to complete the 10 macro so is there some other way to do this /
smarter
/faster

Sub täyttö_1_ja_2pari()

' 1 pari
Dim i&, u&, y&:
y = 0
For i = 2 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")
ElseIf Cells(11, i) = [b2] Then
[b5].Value = y + 10 & Choose(u, "b", "a")
ElseIf Cells(17, i) = [b2] Then
[b5].Value = y + 20 & Choose(u, "b", "a")
ElseIf Cells(23, i) = [b2] Then
[b5].Value = y + 30 & Choose(u, "b", "a")
ElseIf Cells(29, i) = [b2] Then
[b5].Value = y + 40 & Choose(u, "b", "a")
ElseIf Cells(35, i) = [b2] Then
[b5].Value = y + 50 & Choose(u, "b", "a")
ElseIf Cells(41, i) = [b2] Then
[b5].Value = y + 60 & Choose(u, "b", "a")
ElseIf Cells(47, i) = [b2] Then
[b5].Value = y + 70 & Choose(u, "b", "a")
ElseIf Cells(53, i) = [b2] Then
[b5].Value = y + 80 & Choose(u, "b", "a")
ElseIf Cells(59, i) = [b2] Then
[b5].Value = y + 90 & Choose(u, "b", "a")
ElseIf Cells(65, i) = [b2] Then
[b5].Value = y + 100 & Choose(u, "b", "a")
ElseIf Cells(71, i) = [b2] Then
[b5].Value = y + 110 & Choose(u, "b", "a")
ElseIf Cells(77, i) = [b2] Then
[b5].Value = y + 120 & Choose(u, "b", "a")
ElseIf Cells(83, i) = [b2] Then
[b5].Value = y + 130 & Choose(u, "b", "a")
ElseIf Cells(89, i) = [b2] Then
[b5].Value = y + 140 & Choose(u, "b", "a")
ElseIf Cells(95, i) = [b2] Then
[b5].Value = y + 150 & Choose(u, "b", "a")
ElseIf Cells(101, i) = [b2] Then
[b5].Value = y + 160 & Choose(u, "b", "a")
ElseIf Cells(107, i) = [b2] Then
[b5].Value = y + 170 & Choose(u, "b", "a")
ElseIf Cells(113, i) = [b2] Then
[b5].Value = y + 180 & Choose(u, "b", "a")
ElseIf Cells(119, i) = [b2] Then
[b5].Value = y + 190 & Choose(u, "b", "a")
ElseIf Cells(125, i) = [b2] Then
[b5].Value = y + 200 & Choose(u, "b", "a")
ElseIf Cells(131, i) = [b2] Then
[b5].Value = y + 210 & Choose(u, "b", "a")
ElseIf Cells(137, i) = [b2] Then
[b5].Value = y + 220 & Choose(u, "b", "a")
ElseIf Cells(143, i) = [b2] Then
[b5].Value = y + 230 & Choose(u, "b", "a")
ElseIf Cells(149, i) = [b2] Then
[b5].Value = y + 240 & Choose(u, "b", "a")
ElseIf Cells(155, i) = [b2] Then
[b5].Value = y + 250 & Choose(u, "b", "a")
ElseIf Cells(161, i) = [b2] Then
[b5].Value = y + 260 & Choose(u, "b", "a")
ElseIf Cells(167, i) = [b2] Then
[b5].Value = y + 270 & Choose(u, "b", "a")
ElseIf Cells(173, i) = [b2] Then
[b5].Value = y + 280 & Choose(u, "b", "a")
ElseIf Cells(179, i) = [b2] Then
[b5].Value = y + 290 & Choose(u, "b", "a")
ElseIf Cells(185, i) = [b2] Then
[b5].Value = y + 300 & Choose(u, "b", "a")
ElseIf Cells(191, i) = [b2] Then
[b5].Value = y + 310 & Choose(u, "b", "a")
ElseIf Cells(197, i) = [b2] Then
[b5].Value = y + 320 & Choose(u, "b", "a")
ElseIf Cells(203, i) = [b2] Then
[b5].Value = y + 330 & Choose(u, "b", "a")
ElseIf Cells(209, i) = [b2] Then
[b5].Value = y + 340 & Choose(u, "b", "a")
ElseIf Cells(215, i) = [b2] Then
[b5].Value = y + 350 & Choose(u, "b", "a")
ElseIf Cells(221, i) = [b2] Then
[b5].Value = y + 360 & Choose(u, "b", "a")
ElseIf Cells(227, i) = [b2] Then
[b5].Value = y + 370 & Choose(u, "b", "a")
ElseIf Cells(233, i) = [b2] Then
[b5].Value = y + 380 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(239, i) = [b2] Then
[b5].Value = y + 390 & Choose(u, "b", "a")
ElseIf Cells(245, i) = [b2] Then
[b5].Value = y + 400 & Choose(u, "b", "a")
ElseIf Cells(251, i) = [b2] Then
[b5].Value = y + 410 & Choose(u, "b", "a")
ElseIf Cells(257, i) = [b2] Then
[b5].Value = y + 420 & Choose(u, "b", "a")
ElseIf Cells(263, i) = [b2] Then
[b5].Value = y + 430 & Choose(u, "b", "a")
ElseIf Cells(269, i) = [b2] Then
[b5].Value = y + 440 & Choose(u, "b", "a")
ElseIf Cells(275, i) = [b2] Then
[b5].Value = y + 450 & Choose(u, "b", "a")
ElseIf Cells(281, i) = [b2] Then
[b5].Value = y + 460 & Choose(u, "b", "a")
ElseIf Cells(287, i) = [b2] Then
[b5].Value = y + 470 & Choose(u, "b", "a")
ElseIf Cells(293, i) = [b2] Then
[b5].Value = y + 480 & Choose(u, "b", "a")
ElseIf Cells(299, i) = [b2] Then
[b5].Value = y + 490 & Choose(u, "b", "a")
Exit For
End If
Next i
'
' ----------------------------------------------------
'
Dim ii&, uu&, yy&:
yy = 0
For ii = 2 To 21
uu = 1
If ii Mod 2 = 0 Then yy = yy + 1: uu = 2
If Cells(5, ii) = [c2] Then
[c5].Value = yy & Choose(uu, "b", "a")
ElseIf Cells(11, ii) = [c2] Then
[c5].Value = yy + 10 & Choose(uu, "b", "a")
ElseIf Cells(17, ii) = [c2] Then
[c5].Value = yy + 20 & Choose(uu, "b", "a")
ElseIf Cells(23, ii) = [c2] Then
[c5].Value = yy + 30 & Choose(uu, "b", "a")
ElseIf Cells(29, ii) = [c2] Then
[c5].Value = yy + 40 & Choose(uu, "b", "a")
ElseIf Cells(35, ii) = [c2] Then
[c5].Value = yy + 50 & Choose(uu, "b", "a")
ElseIf Cells(41, ii) = [c2] Then
[c5].Value = yy + 60 & Choose(uu, "b", "a")
ElseIf Cells(47, ii) = [c2] Then
[c5].Value = yy + 70 & Choose(uu, "b", "a")
ElseIf Cells(53, ii) = [c2] Then
[c5].Value = yy + 80 & Choose(uu, "b", "a")
ElseIf Cells(59, ii) = [c2] Then
[c5].Value = yy + 90 & Choose(uu, "b", "a")
ElseIf Cells(65, ii) = [c2] Then
[c5].Value = yy + 100 & Choose(uu, "b", "a")
ElseIf Cells(71, ii) = [c2] Then
[c5].Value = yy + 110 & Choose(uu, "b", "a")
ElseIf Cells(77, ii) = [c2] Then
[c5].Value = yy + 120 & Choose(uu, "b", "a")
ElseIf Cells(83, ii) = [c2] Then
[c5].Value = yy + 130 & Choose(uu, "b", "a")
ElseIf Cells(89, ii) = [c2] Then
[c5].Value = yy + 140 & Choose(uu, "b", "a")
ElseIf Cells(95, ii) = [c2] Then
[c5].Value = yy + 150 & Choose(uu, "b", "a")
ElseIf Cells(101, ii) = [c2] Then
[c5].Value = yy + 160 & Choose(uu, "b", "a")
ElseIf Cells(107, ii) = [c2] Then
[c5].Value = yy + 170 & Choose(uu, "b", "a")
ElseIf Cells(113, ii) = [c2] Then
[c5].Value = yy + 180 & Choose(uu, "b", "a")
ElseIf Cells(119, ii) = [c2] Then
[c5].Value = yy + 190 & Choose(uu, "b", "a")
ElseIf Cells(125, ii) = [c2] Then
[c5].Value = yy + 200 & Choose(uu, "b", "a")
ElseIf Cells(131, ii) = [c2] Then
[c5].Value = yy + 210 & Choose(uu, "b", "a")
ElseIf Cells(137, ii) = [c2] Then
[c5].Value = yy + 220 & Choose(uu, "b", "a")
ElseIf Cells(143, ii) = [c2] Then
[c5].Value = yy + 230 & Choose(uu, "b", "a")
ElseIf Cells(149, ii) = [c2] Then
[c5].Value = yy + 240 & Choose(uu, "b", "a")
ElseIf Cells(155, ii) = [c2] Then
[c5].Value = yy + 250 & Choose(uu, "b", "a")
ElseIf Cells(161, ii) = [c2] Then
[c5].Value = yy + 260 & Choose(uu, "b", "a")
ElseIf Cells(167, ii) = [c2] Then
[c5].Value = yy + 270 & Choose(uu, "b", "a")
ElseIf Cells(173, ii) = [c2] Then
[c5].Value = yy + 280 & Choose(uu, "b", "a")
ElseIf Cells(179, ii) = [c2] Then
[c5].Value = yy + 290 & Choose(uu, "b", "a")
ElseIf Cells(185, ii) = [c2] Then
[c5].Value = yy + 300 & Choose(uu, "b", "a")
ElseIf Cells(191, ii) = [c2] Then
[c5].Value = yy + 310 & Choose(uu, "b", "a")
ElseIf Cells(197, ii) = [c2] Then
[c5].Value = yy + 320 & Choose(uu, "b", "a")
ElseIf Cells(203, ii) = [c2] Then
[c5].Value = yy + 330 & Choose(uu, "b", "a")
ElseIf Cells(209, ii) = [c2] Then
[c5].Value = yy + 340 & Choose(uu, "b", "a")
ElseIf Cells(215, ii) = [c2] Then
[c5].Value = yy + 350 & Choose(uu, "b", "a")
ElseIf Cells(221, ii) = [c2] Then
[c5].Value = yy + 360 & Choose(uu, "b", "a")
ElseIf Cells(227, ii) = [c2] Then
[c5].Value = yy + 370 & Choose(uu, "b", "a")
ElseIf Cells(233, ii) = [c2] Then
[c5].Value = yy + 380 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(239, ii) = [c2] Then
[c5].Value = yy + 390 & Choose(uu, "b", "a")
ElseIf Cells(245, ii) = [c2] Then
[c5].Value = yy + 400 & Choose(uu, "b", "a")
ElseIf Cells(251, ii) = [c2] Then
[c5].Value = yy + 410 & Choose(uu, "b", "a")
ElseIf Cells(257, ii) = [c2] Then
[c5].Value = yy + 420 & Choose(uu, "b", "a")
ElseIf Cells(263, ii) = [c2] Then
[c5].Value = yy + 430 & Choose(uu, "b", "a")
ElseIf Cells(269, ii) = [c2] Then
[c5].Value = yy + 440 & Choose(uu, "b", "a")
ElseIf Cells(275, ii) = [c2] Then
[c5].Value = yy + 450 & Choose(uu, "b", "a")
ElseIf Cells(281, ii) = [c2] Then
[c5].Value = yy + 460 & Choose(uu, "b", "a")
ElseIf Cells(287, ii) = [c2] Then
[c5].Value = yy + 470 & Choose(uu, "b", "a")
ElseIf Cells(293, ii) = [c2] Then
[c5].Value = yy + 480 & Choose(uu, "b", "a")
ElseIf Cells(299, ii) = [c2] Then
[c5].Value = yy + 490 & Choose(uu, "b", "a")
Exit For
End If
Next ii
'
' ----------------------------------------------------
Dim iii&, uuu&, yyy&:
yyy = 0
For iii = 2 To 21
uuu = 1
If iii Mod 2 = 0 Then yyy = yyy + 1: uuu = 2
If Cells(5, iii) = [d2] Then
[d5].Value = yyy & Choose(uuu, "b", "a")
ElseIf Cells(11, iii) = [d2] Then
[d5].Value = yyy + 10 & Choose(uuu, "b", "a")
ElseIf Cells(17, iii) = [d2] Then
[d5].Value = yyy + 20 & Choose(uuu, "b", "a")
ElseIf Cells(23, iii) = [d2] Then
[d5].Value = yyy + 30 & Choose(uuu, "b", "a")
ElseIf Cells(29, iii) = [d2] Then
[d5].Value = yyy + 40 & Choose(uuu, "b", "a")
ElseIf Cells(35, iii) = [d2] Then
[d5].Value = yyy + 50 & Choose(uuu, "b", "a")
ElseIf Cells(41, iii) = [d2] Then
[d5].Value = yyy + 60 & Choose(uuu, "b", "a")
ElseIf Cells(47, iii) = [d2] Then
[d5].Value = yyy + 70 & Choose(uuu, "b", "a")
ElseIf Cells(53, iii) = [d2] Then
[d5].Value = yyy + 80 & Choose(uuu, "b", "a")
ElseIf Cells(59, iii) = [d2] Then
[d5].Value = yyy + 90 & Choose(uuu, "b", "a")
ElseIf Cells(65, iii) = [d2] Then
[d5].Value = yyy + 100 & Choose(uuu, "b", "a")
ElseIf Cells(71, iii) = [d2] Then

Click to expand...

Click to expand...

How to simplify If / ElseIf statement	4	Oct 7, 2006
How to simplify If / ElseIf statement	4	Oct 7, 2006
Help Shorten Execution Time of VBA program	2	Apr 21, 2009
Recorded macro selection widened	4	May 14, 2009
Combine columns from seperate sheets into new sheet- macro improve	4	Feb 2, 2010
Excel seems to hang while running a UDF	0	Mar 19, 2008
How to alter the code to copy to the same exact cell destination?	1	Aug 6, 2008
macro to calculate or make/update formula	1	Feb 21, 2010

How to simplify macro

Guest

John Coleman

Michel Pierron

Guest

Guest

John Coleman

Michel Pierron

Ask a Question

Similar Threads