VBA Code for displaying monthly sales in LISTBOX.

[bryskie]

help pls. i cant move on my project.
kept on encountering this error, " Unable to get the EDate property of the WorksheetFunction class"

Here's the code.

Private Sub UserForm_Initialize()

Me.COMBOBOXYear.Value = Format(Date, "YYYY")
Me.COMBOBOXMonth.Value = Format(Date, "MMMM")
For a = 0 To 5
Me.COMBOBOXYear.AddItem Format(Date, "YYYY") - a
Next a
For b = 0 To 11
c = Application.WorksheetFunction.EoMonth("1" & "/" & "January" & "/" & Me.COMBOBOXYear.Value, b)
Me.COMBOBOXMonth.AddItem Format(c, "MMMM")
Next b

End Sub

Private Sub COMBOBOXYear_Change()


Me.LBOXSales.Clear
Me.LBOXSales.AddItem Sheet10.Cells(1, 1).Value
For a = 1 To 4
Me.LBOXSales.List(LBOXSales.ListCount - 1, a) = Sheet10.Cells(1, a + 1).Value
Next a
Me.LBOXSales.Selected(0) = True
For i = 2 To Application.WorksheetFunction.CountA(Sheet10.Range("A:A"))
b = Application.WorksheetFunction.EDate("1" & "/" & Me.COMBOBOXMonth.Value & "/" & Me.COMBOBOXYear, 0)
c = Application.WorksheetFunction.EoMonth("1" & "/" & Me.COMBOBOXMonth.Value & "/" & Me.COMBOBOXYear, 0)
If Sheet10.Cells(i, 1).Value >= CDate(b) And Sheet10.Cells(i, 1).Value <= CDate(c) Then
Me.LBOXSales.AddItem Sheet10.Cells(i, 2).Value
For d = 1 To 4
Me.LBOXSales.List(LBOXSales.ListCount - 1, d) = Sheet10.Cells(i, d + 1).Value
Next d
End If
Next i

End Sub

Thank you!

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

help pls. i cant move on my project.
kept on encountering this error, " Unable to get the EDate property of the WorksheetFunction class"

Here's the code.

Thank you!

The Microsoft Excel EDATE function adds a specified number of months to a date and returns the result as a serial date.


In your function you are not adding months,[FONT=&quot]

[/FONT][/COLOR]b = Application.WorksheetFunction.EDate("1" & "/" & Me.COMBOBOXMonth.Value & "/" & Me.COMBOBOXYear, 0)[COLOR=#333333][FONT=&quot]

[/FONT]Then you only need to create a date, you can use the following:

[COLOR=#0000ff]b = DateSerial(COMBOBOXYear, COMBOBOXMonth + 0, 1)[/COLOR]
c = Application.WorksheetFunction.EoMonth("1" & "/" & Me.COMBOBOXMonth.Value & "/" & Me.COMBOBOXYear, 0)
If Sheet10.Cells(i, 1).Value >=[COLOR=#0000ff] b[/COLOR] And Sheet10.Cells(i, 1).Value <= CDate(c) Then

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

The Microsoft Excel EDATE function adds a specified number of months to a date and returns the result as a serial date.


In your function you are not adding months,

b = Application.WorksheetFunction.EDate("1" & "/" & Me.COMBOBOXMonth.Value & "/" & Me.COMBOBOXYear, 0)

Then you only need to create a date, you can use the following:

[COLOR=#0000ff]b = DateSerial(COMBOBOXYear, COMBOBOXMonth + 0, 1)[/COLOR]
c = Application.WorksheetFunction.EoMonth("1" & "/" & Me.COMBOBOXMonth.Value & "/" & Me.COMBOBOXYear, 0)
If Sheet10.Cells(i, 1).Value >=[COLOR=#0000ff] b[/COLOR] And Sheet10.Cells(i, 1).Value <= CDate(c) Then

sir, Good Day..

As i follow your code, i still encounter error regarding. not adding months.

hope we can find the solution.

all i just want is displaying the monthly sales in a listbox, what ever data chosen on the combobox.

Thank you. much appreciated

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

sir, Good Day..

As i follow your code, i still encounter error regarding. not adding months.
hope we can find the solution.

all i just want is displaying the monthly sales in a listbox, what ever data chosen on the combobox.

Thank you. much appreciated

What error message does the code send and in what line of code does it stop?
What version of excel do you have?

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

What error message does the code send and in what line of code does it stop?
What version of excel do you have?

its on my reply sir. i used 2007
"Runtime error '13: type mismatch"
line is in DateSerial code

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

its on my reply sir. i used 2007
"Runtime error '13: type mismatch"
line is in DateSerial code

Check that you have numerical values ​​in the combos

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

Good Day sir.

I tried pm you but i got an email that my inbox is full the truth us i dont event have any message in my inbox.
anyways, i just want to show you my form.

my combobox has a value of month in words not numerical.

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

Good Day sir.

I tried pm you but i got an email that my inbox is full the truth us i dont event have any message in my inbox.
anyways, i just want to show you my form.

my combobox has a value of month in words not numerical.

Edate or DateSerial work with numerical values, you must convert your text to a number, or simply perform a test with a numeric month, only to obtain the date and generate your mail. You must advance your tests step by step.

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

Edate or DateSerial work with numerical values, you must convert your text to a number, or simply perform a test with a numeric month, only to obtain the date and generate your mail. You must advance your tests step by step.

sir i cant improvise a code cause i have a limited knowledge about the excel coding i just watch to youtube for tutorials and cant find a same problem as i have, please dont lose patience with me.

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

sir i cant improvise a code cause i have a limited knowledge about the excel coding i just watch to youtube for tutorials and cant find a same problem as i have, please dont lose patience with me.

I'm sorry, but I do not understand how your data is or how your controls are. You could upload a file with your userform. On a sheet you explain step by step what you want to do.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.