VBA macro loop [Closed]

Registration date
Wednesday November 1, 2017
Last seen
November 1, 2017
 Blocked Profile -
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

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


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

1 reply

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!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!