0

## 儲存格導回VBA並以代數方式執行程序

1.Dim arrCode(X)
2.X = Worksheets("Sheet1").Range("F1").Value
3.M = Worksheets("Sheet1").Range("F2").Value
4.N = Worksheets("Sheet1").Range("F3").Value
5.R = Worksheets("Sheet1").Range("F4").Value

``````
Public Sub 啟動表單()
startRow = 3
startCol = 3
nIdx = 0
For nI = 1 To M
If nI = M Then nLoop = R Else nLoop = N
For nJ = 1 To nLoop
nIdx = nIdx + 1
arrCode(nIdx) = Format(nI, "00#") & Format(nJ, "00#")
Next nJ
Next nI
For nI = startRow To startRow + X - 1
scode = Cells(nI, startCol)
If (scode <> "") Then
Call useCode(scode)
End If
Next nI
For nI = startRow To startRow + X - 1
scode = Cells(nI, startCol)
If (scode = "") Then
Cells(nI, startCol) = getCode
End If
Next nI
End Sub
Function useCode(ByVal code As String)
For nI = 1 To X
If (arrCode(nI) = code) Then
arrCode(nI) = ""
Exit For
End If
Next nI
End Function
Function getCode() As String
For nI = 1 To X
If (arrCode(nI) <> "") Then
getCode = "'" & arrCode(nI)
arrCode(nI) = ""
Exit For
End If
Next nI
End Function
``````

peterzxcv iT邦新手 5 級 ‧ 2020-05-19 08:35:53 檢舉

### 1 個回答

0
kevin6372003
iT邦新手 5 級 ‧ 2020-05-19 14:22:08

``````Sub 巨集1()
x = Range("A1")  'A1 = 指定儲存格
Range("B1") = x  '然後當代數用
End Sub
``````

peterzxcv iT邦新手 5 級 ‧ 2020-05-19 19:47:17 檢舉

Dim arrCode(X)
(刪)
****編譯錯誤: 必須是常數運算函示

Dim arrCode() As Integer '先定義一個動態陣列
X = Worksheets("Sheet1").Range("F1").Value '產生一個代數
ReDim arrCode(X) '再拿代數定義陣列

peterzxcv iT邦新手 5 級 ‧ 2020-05-21 19:45:10 檢舉