Array with month

[elmnas]

Hello All VB Members,

I need an array that represent each month,

and if cells(i,"H"),

the value i is always a number between 1 -12

1 = jan
2 = feb

etc.

Could someone help me ?

Thank you in advance

[elmnas]

here is my code so far

For Each sht In ThisWorkbook.Worksheets
mysheetname = sht.Name
If sht.Name Like "Volvo Penta*" Then

Myarr = Split(mysheetname, "_")
myCompany = Myarr(0)
myyearandMonth = Myarr(1)
myyear = Left(myyearandMonth, 4)
mymonth = Right(myyearandMonth, 2)


For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row






'Dim myMArr(12) As String
'myMArr(1) = "Jan"
'myMArr(2) = "Feb"
'myMArr(3) = "Mar"
'myMArr(4) = "Apr"
'myMArr(5) = "Maj"
'myMArr(6) = "Jun"
'myMArr(7) = "Jul"
'myMArr(8) = "Aug"
'myMArr(9) = "Sep"
'myMArr(10) = "Okt"
'myMArr(11) = "Nov"
'myMArr(12) = "Dec"










mynewvar = Trim(Value) = "0"




MsgBox mynewvar






'
' Cells(i, "G") = myyear
' Cells(i, "H") = mymonth
' Cells(i, "D") = myCompany
Next i



End If
Next sht


End Sub

[mancubus]

you can use GetCustomListContents to create an array whose elements are month names.

declare Array as Variant.



Sub Monthnames_Custom_List()

Dim MonthNamesShort() As Variant
Dim MonthNamesLong() As Variant

MonthNamesShort = Application.GetCustomListContents(3)
MonthNamesLong = Application.GetCustomListContents(4)

MsgBox Join(MonthNamesShort, "_")
MsgBox Join(MonthNamesLong, "_")

End Sub


visit:
http://www.snb-vba.eu/VBA_Excel_customlist_en.html

[jonh]

For i = 1 To 12
Debug.Print MonthName(i, True)
Next

[Sixthsense..]

MsgBox Format(DateSerial(2015, Cells(i, "H").Value, 1), "MMM")

[elmnas]

Thank you but I dont get it work,

[elmnas]

Moderator Note: These posts merged from other thread.

Dear VB Members,


I have got this problem,


I trying to sort out from the sheetname Companyname,Year,Month


The name is always written:
xxxxxxx_xxxx_xx
example

COMPANY_2015-05



I have made an array to try to filter the parts of the things.


by using these variables I can grep the parts of the name


see code below:






sub mytest()


For Each sht In ThisWorkbook.Worksheets
mysheetname = sht.Name
If sht.Name Like "Volvo Penta*" Then

Myarr = Split(mysheetname, "_") ' seperate all words

myCompany = Myarr(0)


myyearandMonth = Myarr(1)
myyear = Left(myyearandMonth, 4)
mymonth = Right(myyearandMonth, 2)

For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row



' I put all my code in here






Next i



End If
Next sht



end sub





The problem is, when I can alert the last part of the combination (month)
I always recieve if there is a lower value than 10 I get 05.


so I need to delete the leading zero to get value "5"




Then I want to convert the number to the month number 5
so I recieve in a messagebox in this case (may)


Could someone help me out with a code?




Thank you in advance all VB Members




Cheers

[SamT]

Option Explicit

Sub SamT()

Dim Sht As Worksheet
Dim MySheetName As String
Dim UnderScoreLocation As Long
Dim MyCompany As String
Dim MyDatePart As String
Dim MyYear As String
Dim MyMonth As String
Dim MyMon As String

'For Each Sht In ThisWorkbook.Worksheets
MySheetName = "COMPANY_2015-01" 'Sht.Name
If IsNumeric(Right(MySheetName, 2)) Then
UnderScoreLocation = InStr(MySheetName, "_")
MyCompany = Left(MySheetName, UnderScoreLocation - 1)
MyDatePart = Right(MySheetName, Len(MySheetName) - (UnderScoreLocation + 1))
MyYear = Format(MyDatePart, "YYYY")
MyMonth = Format(MyDatePart, "MMMM")
MyMon = Format(MyDatePart, "MMM")
End If

'Next Sht
'Blah, blah, blah
End Sub



I merged the other thread with this thread because they are so similar.

[jonh]

mymonth = Right("COMPANY_2015-05", 2)
mymonth = MonthName(CLng(mymonth))

[elmnas]

I solved it in this way :)


Sub Companyname()

Dim mysheetname As String
Dim Myarr() As String
Dim myCompany As String
Dim myyearandMonth As String
Dim myyear As String
Dim mymonth As String
Dim myvar As String
Dim thisMonth As Integer
Dim myCorrMonth As String


For Each sht In ThisWorkbook.Worksheets
mysheetname = sht.name
If sht.name Like "Volvo Penta*" Then

Worksheets("Volvo_Statistik").Activate
Myarr = Split(mysheetname, "_")
myCompany = Myarr(0)
myyearandMonth = Myarr(1)
myyear = Left(myyearandMonth, 4)
mymonth = Right(myyearandMonth, 2)


For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
mymonth = Val(Right(myyearandMonth, 2)) ' Remove the leading zero
thisMonth = mymonth
myCorrMonth = MonthName(thisMonth, True)
Cells(i, "G") = myyear
Cells(i, "H") = myCorrMonth
Cells(i, "D") = myCompany
Next i

End If
Next sht


End Sub

[snb]

Sub M_snb()
for each sh in sheets
if left(sh.name,6)="Volvo " then
sn=split(sh.name,"_")
with sheets("Volvo_Statistik").usedrange
.columns(4)=sn(0)
.columns(7)=left(sn(1),4)
.columns(8)=monthname(val(right(sn(1),2))
end with
end if
next
End Sub

Avoid 'Select' and 'Activate' in VBA.