作為 VBA 編程的初學者，我很難弄清楚問題是什么。當值按順序增加時，代碼完美運行，但是當陣列中存在不相似的值時，我會收到錯誤訊息。當加載有問題的值時，會發生錯誤Run time error 9, subscript out of range并在代碼中突出顯示這一行代碼sequenceArr(counter) = arr(i 1)的主要任務是對長數字字串進行簡短的標記，并在不同的字串之間進行分隔。
例如：我有盒子 ID 號：M0054515, M0054516, M0054517, M0054620, M0054621, M0054622, M0054751, M0054752,M0054753
當我運行宏時，我得到這樣的輸出結果：
M0054515-517 // M0054620-622 // M0054751-753。
但是當我在 ID 號碼系列的中間有一些亂數時，我得到一個錯誤...... M0046552，M0047396, M0047399, M0047802,M0047803 而不是分隔值，我收到運行時錯誤訊息。
如果有人想幫助解決問題，此鏈接是我的書的示例版本。
對于這項作業，我使用了這個論壇的另一個成員很久以前撰寫的代碼
Sub Generate()
Dim ws As Worksheet
Dim arr() As String, result As String, letter As String, cellValue As String, tempLastElement As String
Dim lastColumn As Long, counter As Long
Dim firstColumn As Integer, targetRow As Integer, i As Integer
Set ws = Worksheets("KreirajRadniNalog")
firstColumn = 1
targetRow = 1
lastColumn = ws.Range(ws.Cells(targetRow, firstColumn), ws.Cells(targetRow, Columns.Count).End(xlToLeft).Columns).Count
ReDim arr(1 To lastColumn - firstColumn 1)
letter = Left(ws.Cells(targetRow, firstColumn).Value, 1)
For i = 1 To UBound(arr)
cellValue = ws.Cells(targetRow, i).Value
arr(i) = Right(cellValue, Len(cellValue) - 1)
Next i
ReDim sequenceArr(1 To UBound(arr))
sequenceArr(1) = arr(1)
counter = 2
For i = 1 To UBound(arr) - 1
If CLng(arr(i)) 1 = CLng(arr(i 1)) Then
tempLastElement = arr(i 1)
sequenceArr(counter) = tempLastElement
Else
counter = counter 1
sequenceArr(counter) = arr(i 1) '<<<this line here is highlighted
counter = counter 1
End If
Next
ReDim Preserve sequenceArr(1 To counter)
result = ""
counter = 1
For i = 1 To UBound(sequenceArr) - 1
If counter > UBound(sequenceArr) Then Exit For
If result = "" Then
result = letter & sequenceArr(counter) & "-" & Right(sequenceArr(counter 1), 3)
counter = counter 2
Else
result = result & "//" & letter & sequenceArr(counter) & "-" & Right(sequenceArr(counter 1), 3)
counter = counter 2
End If
Next
ws.Range("C4").Value = result
End Sub
uj5u.com熱心網友回復：
請嘗試下一個更新的代碼。既然你沒有回答我的澄清問題，我（只）希望我能推斷出你想要完成的......
Sub Generate()
Dim ws As Worksheet
Dim arr, sequenceArr, letter As String, cellValue As String, tempLastElement As String
Dim lastColumn As Long, counter As Long, firstColumn As Long, targetRow As Integer, i As Long, j As Long
Set ws = Worksheets("KreirajRadniNalog")
firstColumn = 1: targetRow = 1
lastColumn = ws.Range(ws.Cells(targetRow, firstColumn), ws.Cells(targetRow, Columns.Count).End(xlToLeft).Columns).Count
ReDim arr(1 To lastColumn - firstColumn 1)
letter = Left(ws.Cells(targetRow, firstColumn).Value, 1)
For i = 1 To UBound(arr)
cellValue = ws.Cells(targetRow, i).Value
arr(i) = Right(cellValue, Len(cellValue) - 1)
Next i
ReDim sequenceArr(1 To UBound(arr))
counter = 1
For i = 1 To UBound(arr) - 1
If CLng(arr(i)) 1 = CLng(arr(i 1)) Then
For j = 0 To UBound(arr)
If i j 1 > UBound(arr) Then Exit For
If CLng(arr(i)) j 1 = CLng(arr(i 1 j)) Then
tempLastElement = arr(i 1 j)
Else
Exit For
End If
Next j
sequenceArr(counter) = arr(i) & "-" & Right(tempLastElement, 3)
counter = counter 1: i = i j
Else
sequenceArr(counter) = arr(i): counter = counter 1
End If
Next
ReDim Preserve sequenceArr(1 To counter - 1)
ws.Range("C4").Value = letter & Join(sequenceArr, "//" & letter)
MsgBox "Success!"
End Sub
更緊湊的版本，使用基于 0 的陣列：
Sub Generate2()
Dim ws As Worksheet
Dim arr, sequenceArr, letter As String, cellValue As String, tempLastElement As String
Dim lastColumn As Long, counter As Long, firstColumn As Long, targetRow As Integer, i As Long, j As Long
Set ws = Worksheets("KreirajRadniNalog")
firstColumn = 1: targetRow = 1
lastColumn = ws.Range(ws.Cells(targetRow, firstColumn), ws.Cells(targetRow, Columns.Count).End(xlToLeft).Columns).Count
letter = Left(ws.Cells(targetRow, firstColumn).Value, 1)
With Application
arr = .Transpose(.Transpose(ws.Range(ws.Cells(targetRow, firstColumn), ws.Cells(targetRow, lastColumn)).Value))
End With
arr(1) = Mid(arr(1), 2)
arr = Split(Join(arr, "|"), "|" & letter)
ReDim sequenceArr(UBound(arr))
counter = 0
For i = 0 To UBound(arr) - 1
If CLng(arr(i)) 1 = CLng(arr(i 1)) Then
For j = 0 To UBound(arr)
If i j 1 > UBound(arr) Then Exit For
If CLng(arr(i)) j 1 = CLng(arr(i 1 j)) Then
tempLastElement = arr(i 1 j)
Else
Exit For
End If
Next j
sequenceArr(counter) = arr(i) & "-" & Right(tempLastElement, 3)
counter = counter 1: i = i j
Else
sequenceArr(counter) = arr(i): counter = counter 1
End If
Next
ReDim Preserve sequenceArr(1 To counter)
ws.Range("C4").Value = letter & Join(sequenceArr, "//" & letter)
MsgBox "Success!"
End Sub
uj5u.com熱心網友回復：
您的代碼的問題在這里
Else
counter = counter 1
sequenceArr(counter) = arr(i 1) '<<<this line here is highlighted
counter = counter 1
End If
因為對于每個數字，計數器都會增加兩次，因此超過了陣列大小。但是你并不真正需要陣列
Sub Generate()
Dim ws As Worksheet, arr
Dim lastColumn As Long, letter As String, tmp As String
Dim result As String, i As Long, m As Long, n As Long
Set ws = Worksheets("KreirajRadniNalog")
Const firstColumn = 1
Const targetRow = 1
lastColumn = ws.Cells(targetRow, Columns.Count).End(xlToLeft).Column
arr = ws.Cells(targetRow, 1).Resize(, lastColumn)
result = arr(1, 1)
m = Mid(arr(1, 1), 2)
For i = 2 To UBound(arr, 2)
n = Mid(arr(1, i), 2)
If n = m 1 Then
tmp = "-" & Right(Val(Mid(arr(1, i), 2)), 3)
Else
result = result & tmp & "//" & arr(1, i)
tmp = ""
End If
m = n
Next
result = result & tmp
ws.Range("C4").Value = result
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/349782.html
下一篇：Excel格式代碼