samedi 14 avril 2018

Array of months with ranges

I am currently trying to create an array that includes every month shortened down to 3 letters, etc. "JAN". I'd however like to assign every month with a specific range that can be used to etc. paste values.

I have tried the following to no avail: (Error: Subscript out of range)

Sub Button1_Click()
Dim celltxt As String
celltxt = Worksheets("FH EXPORT").range("A2").Text
Set ws = Worksheets("Report")
Set genRng = ws.range("B2:B10")
Dim MonthName As Variant
MonthName = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

months(1) = ws.range("E2:E10")
months(2) = ws.range("F2:F10")
months(3) = ws.range("G2:E10")
months(4) = ws.range("H2:H10")
months(5) = ws.range("I2:I10")
months(6) = ws.range("J2:J10")
months(7) = ws.range("K2:K10")
months(8) = ws.range("L2:L10")
months(9) = ws.range("M2:M10")
months(10) = ws.range("N2:N10")
months(11) = ws.range("O2:N10")

If InStr(1, celltxt, "JAN") Then
months(1).Value = genRng.Value
ElseIf InStr(1, celltxt, "FEB") Then
months(2).Value = genRng.Value
Else
    MsgBox ("not found")
End If
End Sub

The whole reason for me attempting this is actually to avoid creating an if statement for every single month, also as shown in the code.

Aucun commentaire:

Enregistrer un commentaire