vba 将“1”添加到范围内的所有单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19821151/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add '1' to all cells in a range
提问by ackdaddy
I'm trying to add a unit of 1to 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
回答by Dick Kusleika
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
回答by pnuts
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 1somewhere on the spreadsheet, copy that cell, select the range of interest and Paste Special with Operation Add. The 1can then be deleted.
停在OK, I'm trying to add a unit of "1" to many cells in a range.最简单的方式通常是没有 VBA 或代码。只需1在电子表格的某处插入，复制该单元格，选择感兴趣的范围并使用操作添加粘贴特殊内容。该1则可以将其删除。
回答by Gary's Student
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
回答by Stephenloky
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 ??? 等待您的评论，看看我是否回答了您的问题。
