Excel 2007 VBA 选择单元格范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13921121/
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 2007 VBA to select range of cells
提问by Desert Spider
I am attempting to select a range of cells. I have done this before but am having trouble with the syntax.
我正在尝试选择一系列单元格。我以前做过这个，但语法有问题。
Sub ChgDateX()
Range("A41").Select
Do
If ActiveCell.Value = "Last Updated" Then
mydate = ActiveCell.Offset(-40, 0).Value
'Cells(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 9)).Select
ActiveCell.Offset(0, 1).Value = mydate
ActiveCell.Offset(0, 2).Value = mydate
ActiveCell.Offset(0, 3).Value = mydate
ActiveCell.Offset(0, 4).Value = mydate
ActiveCell.Offset(0, 5).Value = mydate
ActiveCell.Offset(0, 6).Value = mydate
ActiveCell.Offset(0, 7).Value = mydate
ActiveCell.Offset(0, 8).Value = mydate
ActiveCell.Offset(0, 9).Value = mydate
ActiveCell.EntireRow.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.Offset(1, 0).Select
Else: ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = "" & ActiveCell.Offset(-3, 0).Value = ""
End Sub
I am trying to get away from the individual offset = mydate type coding.
我试图摆脱单个 offset = mydate 类型编码。
回答by Joseph
Couple of things:
几件事：
This code:
这段代码：
Loop Until ActiveCell.Value = "" & ActiveCell.Offset(-3, 0).Value = ""
Doesn't work as expected because the correct operator you're looking for is Andand not &
无法按预期工作，因为您正在寻找的正确运算符是And而不是&
You don't have to "Select" anything. You can just put a reference to a cell in a variable (see my code).
您不必“选择”任何东西。您可以在变量中放置对单元格的引用（请参阅我的代码）。
Also, since you are always moving down to the next cell in the loop, you can put that outside of the IFstatement.
此外，由于您总是向下移动到循环中的下一个单元格，因此您可以将其放在IF语句之外。
Based on your code I think you're looking for something like this:
根据您的代码，我认为您正在寻找这样的东西：
Option Explicit
Sub test()
Dim r As Range
Dim myDate As Date
Set r = Range("A41")
Do
If (r.Value = "Last Updated") Then
myDate = r.Offset(-40, 0).Value
With Range(r.Offset(0, 1), r.Offset(0, 9))
.Value = myDate
.NumberFormat = "m/d/yyyy"
End With
End If
Set r = r.Offset(1, 0)
Loop Until r.Value = vbNullString And r.Offset(-3, 0).Value = ""
End Sub
回答by John Bustos
Try this:
尝试这个：
ActiveCell.Range("B1:J1").Value = MyDate
回答by Scott Holtzman
How about this ... with 1 note. I wouldn't advise the looping like this. Perhaps you can filter on Column A <> ""or something, then just loop through the visible cells? Hard to say without knowing what you are doing.
这个怎么样……有1个音符。我不建议像这样循环。也许您可以过滤Column A <> ""或其他内容，然后循环浏览可见单元格？很难说不知道你在做什么。
Option Explicit
Sub ChgDateX()
Range("A41").Select
Do
If ActiveCell.Value = "Last Updated" Then
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 9)).Value = ActiveCell.Offset(-40, 0).Value
ActiveCell.EntireRow.NumberFormat = "m/d/yyyy"
End If
ActiveCell.Offset(1).Select
Loop Until ActiveCell.Value = "" & ActiveCell.Offset(-3, 0).Value = ""
End Sub
