¦U¦ì¥ý¶i¦n ¤p§Ì¦³°ÝÃD·Q½Ð±Ð
¦b¥Lµ{¦¡¹Bºâ¹Lµ{¤¤ °»´ú¹Bºâ¥X¨Óªºµ²ªG"¨º¤@®æ" »yªkn«ç¼Ë¼¶¼g?
§Ún¨ú±o M ³oÓcellsªº¦ì¸m ¥H§Q«á±ªº»yªk¹B§@
¥t Cells(I, J) - Cells(I, (J - 2)) = 0 nÅý¥L¶]¨ì¤£µ¥©ó0 »yªkn«çקï- Sub AAA123()
-
- Dim I%, J%, k% '«Å§iÅܼÆ
- Dim current As Worksheet
- For Each current In Worksheets '¹ï©Ò¦³¬¡¶Ã¯§@³B²z
- current.Select
- 'J = Rows(1).End(xlToRight).Column '¨ú±o²Ä¤@¦C³Ì«á¤@®æ¦³¸ê®ÆªºÈ
- J = 1
- Do While Cells(1, J) <> "°ªµ{" 'º¡¨¬±ø¥ó®É°õ¦æ¥H¤U°Ê§@
- J = J + 1
- Loop
- J = J - 1
- Columns(J).Insert Shift:=xlToRight '¦V¥k´¡¤J¤@Äæ
- k = Range("a:a").End(xlDown).Row '¨ú±oAÄæ³Ì«á¤@®æ¦³¸ê®ÆªºÈ
- Range(Columns(J + 1).Rows(2), Columns(J + 2).Rows(k)).Clear 'Äæ¦ì¼ÆȲM°£
- Cells(1, J) = Now() 'Áä¤J®É¶¡
- For I = 2 To k Step 1 '°j°é³B²z
- If Cells(I, (J - 1)) - Cells(I, (J - 2)) > 0 Then 'Y¬Û´î¤j©ó0 ¶]t¶Ã¼Æ
- Randomize
- Cells(I, J) = Round(((-0.1 - -0.3) * Rnd + -0.3), 1) + Cells(I, (J - 1))
- ElseIf Cells(I, (J - 1)) - Cells(I, (J - 2)) < 0 Then 'Y¬Û´î¤p©ó0 ¶]¥¿¶Ã¼Æ
- Randomize
- Cells(I, J) = Round(((0.3 - 0.1) * Rnd + 0.1), 1) + Cells(I, (J - 1))
- Else
- Cells(I, J) = "" '¥H¤W±ø¥ó¤£¦¨¥ß«h¬°ªÅ¦r¦ê
- End If
- If Cells(I, J) - Cells(I, (J - 2)) = 0 Then
- Call µ¥©ó0
- End If
- With Cells(I, J + 1) '¨D±o¨âªÌ®t²§
- .Formula = "=IF(ISBLANK(" & .Offset(0, -1).Address & "),""""," & .Offset(0, -1).Address & " - " & .Offset(0, -2).Address & ")"
- End With
- With Cells(I, J + 2) '¨D±o°ªµ{
- .Formula = "=IF(ISBLANK(" & .Offset(0, -2).Address & "),""""," & "b" & I & "+(" & .Offset(0, -2).Address & "*0.001)-ROUND(RAND()*0.00005,5))"
- End With
- If Cells(I, J) = "" Then '·í¦¹¬°ªÅ¦r¦ê
- Cells(I, J + 1) = "" '«hÅã¥ÜªÅ¦r¦ê
- Cells(I, J + 2) = ""
- End If
- Next I
- 'Cells(1, J) = Now() 'Áä¤J®É¶¡
- Set current = Nothing 'ÄÀ©ñª«¥óÅܼÆ
- Next current
- MsgBox "Worksheets working Success!!" '°T®§´£¥Ü
- 'Range(Columns(J + 2).Rows(2), Columns(J + 2).Rows(k)).Copy '°ªµ{½Æ»s
- End Sub
- Sub µ¥©ó0()
- Dim I%, J%, k%, M%
- J = 1
- Do While Cells(1, J) <> "°ªµ{" 'º¡¨¬±ø¥ó®É°õ¦æ¥H¤U°Ê§@
- J = J + 1
- Loop
- J = J - 2
- M = Cells(Rows.Count, Columns(J).End(xlDown)).End(xlUp).Rows '³o¦æ¼g¤£¥X¨Ó...
- 'M = Range("J" & 65536).End(xlUp).Rows
- If Cells(M, (J - 1)) - Cells(M, (J - 2)) > 0 Then 'Y¬Û´î¤j©ó0 ¶]t¶Ã¼Æ
- Randomize
- Cells(M, J - 1) = Round(((-0.1 - -0.3) * Rnd + -0.3), 1) + Cells(M, (J - 1))
-
- ElseIf Cells(M, (J - 1)) - Cells(M, (J - 2)) < 0 Then 'Y¬Û´î¤p©ó0 ¶]¥¿¶Ã¼Æ
- Randomize
- Cells(M, J - 1) = Round(((0.3 - 0.1) * Rnd + 0.1), 1) + Cells(M, (J - 1))
- End If
-
- End Sub
½Æ»s¥N½X ¤wªþ¤WÀÉ®×
½Ð¦U¦ì¤j¤j«ü±Ð |