| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Shortcut Key Creation | Excel Discussion (Misc queries) | |||
| Spreadsheet Function creation? | Excel Worksheet Functions | |||
| Syntax for Info or Cell function for xl. file creation date | Excel Worksheet Functions | |||
| Function Library Creation | Excel Programming | |||
| Matrix Multiplication Function creation | Excel Programming | |||
Hybrid Mode
