Looping from 5th to final worksheet in vba?

Question

I have 35 to 40 worksheets with daily stock data and I am trying to calculate the stock returns for each worksheet in a macro. The formula is: LN(Today/Yesterday) which gives the daily stock return. I am running the code below but I cannot figure out how to start the loop in the 5th worksheet. My portfolio is in the first four worksheets. Anyone know how this would work?

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

score 1 · Answer 1 · answered Dec 02 '16 at 00:02

You should avoid using .Select as that's contributing to it I believe. This should help:

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

Note how you have the right idea, the For loop starting with i = 5 is good, but you then never actually use i. When you use ActiveSheet, it (obviously?) uses whatever the current sheet that's active is. Thus, your code was only going to run on that sheet...and it'd so so however many times you have worksheets (if that makes sense).

@NickGarcia does it work for you? If so, mind marking as answer (click the check mark left of the post) — BruceWayne, Dec 02 '16 at 01:18

score 0 · Answer 2 · answered Dec 02 '16 at 00:30

Figured it out guys. Heres code based on prices in B3:B and returns in 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

Note my data stops at worksheet 56 which was originally in the final worksheet. — Nick Garcia, Dec 02 '16 at 00:31

Looping from 5th to final worksheet in vba?

2 Answers2