- 帖子
- 31
- 主題
- 12
- 精華
- 0
- 積分
- 69
- 點名
- 0
- 作業系統
- WIN XP
- 軟體版本
- OFFICE 2003
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2011-8-19
- 最後登錄
- 2016-1-2
|
各位先進好 小弟有問題想請教
在他程式運算過程中 偵測運算出來的結果"那一格" 語法要怎樣撰寫?
我要取得 M 這個cells的位置 以利後面的語法運作
另 Cells(I, J) - Cells(I, (J - 2)) = 0 要讓他跑到不等於0 語法要怎修改- Sub AAA123()
-
- Dim I%, J%, k% '宣告變數
- Dim current As Worksheet
- For Each current In Worksheets '對所有活頁簿作處理
- current.Select
- 'J = Rows(1).End(xlToRight).Column '取得第一列最後一格有資料的值
- J = 1
- Do While Cells(1, J) <> "高程" '滿足條件時執行以下動作
- J = J + 1
- Loop
- J = J - 1
- Columns(J).Insert Shift:=xlToRight '向右插入一欄
- k = Range("a:a").End(xlDown).Row '取得A欄最後一格有資料的值
- Range(Columns(J + 1).Rows(2), Columns(J + 2).Rows(k)).Clear '欄位數值清除
- Cells(1, J) = Now() '鍵入時間
- For I = 2 To k Step 1 '迴圈處理
- If Cells(I, (J - 1)) - Cells(I, (J - 2)) > 0 Then '若相減大於0 跑負亂數
- 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 '若相減小於0 跑正亂數
- Randomize
- Cells(I, J) = Round(((0.3 - 0.1) * Rnd + 0.1), 1) + Cells(I, (J - 1))
- Else
- Cells(I, J) = "" '以上條件不成立則為空字串
- End If
- If Cells(I, J) - Cells(I, (J - 2)) = 0 Then
- Call 等於0
- End If
- 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 & "),""""," & "b" & I & "+(" & .Offset(0, -2).Address & "*0.001)-ROUND(RAND()*0.00005,5))"
- End With
- If Cells(I, J) = "" Then '當此為空字串
- Cells(I, J + 1) = "" '則顯示空字串
- Cells(I, J + 2) = ""
- End If
- Next I
- 'Cells(1, J) = Now() '鍵入時間
- Set current = Nothing '釋放物件變數
- Next current
- MsgBox "Worksheets working Success!!" '訊息提示
- 'Range(Columns(J + 2).Rows(2), Columns(J + 2).Rows(k)).Copy '高程複製
- End Sub
- Sub 等於0()
- Dim I%, J%, k%, M%
- J = 1
- Do While Cells(1, J) <> "高程" '滿足條件時執行以下動作
- J = J + 1
- Loop
- J = J - 2
- M = Cells(Rows.Count, Columns(J).End(xlDown)).End(xlUp).Rows '這行寫不出來...
- 'M = Range("J" & 65536).End(xlUp).Rows
- If Cells(M, (J - 1)) - Cells(M, (J - 2)) > 0 Then '若相減大於0 跑負亂數
- 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 '若相減小於0 跑正亂數
- Randomize
- Cells(M, J - 1) = Round(((0.3 - 0.1) * Rnd + 0.1), 1) + Cells(M, (J - 1))
- End If
-
- End Sub
請各位大大指教 |
|
發表於 2011-9-5 18:59
| 
發表於 2011-9-7 16:04
| 
