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

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" a écrit dans le message de news:
...
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