Excel VBA：在当前选择中编写公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7186244/
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
Excel VBA: writing formula in current selection
提问by user366121
I want to write the current month based on a referenced cell into the current selection. This is my code but I get the error message: object variable or with block variable not set. I don't know what the problem is - anyone have a clue?
我想将基于引用单元格的当前月份写入当前选择。这是我的代码，但我收到错误消息：未设置对象变量或块变量。我不知道问题是什么-有人有线索吗？
Sub SelectionMonthNames()
Dim Currentrange As Range
For i = 1 To 3
Currentrange = Selection.Address
If i = 1 Then
Currentrange.Formula = "=DATE(YEAR($B);MONTH($B);DAY($B))"
Else
Currentrange.Formula = "=DATE(YEAR($B);MONTH($B)+" & CStr(i - 1) & ";DAY($B))"
End If
Selection.Offset(0, 1).Select
Next i
End Sub
回答by Jandrejc
Try
尝试
Set Currentrange = Selection.Address
Instead of
代替
Currentrange = Selection.Address
EDIT:
编辑：
So, final version of your macro should look like this:
因此，宏的最终版本应如下所示：
Sub SelectionMonthNames()
Dim Currentrange As Range
For i = 1 To 3
Set Currentrange = Selection
If i = 1 Then
Currentrange.Formula = "=DATE(YEAR($B),MONTH($B),DAY($B))"
Else
Currentrange.Formula = "=DATE(YEAR($B),MONTH($B)+" & CStr(i - 1) &",DAY($B))"
End If
Selection.Offset(0, 1).Select
Next i
End Sub
