VBA Code vs Public Function
 

I have the following example data:

Month1 / Qty 1 / Qty 2 / Qty 3 / Qty 4 / Field A
Month 2 / Qty 1 / Qty 2 / Qty 3 / Qty 4 / Field A
Month3/ Qty 1 / Qty 2 / Qty 3 / Qty 4 / Field A
Month 4 / Qty 1 / Qty 2 / Qty 3 / Qty 4 / Field A

The user can select a quarter "start month." I need to set "Field A" to a
unique variable for all months that fall in the quarter.

So, if Month 1 is the Quarter start month, Field A would read "x" for Month
1, Month 2 and month 3.

If Month 2 is the Quarter Start month, Field A would read "x" for Month 2,
Month 3 and Month 4.

I currently am using a public function, but it errors out a lot when people
have more than one excel file open at a time.

Any assistance would be greatly appreciated.

Thanks,

MTinsley

VBA Code vs Public Function
 

Hi,

Post your code, and other people will help you
--
Regards,

Halim



"MTinsley" wrote:

I have the following example data:

Month1 / Qty 1 / Qty 2 / Qty 3 / Qty 4 / Field A
Month 2 / Qty 1 / Qty 2 / Qty 3 / Qty 4 / Field A
Month3/ Qty 1 / Qty 2 / Qty 3 / Qty 4 / Field A
Month 4 / Qty 1 / Qty 2 / Qty 3 / Qty 4 / Field A

The user can select a quarter "start month." I need to set "Field A" to a
unique variable for all months that fall in the quarter.

So, if Month 1 is the Quarter start month, Field A would read "x" for Month
1, Month 2 and month 3.

If Month 2 is the Quarter Start month, Field A would read "x" for Month 2,
Month 3 and Month 4.

I currently am using a public function, but it errors out a lot when people
have more than one excel file open at a time.

Any assistance would be greatly appreciated.

Thanks,

MTinsley

VBA Code vs Public Function
 

Attached is my code for my function. I tried to make it a private Static
Function, but it still errors out if users open up an additional excel file
while they have this one open. After the user has made their selection of
quarter start month, there is a formula in all the data for all the months
that calls the DateSelect and sets the field to 1. Then I sum the data using
multiple criteria. (See below)
=IF((SUMIF($AZ:$AZ,$A13&"1",$AU:$AU))=0,"",(SUMIF( $AZ:$AZ,$A13&"1",$AU:$AU)))

My ultimate goal is to get rid of all the formulas in the worksheet and do
it all in VB.

Thanks,

Meg




Private Static Function DateSelect(Date2 As Date)

'This is a function that allows the quarters to be calculated.

Date1 = Sheets("Corp Data").Cells(6, 65)

Month1 = Month(Date1)
Year1 = Year(Date1)
Month2 = Month(Date2)
Year2 = Year(Date2)

If Month1 = "1" Then
If (Month2 = "1" Or Month2 = "2" Or Month2 = "3") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "2" Then
If (Month2 = "2" Or Month2 = "3" Or Month2 = "4") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "3" Then
If (Month2 = "3" Or Month2 = "4" Or Month2 = "5") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "4" Then
If (Month2 = "4" Or Month2 = "5" Or Month2 = "6") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "5" Then
If (Month2 = "5" Or Month2 = "6" Or Month2 = "7") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "6" Then
If (Month2 = "6" Or Month2 = "7" Or Month2 = "8") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "7" Then
If (Month2 = "7" Or Month2 = "8" Or Month2 = "9") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "8" Then
If (Month2 = "8" Or Month2 = "9" Or Month2 = "10") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "9" Then
If (Month2 = "9" Or Month2 = "10" Or Month2 = "11") And Year1 = Year2 Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "10" Then
If (Month2 = "10" Or Month2 = "11" Or Month2 = "12") And Year1 = Year2
Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "11" Then
Year3 = Year1 + 1
If ((Month2 = "11" Or Month2 = "12") And Year1 = Year2) Or (Month2 = €˜1
And Year3 = Year2) Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

If Month1 = "12" Then
Year3 = Year1 + 1
If ((Month2 = "12") And Year1 = Year2) Or ((Month2 = €˜1 Or Month2 =
€˜2) And Year3 = Year2) Then
DateSelect = 1
Else
DateSelect = 0
End If
End If

Application.Volatile True

End Function