ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] °õ¦æ¬¡­¶Ã¯¤º©Ò¦³¤u§@ªíªº°g«ä

[µo°Ý] °õ¦æ¬¡­¶Ã¯¤º©Ò¦³¤u§@ªíªº°g«ä

¦U¦ì¥ý¶i

¥i§_À°¤p§Ì¬Ý¤@¤U ¥H¤U»yªk ­þ¸Ì¦³¿ù
§Ú­n³B²z¬¡­¶Ã¯²zªº©Ò¦³¤u§@ªí ¥i¬O¥L¥u¯à¦b¦P¤@­Óws¤º¶]"¥Ø«e¤u§@ªíªº¼Æ¶q"ªº¦¸¼Æ  ¨S¿ìªk¤@­Ó¤u§@ªí¶]§¹ ¶]¤U¤@­Ó£» ©Î¬O¦U¦ì¤j¤j¦³§ó¦nªº¼gªk?!

Sub A123()


Dim I%, J%, k%
Dim Current As Worksheet

For Each Current In Worksheets

J = Rows(1).End(xlToRight).Column
J = J - 1
Columns(J).Insert Shift:=xlToRight
   
k = Range("a:a").End(xlDown).Row

For I = 2 To k Step 1
Cells(I, (J + 1)).Formula = "=IF(ISBLANK(OFFSET(0,-1)),"",OFFSET(0,-1)-OFFSET(0,-2))"
Cells(I, (J + 2)).Formula = "=IF(ISBLANK(OFFSET(0,-2)),"",B3+(OFFSET(0,-2)*0.001)-ROUND(RAND()*0.00005,5))"
   
    If Cells(I, (J - 1)) = "" Then
  Cells(I, J) = ""

    ElseIf Cells(I, (J - 1)) - Cells(I, (J - 2)) > 0 Then
Randomize
   Cells(I, J) = Round(((-0.1 - -0.4) * Rnd + -0.4), 1) + Cells(I, (J - 1))
   
   
    ElseIf Cells(I, (J - 1)) - Cells(I, (J - 2)) < 0 Then
Randomize
   Cells(I, J) = Round(((0.3 - 0.1) * Rnd + 0.1), 1) + Cells(I, (J - 1))

    End If
Next I
    Cells(1, J) = Now()
    MsgBox Current.Name
Next

End Sub

¦^´_ 1# alex520188
  1. Sub A123()
  2. Dim I%, J%, k%
  3. Dim Current As Worksheet
  4. For Each Current In Worksheets
  5.     With Current
  6.         J = .Rows(1).End(xlToRight).Column
  7.         J = J - 1
  8.         .Columns(J).Insert Shift:=xlToRight
  9.         k = .Range("a:a").End(xlDown).Row
  10.         For I = 2 To k Step 1
  11.             .Cells(I, (J + 1)).Formula = "=IF(ISBLANK(OFFSET(0,-1)),"",OFFSET(0,-1)-OFFSET(0,-2))"
  12.             .Cells(I, (J + 2)).Formula = "=IF(ISBLANK(OFFSET(0,-2)),"",B3+(OFFSET(0,-2)*0.001)-ROUND(RAND()*0.00005,5))"
  13.             If .Cells(I, (J - 1)) = "" Then
  14.                 .Cells(I, J) = ""
  15.             ElseIf .Cells(I, (J - 1)) - .Cells(I, (J - 2)) > 0 Then
  16.                 Randomize
  17.                 .Cells(I, J) = Round(((-0.1 - -0.4) * Rnd + -0.4), 1) + .Cells(I, (J - 1))
  18.             ElseIf .Cells(I, (J - 1)) - .Cells(I, (J - 2)) < 0 Then
  19.                 Randomize
  20.                 .Cells(I, J) = Round(((0.3 - 0.1) * Rnd + 0.1), 1) + .Cells(I, (J - 1))
  21.             End If
  22.         Next I
  23.         .Cells(1, J) = Now()
  24.         MsgBox .Name
  25.     End With
  26. Next
  27. End Sub
½Æ»s¥N½X

TOP

