Posts by adamsdr3

    I wrote 3 UDF's, 2 of which are around 600 lines of code. I thought it would be a good idea to separate the two longer ones and put them in their own module....I went into the editor and inserted two new modules. Then I copied the functions over. The problem is that now excel doesn't recognize the function in the sheet. I get #Name? in the cells where they use to work. I tried retyping out the function and got no luck. The only thing that seems to work is pasting it back into the original module....The weirdest thing is that if I start typing out the name of the function in a cell, excel suggests the name of the function...

    Thoughts? These are all public functions. Nothing is private....

    Re: UDF Debug?

    Quote from Caleeco;771720

    Hmm strange.. is is possible because your Dim'd them as Integer and 50,600 is outside the limit of what an integer can hold? (Although I though Excel upgraded these to long anyway)...

    That's exactly what the problem was. Thanks

    Re: UDF Debug?

    that's a really nice way to clean up the code....but I'm still getting #VALUE!
    I'm sure that I'm using the function right because if I change the cell that Classification is referencing to anything other than "CLASS IIIB" then it works just fine....

    I wrote a UDF to run a calculation for me. One of the variables is "Classification." The function checks the variable Classification and assigns a value to MAQG and MAQL. This works great for every classification....except for the very last one, "Class IIIB." Excel returns #VALUE for some reason and I'm not sure why...Thoughts?

    Re: Line up 5 groups of 3 columns each by the first 2 values in each row, leaving bla

    This is the fastest way I can think of to do this;

    1. Place all of the data in 3 columns
    2. Highlight all of the data and press "sort" under the Data tab.
    3. Sort the first column by value from smallest to largest.
    4. Sort the second column by value from smallest to largest.

    I think this is what you were asking for? If I misunderstood, please let me know.

    Re: Relative Range References in VBA

    Sorry mate. Same result. I can't for the life of me figure out why it doesn't want to work anywhere other than A1:G15.

    The values in the table are maximums so it needs to return the pipe ID that is closest but higher than the gpm.

    Re: Relative Range References in VBA

    Thanks for your help Luke.

    I originally used the match and index functions to do it but it's easier to fix it when the people I share the file with mess it up in the form of a UDF.

    I checked the code you wrote and the result is same as mine. It no longer works when the table is anywhere other than A1:G15.

    I wrote a UDF to return the value from a table. It works fine as long as the table starts in cell A1. When I move it, it doesn't work so well. Is there are way to make the table references relative instead of absolute? here is the code

    The function basically looks up a gpm under a specified slope and returns the pipe diameter.


    Re: Nested IF Mutiple Logical Tests

    Too many parenthesis in the If statements in a couple of spots. If you use it the way you wrote it, use this

    =IF(A43>=3.5,"Highly Effective",IF(A43>=2.5,"Effective",IF(A43>=1.5,"Needs Improvement",IF(A43<1.5,”INEFFECTIVE”,"N/A"))))

    The easiest way would be to just create a grading table and use vlookup.

    Re: User For Location

    Got it. This goes in the code for the form.

    1. Private Sub UserForm_Initialize()
    2. Me.StartUpPosition = 0
    3. Me.Top = 0
    4. Me.Left = Application.Left + Application.Width - Me.Width
    5. End Sub

    I have a macro that opens a user form when the workbook is opened. Is there a way to place the user form in the upper right corner when it opens?

    Thanks in advance.

    I'm running excel 2007 and I can't for the life of me figure out how to get it to run my macro when it opens. The name of the macro is "showform." I put the following code in sheet 1 but no luck.

    1. private sub WorkBook_Open()
    2. Showform
    3. end sub

    I'm sure I'm just doing something wrong but I'm not sure what. Suggestions?