Loop through part of Array based on User Form selection

[GarC]

Hi

I am building a workbook to compile selected months into quarterly and perhaps semi-annual and annual reports.

I created a user form so the user can select the "Year", the "FirstMonth" and the "LastMonth". This works perfectly.

The full month name is part of each workbook containing information.
I would like to loop through these specific workbooks and extract the same data from each one. I have no problem getting it to loop through all the months or perform the tasks I require, but the part I am finding tricky is limiting it to only loop through the user defined selection of months.

I suspect I should be using For and To but normally only do this with numerical values, not arrays.

Any assistance or link to a similar solution would be appreciated.

[COLOR=#0000ff]Option Explicit[/COLOR]


[COLOR=#0000ff]Sub [/COLOR]QuarterlyCompiler()

[COLOR=#0000ff]Dim [/COLOR]intYear [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]strMonth [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]strLastMonth [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]varMonths [COLOR=#0000ff]As Variant[/COLOR]

[COLOR=#008000]'Show User Form
'On the user form, there are three comboboxes
    '1: Select Year
    '2: Select First Month
    '3: Select Last Month[/COLOR]
frmSelectRangeOfMonths.Show


[COLOR=#008000]'This is where the user form deposits the selections[/COLOR]
         intYear = Application.Sheets("InputBox").Range("A2")
         strMonth = Application.Sheets("InputBox").Range("D19")
         strLastMonth = Application.Sheets("InputBox").Range("E19")


[COLOR=#008000]'Array defined [/COLOR]
varMonths = Array("January", "February", "March", "April", "May", "June", "July", _
         "August", "September", "October", "November", "December")


[COLOR=#008000]'Loop [/COLOR]
         [COLOR=#0000ff]For Each [/COLOR]strMonth [COLOR=#0000ff]In [/COLOR]varMonths

                  Application.DisplayAlerts = False
                  Workbooks.Open "path is here" & intYearNo & "\Monthly Performance " & strMonth & ".xlsx"
          
                 [COLOR=#008000] 'Extraction Code Here[/COLOR]

                  ActiveWorkbook.Close (False) 


         Next strMonth

[COLOR=#008000]'Other Code Here[/COLOR]

[COLOR=#0000ff]
End Sub[/COLOR]

 

[dmt32]

Hi,
see if this update to your code helps:

Option Explicit




Sub QuarterlyCompiler()


Dim intYear As Integer
Dim StartMonth As Integer, EndMonth As Integer
Dim i As Integer


'Show User Form
'On the user form, there are three comboboxes
    '1: Select Year
    '2: Select First Month
    '3: Select Last Month
frmSelectRangeOfMonths.Show




'This is where the user form deposits the selections


    On Error GoTo myerror
    With ThisWorkbook.Sheets("InputBox")
         intYear = .Range("A2")
         'assumes ranges contain valid month names (short or long)
         StartMonth = Month(DateValue("01/" & .Range("D19")))
         EndMonth = Month(DateValue("01/" & .Range("E19")))
    End With
    


    With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    End With
    
        For i = StartMonth To EndMonth
        
                  Workbooks.Open "path is here" & intYearNo & "\Monthly Performance " & MonthName(i, False) & ".xlsx"
          
                  'Extraction Code Here


                  ActiveWorkbook.Close (False)


         Next i


'Other Code Here




myerror:
    With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    End With
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Hope Helpful

Dave

 

[GarC]

Hi Dave

Sorry for delayed response. I have been offline for a few days.

Thanks for your effort. I'll check as soon as possible and let you know how I get

 

[GarC]

Thanks again for your response. It wasn't quite what I was looking for but it taught me something I didn't know and I was able to use that to solve my problem.

On my user form I selected months January to February.

In your code, the variables StartMonth and EndMonth are therefore holding the month numbers "1" & "3". What I was attempting was to get the variables to hold "January" and "March".

However, based on your code, I extracted the month numbers had the code deposit the "i" variable into a cell on a hidden sheet. I then put a VLOOKUP in the adjacent cell which generated the month name from a nearby table. I then used this month name as the required variable which allowed me to loop through the specific file names.

Not the most elegant solution but getting the job done is the main thing. Time to go home!!

 

[dmt32]

Glad you solved problem in way that works for you but little puzzled as MonthName(i,False) Function should return the Month name in way you require?

The StartMonth & EndMonth variables are used in For next Loop to index the required months in manner requested.

Dave