作為對我之前得到答復的問題的補充，它幫助我以超快的速度完成了專案，我現在有一個類似的專案，原始代碼已經運行并且運行良好，但是，我有兩個包含以下內容的列：
K 列 - ws 和服務器的名稱及其 IP 地址以逗號分隔 L 列 - 只是 IP 地址
在每批名稱和 IP 或只是 IP 中，由于各種原因，我不希望在最終輸出中出現一些范圍。
遺憾的是，我無法給出字串的真實示例，但以下是您可能會發現的示例：
K - Mike [10.6.3.4], Mike1 [10.2.3.4], Mike3 [10.165.75.3].......（不同數量的 L - 10.6.3.4, 10.2.3.4, 10.165.75.3...... …………
L 中的IP 與K 中的IP 相同，但順序不一定相同。
這沒有關系。
我撰寫了以下子程式，它遍歷 excel ws 中的所有行，讀入字串，然后寫出沒有我不想要的范圍的相同字串（你會看到那些是 10.6. 和 192.168.
如您所見，我為一行中的第一個字串撰寫了一個回圈，然后根據列為同一行中的第二個字串撰寫了第二個回圈，我清除了訓練逗號（我不知道如何設定新陣列將保存“干凈”字串的末尾長度，因此我得到了一些必須清理的額外逗號），然后我轉到下一行直到結束。
它作業正常。
我只是覺得它可以做得更快、更干凈，而且可以在一個回圈中而不是兩個回圈中完成，而且可能不會得到需要清理的額外逗號。
我很感激任何人可能有的任何評論或修復。
同樣，它作業正常，但如果您理解我的意思，它會感覺“不干凈”。
'Sub to remove unwanted IP ranges from both columns
Sub CleanNamesIPsfromList()
Const Col_Names = "J"
Const Col_IPs = "K"
Const ROW_NamesIPs = 5 'First line of Names and IP's
Dim wb As Workbook
Dim wsMain As Worksheet
Dim arHoldNames() As String, arHoldIPs() As String
Dim arPutNames() As String, arPutIPs() As String
Dim strHoldNames As String, strHoldIPs As String
Dim txt106 As String, txt192168 As String
Dim rn As Long, rhn As Long, ri As Long, rhi As Long, lastrowN As Long, lastrowI As Long, i As Long
txt106 = "10.6."
txt192168 = "192.168."
Set wb = ActiveWorkbook
With wb
Set wsMain = .Sheets("Main")
End With
With wsMain
lastrowN = .Cells(.Rows.Count, Col_Names).End(xlUp).Row
lastrowI = .Cells(.Rows.Count, Col_IPs).End(xlUp).Row
If lastrowN < ROW_NamesIPs Or lastrowI < ROW_NamesIPs Then
MsgBox "No text found in Columns " & Col_Names & " " & Col_IPs, vbCritical
Exit Sub
End If
'Run through whole ws row by row in both columns
For i = ROW_NamesIPs To lastrowN
rhn = 0
'Load list of names into string holder
strHoldNames = .Cells(i, Col_Names).Value
'Split the string into the holding array based on the comma
arHoldNames = Split(strHoldNames, ",")
'Loop through the array to find those names not containg unwanted data
ReDim arPutNames(0 To UBound(arHoldNames))
For rn = 0 To UBound(arHoldNames)
If InStr(1, arHoldNames(rn), txt106, vbTextCompare) > 0 Or InStr(1, arHoldNames(rn), txt192168, vbTextCompare) > 0 Then
'If it contains unwanted string, increment rn to next array cell and move on
rn = rn 1
Else
'I want this string so put in new array holder and increment array counter
arPutNames(rhn) = arHoldNames(rn)
rhn = rhn 1
End If
Next 'Loop through array holding origional string
'I have what I want, so put it back in relevant cell in ws
.Cells(i, Col_Names).Value = Join(arPutNames, ",")
'Remove all trailing commas
While Right(.Cells(i, Col_Names).Value, 1) = ","
.Cells(i, Col_Names).Value = Left(.Cells(i, Col_Names).Value, Len(.Cells(i, Col_Names).Value) - 1)
Wend
rhi = 0
'Load list of names into string holder
strHoldIPs = .Cells(i, Col_IPs).Value
'Split the string into the holding array based on the comma
arHoldIPs = Split(strHoldIPs, ",")
'Loop through the array to find those names not containg unwanted data
ReDim arPutIPs(0 To UBound(arHoldIPs))
For ri = 0 To UBound(arHoldIPs)
If InStr(1, arHoldIPs(ri), txt106, vbTextCompare) > 0 Or InStr(1, arHoldIPs(ri), txt192168, vbTextCompare) > 0 Then
'If it contains unwanted string, increment ri to next array cell and move on
ri = ri 1
Else
'I want this string so put in new array holder and increment array counter
arPutIPs(rhi) = arHoldIPs(ri)
rhi = rhi 1
End If
Next 'Loop through array holding origional string
'I have what I want, so put it back in relevant cell in ws
.Cells(i, Col_IPs).Value = Join(arPutIPs, ",")
'Remove all trailing commas
While Right(.Cells(i, Col_IPs).Value, 1) = ","
.Cells(i, Col_IPs).Value = Left(.Cells(i, Col_IPs).Value, Len(.Cells(i, Col_IPs).Value) - 1)
Wend
Next 'Move to next row and repeat untill end
End With 'End main
End Sub
新代碼
好的，基于將所有資料讀入陣列，我寫了這一段代碼（當然是從betters那里借來的）。
Dim Lastcell As Range
Set LastCell = .Range("K5:L5").End(xlDown)
'capture all of the data at once with a range-array copy
Dim arHoldAllNames As String, ArHoldAllIPs As String
arHoldAllNames = .Range("K5", LastCell).Value
ArHoldAllIPs = .Range("L5", LastCell).Value
for i = 0 to UBound(arHoldAllNames, 1)
'Code for Names
Next
for i = 0 to UBound(ArHoldAllIPs, 1)
'Code for IPs
Next
temp = 0
For i = 5 To LastCell
.Cells(i, Col_Names).Value = arHoldAllNames(temp)
.Cells(i, Col_IPs).Value = ArHoldAllIPs(temp)
i = i 1
temp = temp 1
Next
你們都是這個意思嗎？
此外，我看到我可以將一系列資料讀入陣列，這無疑加快了速度。
所以這將是這段代碼：
arHoldAllNames = .Range("K5", LastCell).Value
arHoldAllIPs = .Range("L5", LastCell).Value
然后我可以扭轉它并執行以下操作：
.Range("K5", LastCell).Value = arHoldAllNames
.Range("L5", LastCell).Value = arHoldAllIPs
并讓 ws 中的所有單元格與陣列中的內容一起傳播？
還是我還需要做 For 回圈：
temp = 0
For i = 5 To LastCell
.Cells(i, Col_Names).Value = arHoldAllNames(temp)
.Cells(i, Col_IPs).Value = ArHoldAllIPs(temp)
i = i 1
temp = temp 1
Next
uj5u.com熱心網友回復：
以下是如何使用范圍陣列復制的示例：
'Sub to remove unwanted IP ranges from both columns
Sub CleanNamesIPsfromList()
Const Col_Names = "J"
Const Col_IPs = "K"
Const ROW_NamesIPs = 5 'First line of Names and IP's
Dim wb As Workbook
Dim wsMain As Worksheet
Dim arHoldNames() As String, arHoldIPs() As String
Dim arPutNames() As String, arPutIPs() As String
Dim strHoldNames As String, strHoldIPs As String
Dim txt106 As String, txt192168 As String
Dim rn As Long, rhn As Long, ri As Long, rhi As Long, lastrowN As Long, lastrowI As Long, i As Long
txt106 = "10.6."
txt192168 = "192.168."
Set wb = ActiveWorkbook
With wb
Set wsMain = .Sheets("Main")
End With
Dim NamCel As Variant
Dim IpCal As Variant
With wsMain
lastrowN = .Cells(.Rows.Count, Col_Names).End(xlUp).Row
lastrowI = .Cells(.Rows.Count, Col_IPs).End(xlUp).Row
If lastrowN < ROW_NamesIPs Or lastrowI < ROW_NamesIPs Then
MsgBox "No text found in Columns " & Col_Names & " " & Col_IPs, vbCritical
Exit Sub
End If
' Range-array copy the data into VBA
NamCel = .Range(Col_Names & "1:" & Col_Names & lastrowN)
IpCel = .Range(Col_IPs & "1:" & Col_IPs & lastrowI)
'Run through whole ws row by row in both columns
For i = ROW_NamesIPs To lastrowN
rhn = 0
'Load list of names into string holder
'strHoldNames = .Cells(i, Col_Names).Value
strHoldNames = NamCel(i, 1)
'Split the string into the holding array based on the comma
arHoldNames = Split(strHoldNames, ",")
'Loop through the array to find those names not containg unwanted data
ReDim arPutNames(0 To UBound(arHoldNames))
For rn = 0 To UBound(arHoldNames)
If InStr(1, arHoldNames(rn), txt106, vbTextCompare) > 0 Or InStr(1, arHoldNames(rn), txt192168, vbTextCompare) > 0 Then
'If it contains unwanted string, increment rn to next array cell and move on
rn = rn 1
Else
'I want this string so put in new array holder and increment array counter
arPutNames(rhn) = arHoldNames(rn)
rhn = rhn 1
End If
Next 'Loop through array holding origional string
'I have what I want, so put it back in relevant cell in ws
'.Cells(i, Col_Names).Value = Join(arPutNames, ",")
NamCel(i, 1) = Join(arPutNames, ",")
'Remove all trailing commas
While Right(NamCel(i, 1), 1) = ","
NamCel(i, 1) = Left(NamCel(i, 1), Len(NamCel(i, 1)) - 1)
Wend
rhi = 0
'Load list of names into string holder
strHoldIPs = IpCel(i, 1)
'Split the string into the holding array based on the comma
arHoldIPs = Split(strHoldIPs, ",")
'Loop through the array to find those names not containg unwanted data
ReDim arPutIPs(0 To UBound(arHoldIPs))
For ri = 0 To UBound(arHoldIPs)
If InStr(1, arHoldIPs(ri), txt106, vbTextCompare) > 0 Or InStr(1, arHoldIPs(ri), txt192168, vbTextCompare) > 0 Then
'If it contains unwanted string, increment ri to next array cell and move on
ri = ri 1
Else
'I want this string so put in new array holder and increment array counter
arPutIPs(rhi) = arHoldIPs(ri)
rhi = rhi 1
End If
Next 'Loop through array holding origional string
'I have what I want, so put it back in relevant cell in ws
IpCel(i, 1) = Join(arPutIPs, ",")
'Remove all trailing commas
While Right(.Cells(i, Col_IPs).Value, 1) = ","
IpCel(i, 1) = Left(IpCel(i, 1), Len(IpCel(i, 1)) - 1)
Wend
Next 'Move to next row and repeat untill end
' Range-array copy the data back out to Excel
.Range(Col_Names & "1:" & Col_Names & lastrowN) = NamCel
.Range(Col_IPs & "1:" & Col_IPs & lastrowI) = IpCel
End With 'End main
End Sub
uj5u.com熱心網友回復：
好的。
根據給出的思路，以及RBarryYoung 寫的代碼幫助，并做了一個簡單的更改，以擺脫從主陣列讀取字串到小作業陣列時出現的下標超出范圍錯誤，代碼如下。
'Sub to remove unwanted IP ranges from both columns
Sub CleanNamesIPsfromList()
Const Col_Names = "J"
Const Col_IPs = "K"
Const StrtRow = 5 'First line of Names and IP's
Dim wb As Workbook
Dim wsMain As Worksheet
Dim arHoldNames() As String, arHoldIPs() As String
Dim arPutNames() As String, arPutIPs() As String
Dim strHoldNames As String, strHoldIPs As String
Dim txt106 As String, txt192168 As String
Dim rn As Long, rhn As Long, ri As Long, rhi As Long, lastrowN As Long, lastrowI As Long, i As Long, RlLstRw As Long
Dim NamCel As Variant, IpCal As Variant
txt106 = "10.6."
txt192168 = "192.168."
Set wb = ActiveWorkbook
With wb
Set wsMain = .Sheets("Main")
End With
With wsMain
lastrowN = .Cells(.Rows.Count, Col_Names).End(xlUp).Row
lastrowI = .Cells(.Rows.Count, Col_IPs).End(xlUp).Row
If lastrowN < StrtRow Or lastrowI < StrtRow Then
MsgBox "No text found in Columns " & Col_Names & " " & Col_IPs, vbCritical
Exit Sub
End If
' Range-array copy the data into VBA
NamCel = .Range(Col_Names & StrtRow & ":" & Col_Names & lastrowN)
IpCel = .Range(Col_IPs & StrtRow & ":" & Col_IPs & lastrowI)
'Run through whole ws row by row in both columns
RlLstRw = lastrowN - 4
For i = 1 To RlLstRw
rhn = 0
'Load list of names into string holder
'strHoldNames = NamCel(i, 1)
strHoldNames = NamCel(i, 1)
'Split the string into the holding array based on the comma
arHoldNames = Split(strHoldNames, ",")
'Loop through the array to find those names not containg unwanted data
ReDim arPutNames(0 To UBound(arHoldNames))
For rn = 0 To UBound(arHoldNames)
If InStr(1, arHoldNames(rn), txt106, vbTextCompare) > 0 Or InStr(1, arHoldNames(rn), txt192168, vbTextCompare) > 0 Then
'If it contains unwanted string, increment rn to next array cell and move on
rn = rn 1
Else
'I want this string so put in new array holder and increment array counter
arPutNames(rhn) = arHoldNames(rn)
rhn = rhn 1
End If
Next 'Loop through array holding origional string
'I have what I want, so put it back in relevant cell in ws
NamCel(i, 1) = Join(arPutNames, ",")
'Remove all trailing commas
While Right(NamCel(i, 1), 1) = ","
NamCel(i, 1) = Left(NamCel(i, 1), Len(NamCel(i, 1)) - 1)
Wend
rhi = 0
'Load list of names into string holder
strHoldIPs = IpCel(i, 1)
'Split the string into the holding array based on the comma
arHoldIPs = Split(strHoldIPs, ",")
'Loop through the array to find those names not containg unwanted data
ReDim arPutIPs(0 To UBound(arHoldIPs))
For ri = 0 To UBound(arHoldIPs)
If InStr(1, arHoldIPs(ri), txt106, vbTextCompare) > 0 Or InStr(1, arHoldIPs(ri), txt192168, vbTextCompare) > 0 Then
'If it contains unwanted string, increment ri to next array cell and move on
ri = ri 1
Else
'I want this string so put in new array holder and increment array counter
arPutIPs(rhi) = arHoldIPs(ri)
rhi = rhi 1
End If
Next 'Loop through array holding origional string
'I have what I want, so put it back in relevant cell in ws
IpCel(i, 1) = Join(arPutIPs, ",")
'Remove all trailing commas
While Right(.Cells(i, Col_IPs).Value, 1) = ","
IpCel(i, 1) = Left(IpCel(i, 1), Len(IpCel(i, 1)) - 1)
Wend
Next 'Move to next row and repeat untill end
' Range-array copy the data back out to Excel
.Range(Col_Names & StrtRow & ":" & Col_Names & lastrowN) = NamCel
.Range(Col_IPs & StrtRow & ":" & Col_IPs & lastrowI) = IpCel
End With 'End main
End Sub
謝謝大家。一如既往的大幫助。
該腳本在我的筆記本電腦上運行速度提高了大約 120 倍，有 8,323 行（完成所有操作需要 2 秒和一點時間）。所以在我的 ws 上它會更快。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/377961.html
上一篇：java替代使用嵌套for回圈