How to simplify macro

K

Ksu

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
 
J

John Coleman

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

Michel Pierron

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

Ksu

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...
 
K

Ksu

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...
 
J

John Coleman

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).
Click to expand...
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
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...
Click to expand...
 
M

Michel Pierron

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

Ask a Question

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

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

Ask a Question

Similar Threads

If Condition 0
How to simplify If / ElseIf statement 4
How to simplify If / ElseIf statement 4
Need help with macro 0
Help Shorten Execution Time of VBA program 2
Combine columns from seperate sheets into new sheet- macro improve 4
Recorded macro selection widened 4
macro to calculate or make/update formula 1

Top