lol (UK not US) ... now that I look at it.. my response DOES look somewhat humorous... perhaps I should've said "why you came to THIS FORUM" rather than "US"...
and hey! I DID say "We've" got a couple of problems here! After all, "shared pain" is less, right? right?? well... maybe... I realize that your file is relatively large. The way you've started (and we've expanded upon) is commonly called a User Defined Function (UDF). yes, if you use this (or any) UDF in many lines, it can be time consuming. primarily because it at times will attempt to "refresh" the entire sheet, causing multiple executions of the macro. however, it COULD be written as a VBA macro that you run manually (somewhat) I routinely execute a macro that processes 100,000 lines. depending on what the macro does, it runs fairly quickly (10-15 seconds). Of course, the macro that updates these records from multiple databases takes significantly longer... I'm not sure if you last post indicates that you continue to have a question, or if WE'VE resolved the immediate need. (the TASK is never complete, the journey continues on!) Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: pawel lupinski <lupins...@yahoo.com> To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> Sent: Wed, December 5, 2012 8:33:05 AM Subject: Re: $$Excel-Macros$$ multiple parameters function VBA Paul, Thanks for reply. Yes I have couple problems :) 1. file is too big to send. 2. I've found solution (secound email) I've tried to resolve problem with the procedure that you've made for me before, but for this instant I've done something different. 3. now I've got different issue I'd like to run this function only for single row (it'll be consuming too much time if I'll be running it for all cells) regards, Pawel PS UK not US :) From: Paul Schreiner <schreiner_p...@att.net> To: excel-macros@googlegroups.com Sent: Wednesday, December 5, 2012 12:50 PM Subject: Re: $$Excel-Macros$$ multiple parameters function VBA Pawel, We've got a couple of problems here. (I guess that's why you came to US, right?) The first problem is going to be that will need help understanding your data. If you only want to check specific columns (C, F, Z, AC) then we can do that. But if you want to look in the entire range, say: from column B to AZ and try to locate the first value less than 0, then we can do that. It would actually be EASIER to check the entire range. next.. What you're trying to do with the function: Function Check(...Vvalue, Vvalue1,....) is pretty "convoluted". TECHNICALLY, you have to have a FIXED number of arguments passed to a function. You can CHOOSE to make some "optional". but you still have to define them: Function Check(Vvalue1, Vvalue2, Vvalue3, optional Vvalue4) Now, if you REALLY want to be able to use a variable number of arguments, the it IS possible to define an array argument, but then you have to enter your cell values as an array, which isn't done simply. --------------- next: I think your sample function was simply an "example" and not what you were intending... I'm not sure if you need some help with loops, but here's some pointers. your "For" loop should look like: For a = 1 To 100 ... Next a you should NOT put a line like a=a+1 within the loop, because the For... statement will increment the counter (a) by 1 for each loop. If you put a=a+1 inside the loop, it will be incremented TWICE. Next, to break out of the loop, you would put "Exit For" not "End If". ------------------ Since you said that using your sample data, you expected a return value of (4), then I assume that you want a COUNT of the number of values, not the COLUMN that contains the value. What I would suggest is a function that looks like: ================================================== Public Function Check(Rng As Range) Dim tRng, vCnt vCnt = 0 For Each tRng In Rng If tRng.Value <> "" Then vCnt = vCnt + 1 If (tRng.Value < 0) Then Exit For End If Next tRng Check = vCnt End Function ================================================== This would need to go into a "standard" module rather than a "sheet" module. In your sample file, you would put in Cell A3: =check(B3:AZ3) let me know if this is what you are looking for. Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- From: pawel lupinski <lupins...@yahoo.com> To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> Sent: Wed, December 5, 2012 6:38:42 AM Subject: $$Excel-Macros$$ multiple parameters function VBA Hi All, I'd like to create function that will check for me if the cell in the row has value below 0. The issue of mine is that lookup value is not cosistly in the same column. so could be like this: I'm looking value in column C then F them Z then AC so there is not real sequence for it. Function Check(...Vvalue, Vvalue1,....) Dim a As Integer '- amount Vvalue that I'd like to check Dim Vvalue As Double For a = 1 To 100 If Vvalue < 0 Then End If a=a+1 End If ActiveCell = a End Function Please see attachement should be more clear. Regards, Pawel-- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
