View Full Version : [SOLVED:] Array with month
elmnas
06-02-2015, 11:45 PM
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
06-03-2015, 12:33 AM
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
06-03-2015, 12:58 AM
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
For i = 1 To 12
Debug.Print MonthName(i, True)
Next
Sixthsense..
06-03-2015, 03:22 AM
MsgBox Format(DateSerial(2015, Cells(i, "H").Value, 1), "MMM")
elmnas
06-03-2015, 04:43 AM
Thank you but I dont get it work,
elmnas
06-03-2015, 04:53 AM
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
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.
mymonth = Right("COMPANY_2015-05", 2)
mymonth = MonthName(CLng(mymonth))
elmnas
06-04-2015, 12:46 AM
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.