Function Creation
 

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 a10,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Function Creation
 

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:L10)*(COLUMN(A1:L1))),1),"mm m")

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

=TEXT(DATE(1,MAX((A1:K10)*(COLUMN(A1:K1))),1),"mm mm")

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


"teresa" wrote in message
...
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 a10,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Function Creation
 

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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:L10)*(COLUMN(A1:L1))),1),"mm m")

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

=TEXT(DATE(1,MAX((A1:K10)*(COLUMN(A1:K1))),1),"mm mm")

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


"teresa" wrote in message
...
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 a10,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Function Creation
 

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")

--
Regards,
Tom Ogilvy



"teresa" wrote in message
...
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 a10,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Function Creation
 

Thanks a lot Tom - thats excellent

"Tom Ogilvy" wrote:

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")

--
Regards,
Tom Ogilvy



"teresa" wrote in message
...
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 a10,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Function Creation
 

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.

--
Thanks,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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")

--
Regards,
Tom Ogilvy



"teresa" wrote in message
...
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 a10,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Function Creation
 

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

"Tom Ogilvy" wrote:

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.

--
Thanks,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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")

--
Regards,
Tom Ogilvy



"teresa" wrote in message
...
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 a10,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Function Creation
 

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


"Tom Ogilvy" wrote:

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.

--
Thanks,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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")

--
Regards,
Tom Ogilvy



"teresa" wrote in message
...
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 a10,sum(B1:L1)=0
Month = "Jan"

etc....

help is much appreciated - thanks