我一直在嘗試在下面的 vba 腳本上取得成功，但仍然沒有運氣，我的目標是在 (C) 列中查找單元格值，并在單元格值上方插入一個空白行，如果它沒有“點”/“ .”，例如 - 如果單元格 C2 沒有“.” 那么它應該在其上方創建一個空白行
Sub testing()
Dim col As Variant
Dim lr As Long
Dim i As Long
Dim startRow As Long
col = "C"
startRow = 2
lr = Cells(Rows.Count, col).End(xlUp).Row
With ActiveSheet
For i = lr To startRow Step -1
If IsNumeric(Range("E2", "E" & lr).Value) = True Then
.Cells(i 1, col).EntireRow.Insert shift:=xlUp
End If
Next i
End With
End Sub
輸入
期望的輸出
uj5u.com熱心網友回復：
插入發生在末尾的稍微不同的方法：
Sub Tester()
Dim c As Range, rng As Range, ws As Worksheet, v
Set ws = ActiveSheet
For Each c In ws.Range("C2:C" & ws.Cells(Rows.Count, "C").End(xlUp).Row).Cells
v = c.Value
If InStr(v, ".") = 0 And Len(v) > 0 Then 'has a value, with no "." ?
If rng Is Nothing Then 'any previous cells found?
Set rng = c 'start the range with `c`
Else
Set rng = Application.Union(c, rng) 'add `c` to `rng`
End If
End If
Next c
'if found any cells then do the insert
If Not rng Is Nothing Then rng.EntireRow.Insert shift:=xlDown
End Sub
uj5u.com熱心網友回復：
您的代碼中有兩個錯誤：
- 您正在檢查
IsNumeric整個 E 列，直到您當前正在處理的行。但是多個單元格沒有數值。 - 您在包含點的單元格下方插入行。您想在不包含任何點的單元格上方插入行。
它應該是
Sub testing()
Dim col As String
Dim i As Long
Dim startRow As Long
Dim lastRow As Long
col = "C"
startRow = 2
lastRow = Cells(Rows.Count, col).End(xlUp).Row
With ActiveSheet
For i = lastRow To startRow Step -1
If IsNumeric(Range("E" & i).Value) = False Then
.Cells(i, col).EntireRow.Insert shift:=xlDown
End If
Next i
End With
End Sub
您甚至可以取消列 E，因為 VBA 包含一個呼叫的函式，該函式InStr在另一個字串中查找字符或字串，如下所示：
If InStr(Range("C" & i).Value, ".") = 0 Then
' ...
End If
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/447192.html