Report

VBA macro loop

Ask a question bjj127 1Posts Wednesday November 1, 2017Registration date November 1, 2017 Last seen - Last answered on Nov 1, 2017 at 06:51 PM by ac3mark
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
Helpful
+0
plus moins
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!

Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!