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

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

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