Posts by flee01

  • Indefinite Number Of Arguments In A Udf

    I would like to build a UDF that takes 2 or more arguements. Sometimes it could be as many as 10 arguments. I would like to avoid using the optional statement. I remember reading a way to pass them in using something like the params method but cant find it again.


    Thanks in advance![hr]*[/hr] Auto Merged Post;[dl]*[/dl]Never mind. I just found it in the help. I thought I would check one more time & there it was.


    In excel 2003 the search you can use to find the info is: Understanding Parameter Arrays.

  • Counting Number Of Multiple Entries

    Re: Counting Number Of Multiple Entries


    I have been using this array formula for a while. It finds anything that occurs more than once. The # it returns is the # of times more than once the data occurs.


    =SUM(IF($H2=$H$2:$H$500,1,0))-1


    $H$2:$H$500 is the array you want to search. $H2 is the line you are on currently. The only way this works is if it is on each line that has a value you want to check for duplicates.

  • Copy/paste Formulas Only

    Re: Copy/paste Formulas Only


    I don't know if I am reading this right, but I think I can help you get where you are heading. You want to be able to update just cells that are formulas, right? If so, here is a UDF that I wrote to mark them as formula or value. Create a column for use only as a sorter. Then put the formulaOrNot UDF in it referencing the column you want to update. I sort by the column that I use the UDF in. That allows me to group ones that are formulas with ones that are formulas for the copy / paste update.



    It can take either a cell reference or a range reference.


    HTH's

  • Code Does Not Find A Match

    Re: Code Does Not Find A Match



    The code is definitely solid. I use this type of thing in my main spreadsheet on upwards of 2000 lines of code without problems. The real question is "How is it dodgy?".

  • Code Does Not Find A Match

    Re: Code Does Not Find A Match


    Using the cells method without .value or .text will lead to haphazzard results. To tell which on you should use a handy tool is highlighting the full test (like " Cells(theRow, 1) <> Val(TextBox4) ") then hitting shift f9. Then if that is false and you think it should be true highlight each side of the test to see what the value of each part is and why it is throwing an unexpected result.


    Mybest guess is you need to update the below:


    Code
    Cells(theRow, 1)[COLOR="Red"].Text [/COLOR]<> Val(TextBox4)


    HTH's.

  • Code Execution Has Been Interrupted

    I keep geting the error msg "Code execution has been interrupted" after I add a do loop to my code. Keep in mind I have about 60 modules in this project and about 40 additions to events. So this is a heavily coded workbook. Also noteworthy is the fact that I can cancel the error by encapsulating the previously working and notibly unrelated code in:


    Code
    application.enablecancelkey = xldisabled


(my unrelated, previously working code)


application.enablecancelkey = xlinterupt


    If that is not enough, the code that is represented by (my unrelated, previously working code even in other subroutines) can change at a moments notice and then I have to encapsulate that to get error to stop.



    The error comes up with 4 buttons:


    [continue][end][debug][help]


    When I hit continue many times the code just finishes it's normal functions. But why does it keep happening. Below is a copy of the module. Look for the "do until" loop...


  • User Defined Events?

    Re: User Defined Events?


    Ur right. I read that copy / paste do not fire worksheet_change after it did not work in my master spreadsheet. After reading your reply showing it does work in xl2003 I tried it in my original test spreadsheet. The event fires there. The funny thing is that in my master spreadsheet it is firing all except the copies / pastes and undo redo. I think I'll reboot and see if anything changes.

  • If Statement Causing Loop To Fail

    Re: If Statement Causing Loop To Fail


    There is a way to work with merged ranges that is easier than other ways if you don't have a choice; you name them. (See Excel 2003 help topic "Define named cell references or ranges" > "Name a cell or a range of cells" for exact instructions.)


    Once named you can access the cell's value via the below code.

    Code
    range("myRangeName").value = [whatever]


    You can set the cell value and a host of other things equal to what ever you want. No matter where you move the cell or anything else in the workbook, excel keeps up with it's location and updates it however you want.


    Though, beware. The admins on this site do not like a thread to morph. With a new topic, it is best to start a new thread.

  • If Statement Causing Loop To Fail

    Re: If Statement Causing Loop To Fail


    Exactly. And there are no hard and set rules. You will find what works best for you as time goes along, but what you have outlined will definitely help your future coding efficiency 10 fold.

  • MapPoint VBA Support

    Re: Mappoint 2006 Vba Support


    I don't have access to MapPoint 2006 and Excel 2003 and wonder if the below code works? I am on a bit of a time crunch.


    If you don't have MapPoint 2006, here's the link.


    Mappoing 2006


    (It's a gig download)



    Below is a reference work I found online:



    Any help anyone can give me would be great!