Function Creation

[Guest]

If A1 > 0 AND SUM(B1:L1) = 0 then A13 = Jan
If B1 > 0 AND SUM(C1:L1) = 0 then A13 = Feb
If D1 > 0 AND SUM(E1:L1) = 0 then A13 = March
etc.etc.

Rather than just using nested Ifs which is cumbersome I am trying:

Function Month(Integer)

Select Case Integer
Case a1>0,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks

 

[Bernie Deitrick]

Teresa,

If you want Jan, Feb, Mar, Apr, etc, then Array enter, using
Ctrl-Shift-Enter, this formula in cell A13:

=TEXT(DATE(1,MAX((A1:L1>0)*(COLUMN(A1:L1))),1),"mmm")

or if you want January, February, March, etc. then Array enter (again,
using Ctrl-Shift-Enter)

=TEXT(DATE(1,MAX((A1:K1>0)*(COLUMN(A1:K1))),1),"mmmm")

Also, it is a bad idea to write functions that have the same name as Excel
functions. (MONTH is a valid Excel function)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sorry, the second formula should have also had L1 where it currently has K1.

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Assumes M1 will be empty:

Dim rng as Range, cell as Range, rng1 as Range
Dim i as Long, sMonth as String
set rng = Range("A1:L1")
for each cell in rng
set rng1 = Range(cell.offset(0,1), range("M1"))
if cell.Value > 0 and application.sum(rng1) = 0 then
i = cell.Column
end if
Next
sMonth = format(DateSerial(year(date),i,1),"mmm")

if you want full month names, then change mmm to mmmm

If you know there will be no zero values in months before the current month
you can simplify to:

Dim rng as Range, cell as Range
Dim i as Long, sMonth as String
set rng = Range("A1:L1")
for each cell in rng
if cell.Value > 0 then
i = cell.Column
else
exit for
end if
Next
sMonth = format(DateSerial(year(date),i,1),"mmm")

 

[Guest]

Thanks a lot Tom - thats excellent

Assumes M1 will be empty:

Dim rng as Range, cell as Range, rng1 as Range
Dim i as Long, sMonth as String
set rng = Range("A1:L1")
for each cell in rng
set rng1 = Range(cell.offset(0,1), range("M1"))
if cell.Value > 0 and application.sum(rng1) = 0 then
i = cell.Column
end if
Next
sMonth = format(DateSerial(year(date),i,1),"mmm")

if you want full month names, then change mmm to mmmm

If you know there will be no zero values in months before the current month
you can simplify to:

Dim rng as Range, cell as Range
Dim i as Long, sMonth as String
set rng = Range("A1:L1")
for each cell in rng
if cell.Value > 0 then
i = cell.Column
else
exit for
end if
Next
sMonth = format(DateSerial(year(date),i,1),"mmm")

 

[Tom Ogilvy]

I see you had an earlier posting of this and responded positively to a
worksheet function solution. Since you posted in programming the indication
would be code as the desired solution and your example showed a code
solution. If that isn't what you wanted, it would be better to indicate
what you want - code, worksheet function or that you are interesed in both.

Might help people to avoid wasting their time.

 

[Guest]

Tom - understood, going forward I'll bear this in mind and apologies for any
time wasting

 

[Guest]

Tom - understood, going forward I'll bear this in mind and apologies for any
time wasting