我試圖從我的檔案中取出第一列（除標題外的所有行）并洗掉冒號字符左側的文本，但我從 VBA 收到 400 錯誤。我不知道這段代碼有什么問題。
例如 A2（以及 A 列中的后續單元格）如下所示：
Sub cleanLoginTime()
Dim cell As Range
Dim MyRange As Range
Dim tmp As String
LastRow = Cells(Rows.Count, 1).End(xlUp)
Set MyRange = ActiveSheet.Range("A2:A" & LastRow) 'this is your range of data
For Each cell In MyRange.Cells
tmp = cell.Value
'output n - 1 characters from the right
cell.Value = Right(tmp, Len(tmp) - 21)
Next
End Sub
uj5u.com熱心網友回復：
從列中的字串中洗掉左側
Sub CleanLoginTime()
Const FindString As String = ":"
Dim FindStringLength As Long: FindStringLength = Len(FindString)
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim rg As Range
Set rg = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
Dim cell As Range
Dim FindStringPosition As Long
Dim CellString As String
For Each cell In rg.Cells
CellString = CStr(cell.Value)
FindStringPosition = InStr(CellString, FindString)
If FindStringPosition > 0 Then ' string found
cell.Value = Right(CellString, Len(CellString) _
- FindStringPosition - FindStringLength 1)
'Else ' string not found; do nothing
End If
Next cell
End Sub
- 為了使其更高效（更快），您可以引入一個陣列 (
Data)，以最低限度地訪問作業表。
Sub CleanLoginTimeArray()
Const FindString As String = ":"
Dim FindStringLength As Long: FindStringLength = Len(FindString)
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim rg As Range
Set rg = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
Dim Data() As Variant: Data = rg.Value ' works only if more than one cell!
Dim r As Long
Dim FindStringPosition As Long
Dim CellString As String
For r = 1 To UBound(Data, 1)
CellString = CStr(Data(r, 1))
FindStringPosition = InStr(CellString, FindString)
If FindStringPosition > 0 Then ' string found
Data(r, 1) = Right(CellString, Len(CellString) _
- FindStringPosition - FindStringLength 1)
'Else ' string not found; do nothing
End If
Next r
rg.Value = Data
End Sub
uj5u.com熱心網友回復：
我收到運行時錯誤“13”，因為您需要 .row in
lastrow = Cells(Rows.Count, 1).End(xlUp).row
您的任何單元格的長度是否小于 21？
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/528690.html
標籤：擅长vba