declare multiple variables
hi,
is there any way i can declare multiple variables at the same time without creating tons of different functions. for example, lets say i have: Function zum() As Variant Application.Volatile zum = 0 For Each w In Worksheets zum = zum + w.Range("C2").Value Next End Function i want to be able to modify it to something like: Function zum() As Variant Application.Volatile zum = 0 For Each w In Worksheets zum = zum + w.Range("C2").Value zum2 = zum2 + w.Range("D2").Value zum3 = zum3 + w.Range("F2").Value Next End Function how should I modify it? thanks in advance, geebee |
declare multiple variables
It looks like you are doing a UDF which by default can only return one value.
How about something more like this... Function zum(byval Target as range) As double dim w as worksheet Application.Volatile zum = 0 For Each w In Worksheets zum = zum + w.Range(Target.Address).Value Next w End Function Use it like =Zum(C2) or =Zum(D2) -- HTH... Jim Thomlinson "geebee" wrote: hi, is there any way i can declare multiple variables at the same time without creating tons of different functions. for example, lets say i have: Function zum() As Variant Application.Volatile zum = 0 For Each w In Worksheets zum = zum + w.Range("C2").Value Next End Function i want to be able to modify it to something like: Function zum() As Variant Application.Volatile zum = 0 For Each w In Worksheets zum = zum + w.Range("C2").Value zum2 = zum2 + w.Range("D2").Value zum3 = zum3 + w.Range("F2").Value Next End Function how should I modify it? thanks in advance, geebee |
declare multiple variables
Hi,
replace the line: zum = zum + w.Range(Target.Address).Value with this line: zum = zum + worksheetfunction.sum(w.Range(Target.Address) It should work Much fun, Heimo "geebee" (noSPAMs) schrieb im Newsbeitrag ... hi, this works good. but now i would like to know how i can add hem. lts say for example that cell C1 has a formula of =zum(E12) in it. and cell C2 has a formula of =zum(E13) in it. and so forth. i would like to know how i can add these zums up in cell C5.i tried to use =sum(C1:C4) but its not working. thanks in advance, geebee "Jim Thomlinson" wrote: It looks like you are doing a UDF which by default can only return one value. How about something more like this... Function zum(byval Target as range) As double dim w as worksheet Application.Volatile zum = 0 For Each w In Worksheets zum = zum + w.Range(Target.Address).Value Next w End Function Use it like =Zum(C2) or =Zum(D2) -- HTH... Jim Thomlinson "geebee" wrote: hi, is there any way i can declare multiple variables at the same time without creating tons of different functions. for example, lets say i have: Function zum() As Variant Application.Volatile zum = 0 For Each w In Worksheets zum = zum + w.Range("C2").Value Next End Function i want to be able to modify it to something like: Function zum() As Variant Application.Volatile zum = 0 For Each w In Worksheets zum = zum + w.Range("C2").Value zum2 = zum2 + w.Range("D2").Value zum3 = zum3 + w.Range("F2").Value Next End Function how should I modify it? thanks in advance, geebee |
All times are GMT +1. The time now is 03:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com