ª©¤j ÁÂÁ§Aªº¦^ÂÐ
¤£¹L¥[¤F³o¦æ With Current ¤§«á
¤@¼Ë¤]¤£¯à¶]©Ò¦³ªº¬¡­¶Ã¯

¥t¥~§Ú·Q°Ý¤@¤U
Cells(I, (J + 1)).Formula = "=IF(ISBLANK(OFFSET(0,-1)),"",OFFSET(0,-1)-OFFSET(0,-2))"
Cells(I, (J + 2)).Formula = "=IF(ISBLANK(OFFSET(0,-2)),"",B3+(OFFSET(0,-2)*0.001)-ROUND(RAND()*0.00005,5))"

³o¨â¦æcode ­n«ç§ï¼g ¦]¬°§Ú¬O±qexecl¤½¦¡ª½±µ¶K¤Wªº °õ¦æ¤W·|¥d¦í~"~

TOP

¦^´_ 3# alex520188
¤@¼Ë¤]¤£¯à¶]©Ò¦³ªº¬¡­¶Ã¯
­n¶]©Ò¦³ªº¬¡­¶Ã¯¶Ü?
For Each Wo In Workbooks      
          For Each Current In Wo.Worksheets
              
                With .Cells(I, J + 1)
                        .Formula = "=IF(ISBLANK(" & .Offset(0, -1).Address & "),""""," & .Offset(0, -1).Address & " - " & .Offset(0, -2).Address & ")"
               End With
               With .Cells(I, J + 2)
                    .Formula = "=IF(ISBLANK(" & .Offset(0, -2).Address & "),"""",B3+(" & .Offset(0, -2).Address & "*0.001)-ROUND(RAND()*0.00005,5))"              
               End With
      

       Next
Next

TOP

ª©¤j ÁÂÁ§Aªº¦^ÂÐ
³o¨Çcode¤w¸g¯à¥¿±`¨Ï¥Î
¤£¹L¤p§ÌÁÙ¬O¦³°ÝÃD·Q½Ð±Ð
"=IF(ISBLANK(" & .Offset(0, -1).Address & "),""""," & .Offset(0, -1).Address & " - " & .Offset(0, -2).Address & ")"
³o¤@¦ê & .Offset(0, -1).Address &  ¬°¦ó­n³o¼Ë¼g §Ú¤£À´¥Lªº»yªk

For Each Current In Wo.Worksheets
¨ä¤¤ Wo.Worksheets  ¬O¤°»ò·N«ä?

TOP

¦^´_ 4# GBKEE


ª©¤j ÁÂÁ§Aªº¦^ÂÐ
³o¨Çcode¤w¸g¯à¥¿±`¨Ï¥Î
¤£¹L¤p§ÌÁÙ¬O¦³°ÝÃD·Q½Ð±Ð
"=IF(ISBLANK(" & .Offset(0, -1).Address & "),""""," & .Offset(0, -1).Address & " - " & .Offset(0, -2).Address & ")"
³o¤@¦ê ("& .Offset(0, -1).Address & ") ¬°¦ó­n³o¼Ë¼g §Ú¤£À´¥Lªº»yªk

For Each Current In Wo.Worksheets
¨ä¤¤ Wo.Worksheets  ¬O¤°»ò·N«ä?

TOP

¦^´_ 6# alex520188
Offset ¬O  Range ª«¥óªºÄÝ©Ê ¡A¥Î¥H¥Nªí¬Y­Ó«ü©w°Ï°ì¥H¥~ªº½d³ò¡C°ßŪ¡C
With Range("a1")
    MsgBox    .Offset( 0, 0).Address
     MsgBox    .Offset(5).Address
    MsgBox    .Offset(5 , 2).Address
End  With

§A¬O§_ª¾¤F¸Ñ  For Each Current In Worksheets     ¤u§@ªí¶°¦Xª«¥ó
¦P²z
For Each Wo In Workbooks          ¬¡­¶Ã¯¶°¦Xª«¥ó

TOP

¦^´_ 7# GBKEE


    ÁÂÁª©¤j
¤p§Ì¤w¤F¸Ñ

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD