VBA macro loop

bjj127 1 Posts Wednesday November 1, 2017Registration date November 1, 2017 Last seen - Nov 1, 2017 at 02:22 AM - Latest reply: ac3mark 9977 Posts Monday June 3, 2013Registration dateModeratorStatus July 20, 2018 Last seen
- Nov 1, 2017 at 06:51 PM
Bare with me here, I just started learning VBA code so it may look a little ruff but what i am trying to do is to use a loop to copy and paste each sub-sample into the first nSubperiods columns of the new worksheet, starting on Column B Row 5. So, if the user picks three sub-periods, the copy of the first sub-period should start in cell B5 and contain ten years of monthly returns down Column B; the second sub-period should start in cell C5 and contain the next ten years of monthly returns down Column C; the third sub-period should start in cell D5 and contain the final ten years of monthly returns down Column D. You will need to use Offset to determine the Range for each sub-sample (Offset is a property that helps define the object Range), and the macro should label the sub-periods in Row 1 (Sub-Period 1, Sub- Period 2, and Sub-Period 3 if the user picked three sub-periods).

Here is my code so far and i'm stuck attempting to run the loop

'Enter number of desired non-overlapping periods between 1 and 10
Dim Nsubperiods As Variant

Nsubperiods = InputBox("Please enter desired number of SubPeriods between 1 and 10", "How many SubPeriods do you want to test?")
[K2].Value = Nsubperiods

'Count all monthly oberservations in Column C
Set ws = ThisWorkbook.Sheets("DJIA")

'Dim MonthlyObservations As Long
Dim MonthlyObservations As Integer

CurrentCell = ActiveCell.Row

Cells(CurrentCell, 2).Activate

MonthlyObservations = Range(ActiveCell, ActiveCell.Offset(2, 0).End(xlDown)).Rows.Count
'= Range(ActiveCell, ActiveCell.Offset(2, 2).End(xlDown)).Select
Dim AnnualObservations As Integer

Dim MonthsSubPeriod As Integer
Dim YearsSubPeriod As Integer
Dim ObservedOutliers As Integer
Dim ExpectedOutliers As Integer
Dim Year As Integer
'MonthsSubPeriod = [K2]
Year = 12

Answer1 = MonthlyObservations / Year
Answer2 = MonthlyObservations / [K2]
Answer3 = [L2] / Year


[I2].Value = Answer1
[I2].NumberFormat = "0.00"
[J2].Value = MonthlyObservations
[L2].Value = Answer2
[L2].NumberFormat = "0.00"
[M2].Value = Answer3
[M2].NumberFormat = "0.00"
[N2].Value = ObservedOutliers
[O2].Value = ExpectedOutliers

'Divide MonthlyObservations by year
'Set ws = ThisWorkbook.Sheets("DJIA")

'Dim AnnualObservations As Long

'AnnualObservations = (MonthlyObservations) / (12)
'Set ws = ThisWorkbook.Sheets("DJIA")

Dim YearsInSub As Long

'Create a new worksheet (W with X Periods)
Worksheets.Add.Name = ActiveSheet.Name & "With" & Nsubperiods & "Periods"

ActiveSheet.Name = "SubPeriods"


'Resize cells
Range("A:D").EntireColumn.AutoFit

'Activesheet new name
Set DsubPeriod = ActiveSheet
Set DJIA = Sheet1



Sheets("DJIA").Activate
Range("B2:B34").Select
Selection.Copy
Sheets("SubPeriods").Select
Range("C3").Select
ActiveSheet.Paste




The last bit with Sheets ("DJIA") was an attempt to copy data into the sheet i had just created when a user inputs the desired number of subperiods. I had to change the ActiveSheet.Name = "SubPeriods" because i can't figure out to paste a selection into the worksheets.add.name = activesheet....etc...etc...

Any help would be greatly appreciated!!!

Thank you so much
See more 

Your reply

1 reply

ac3mark 9977 Posts Monday June 3, 2013Registration dateModeratorStatus July 20, 2018 Last seen - Nov 1, 2017 at 06:51 PM
0
Thank you
Can you chop this up into smaller functions, or subroutines, that produce an expected output? Then call each subroutine, so you can troubleshoot (and understand how to pass the variable into the routines that need them). BTW, I didn't notice any loop.

In the case of the sheet name, build another variable like:

thesheetname(0)= ActiveSheet.Name & "With" & Nsubperiods & "Periods"



Try that!

Respond to ac3mark