| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to simplify macro
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to simplify macro
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") 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to simplify macro
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to simplify macro
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") |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to simplify macro
|
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to simplify macro
I meant
Dim endString as String In the declarations part. It was probably the line endString = "a" that was throwing the type mismatch. I'm surprised it wasn't caught by Debug Compile (which I did do before posting). I wasn't quite sure what your spreadsheet looked like - so I wasn't able to test the code. There is something I need to get done at work in the next few hours - so I won't be able to look at your spreadsheet (today at least) Sorry about any confusion -John Coleman Ksu wrote: 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") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simplify this macro | Excel Discussion (Misc queries) | |||
| simplify a macro | Excel Worksheet Functions | |||
| Simplify the Macro of Looping | Excel Programming | |||
| How do I simplify a macro in excel? | Excel Programming | |||
| Simplify a macro | Excel Programming | |||
Linear Mode
