Select month to fill an Array

[msquared99]

I have this macro that I want the user to be able to select a month to help fill an array. So, when they run the macro it will allow them to select a month from say a dropdown menu.

Here is the line of code that I would like the selection to complete. Relpace March with the user selected month:

ColHeads = Array("Client Name", "Service", "Start Date", "Rep", "First Year Comm %", "Residual Commission", "March IC Revenue", "March Commission")

Thanks for your help!

 

[Luke M]

Does this help?

Sub Example()
Dim colHeads As Variant
Dim i As Long
Dim newMonth As String

colHeads = Array("Client Name", "Service", "Start Date", "Rep", "First Year Comm %", "Residual Commission", "March IC Revenue", "March Commission")
newMonth = InputBox("What is the new month?", "New Month", "March")

'Do substitition
For i = LBound(colHeads) To UBound(colHeads)
    colHeads(i) = Replace(colHeads(i), "March", newMonth)
Next i

'Check our work
MsgBox colHeads(6)
End Sub

 

[msquared99]

Thanks for the reply Luke.

How would I use something like this?

mBefore = Format(DateAdd("m", -1, Date), "mmmm")

That way it would eliminate the user selecting a month. I will always want to use the previous month anyway. So it is May and I want to use April.

 

[msquared99]

BIBGO! Luke you gave me an idea. Here is the code:

Sub Example()
 Dim colHeads As Variant
 Dim i As Long
 Dim newMonth As String
 Dim mBefore As Date
 
 mBefore = DateAdd("m", -1, Now)
 d = Format(mBefore, "mmmm")
 
colHeads = Array("Client Name", "Service", "Start Date", "Rep", "First Year Comm %", "Residual Commission", d & " IC Revenue", d & " Commission")
'Check our work
 MsgBox colHeads(6)
 End Sub

Thanks for helping!