将“1”添加到范围内的所有单元格

I'm trying to add a unit of 1 to many cells in a range. Below is where I'm at and I keep getting a type mismatch error:

我正在尝试在一个范围内添加1到多个单元格的单位。下面是我在的地方,我不断收到类型不匹配错误:

Dim r As Range, cell As Range

Set r = Range("D2:E1000")

For Each cell In r
    If cell.Value > 0 Then
        cell.Value = cell.Value + 1
    End If
Next

4 个解决方案

#1

How about:

Sub marine()
    Dim r As Range, cell As Range

    Set r = Range("D2:E1000")

    For Each cell In r
        If IsNumeric(cell.Value) Then
            If cell.Value > 0 Then
                cell.Value = cell.Value + 1
            End If
        Else
            MsgBox "Cell " & cell.Address(0, 0) & " does not have a number"
            Exit Sub
        End If
    Next
End Sub

#2

Here's another way

这是另一种方式

Sub Sandwich()

    Dim rTemp As Range
    Dim rTarget As Range
    Dim sNumFmt As String

    'Define the ranges
    Set rTemp = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0)
    Set rTarget = Sheet2.Range("D2:E1000")

    'store a temporary value and the current number format
    rTemp.Value = 1
    sNumFmt = rTarget.Cells(1).NumberFormat

    'copy and paste special-add
    rTemp.Copy
    rTarget.PasteSpecial , xlPasteSpecialOperationAdd

    'get rid of the temp and reapply the format
    rTemp.ClearContents
    rTarget.NumberFormat = sNumFmt

End Sub

#3

Stopping at OK, I'm trying to add a unit of "1" to many cells in a range. the easiest way is often without VBA or code. Just insert 1 somewhere on the spreadsheet, copy that cell, select the range of interest and Paste Special with Operation Add. The 1 can then be deleted.

在OK,我正在尝试向一个范围内的许多单元格添加“1”单位。最简单的方法通常是没有VBA或代码。只需在电子表格的某处插入1,复制该单元格,选择感兴趣的范围,然后使用操作添加选择性粘贴。然后可以删除1。

#4

Dim r As Range, cell As Range

Set r = Range("D2:E1000")

For Each cell In r
    If cell > 0 Then
        cell.Value = cell.Value + 1
    End If
Next

Is Working for me , perhaps you want IF cell = 0 ??? Waiting for your comments to see if i anwsered your question.

为我工作,也许你想要IF cell = 0 ???等待你的评论,看看我是否提出了你的问题。

#1