在vba中从第5个工作表循环到最后一个工作表？
在vba中从第5个工作表循环到最后一个工作表？
提问于 2016-12-02 07:59:11
我有35到40个包含每日股票数据的工作表，我正在尝试计算宏中每个工作表的股票回报。公式是:LN(今天/昨天)，它给出了每天的股票回报。我正在运行下面的代码，但我不知道如何在第五个工作表中启动循环。我的文件夹在前四个工作表中。有人知道这是怎么回事吗？
Sub Macro2()
Dim wb As Workbook
Dim i As Integer
Dim ws As Worksheet
Set wb = ActiveWorkbook
Set ws = Worksheets.Count = 5
For i = 5 To ThisWorkbook.Worksheets.Count
Activeworksheet.Columns("c").ClearContents
Range("C4").Select
ActiveCell.FormulaR1C1 = "=LN(RC[-1]/R[-1]C[-1])"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:C507")
Range("C4:C507").Select
Next i
End Sub回答 2
Stack Overflow用户
发布于 2016-12-02 08:02:14
你应该使用avoid using .Select，因为我相信这对它有贡献。这应该会有帮助：
Sub Macro2()
Dim wb As Workbook
Dim i As Integer
Dim ws As Worksheet
Set wb = ActiveWorkbook
Set ws = Worksheets.Count = 5
For i = 5 To ThisWorkbook.Worksheets.Count
With Worksheets(i)
.Columns("c").ClearContents
.Range("C4").FormulaR1C1 = "=LN(RC[-1]/R[-1]C[-1])"
.Range("C4").AutoFill Destination:=.Range("C4:C507")
Next i
End Sub请注意，您的想法是正确的，以i=5开头的For循环很好，但是您实际上从来没有使用过i。当您使用ActiveSheet时，它(显然？)使用当前处于活动状态的工作表。因此，您的代码只能在该sheet...and上运行，所以无论您有多少次工作表(如果这是有意义的)。
Stack Overflow用户
发布于 2016-12-02 08:30:30
伙计们想明白了。以下代码基于B3:B中的价格和C4:C中的回报
Sub Macro2()
Dim wb As Workbook
Dim i As Integer
Dim ws As Long
Set wb = ActiveWorkbook
For i = 6 To 56
ws = Worksheets(i).Activate
Range("C4").Select
ActiveCell.FormulaR1C1 = "=LN(RC[-1]/R[-1]C[-1])"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:C507")
Range("C4:C507").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "Daily Return"
Range("C:C").Select
Columns("C:C").EntireColumn.AutoFit
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B:B").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Currency"
Next i
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40922562
复制相关文章
相似问题
腾讯云开发者
