Posts by LarryAaron

    I have an input cell formatted as Number/Percentage/4. This cell needs to display the user's input without the user having to type the percent sign. It is used to express some small tax percentages, oftentimes less thamn 1%. This cell is used in other places on the sheet to perform calculations. My issue is with the display of the input values.

    When the user types "1" (meaning 1%, the same as the decimal .01), the cell correctly displays as 1.0000 %.
    When the user types "10" (meaning 10%, the same as the decimal .10), the cell correctly displays 10.0000 %.
    When the user types "100" (meaning 100%, the same as the decimal 1.00), the cell correctly displays 100.0000 %.

    HOWEVER when the user types .1 (meaning .1% --- aka 1/10% --- the same as the decimal .001), the cell INCORRECTLY displays as 10%. It should display as .1%; treating it similarly to the others shown above. Excel is assuming that the user made an error and doesn't want to express a decimal of a percentage, but that is an incorrect assumption.

    It appears that Excel will leave the cell entry alone and treat the entry as a percnetage as long as that percentage is greater than or equal to 1. But when the entry is less than one, it multiplies that amount by 100 and then expressing as 100.

    I have searched the Internet and cannot find a custom format to get Excel to properly express this cell entry as a percent.

    For a temp fix, I WAS able to get Excel to correctly put the percent sign in if the user were to type ".1%". [needed to type the percent sign after the number]. But the problems is it is not intended that the user have to type the percent sign when entering text into that cell. In this application, it would be very confusing and inconsistent for the user to type the percent sign in some instances (if less than 1) and no percent sign when greater than 1%.

    No matter what coordinates I place in this InputBox, the box always appears in the same position on the screen. [I've never have posted my code to this forum; I hope I did this correctly]:

    prompter = "You have either of two choices:" & Chr(10) & Chr(10) & vbTab & Chr(34) _
                       & "1 - zero-cost only" & Chr(10) & vbTab & Chr(34) _
                       & "2 - zero-cost and no scope" & Chr(10) & Chr(10) & Chr(10)
    Choice = Application.InputBox(prompter, "Choose Type of Consolidation", 1, Left:=1000, Top:=800, Type:=1 ) ' Type:=1 restricts Input Box to accept a number only

    Have tried with and without the Left:= and Top:= qualifiers on the parameter list; same effect.

    Also, won't allow me to trap the Excape/Cancel key, even if I chnage paramter to Type:=1+4 which is waht Microsoft MSDN says to do as shown here:

    "The following table lists the values that can be passed in the Type argument. Can be one or a sum of the values. For example, for an input box that can accept both text and numbers, set Type to 1 + 2." [1 means 'number', 2 means 'string/text'].

    MSDN also says that applixcation.inputbox should call the InputBox Method, not the InputBox function, and should fit this syntax:

    expression .InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)expression A variable that represents an Application object.

    Use of a MesgBox automatically puts my prompt box in center of screen but it won't allow me to gather User Input. Really hate to use a UserForm when this method is supposed to work.

    Re: Count number cells based on multiple criteria

    Thanks for your insightful suggestion and for retitling my post. (I've always had a hard time hitting the high points; always too detailed) :smile:
    Your solution won't quite work because my range of cells is indeterminate. The number of columns in that range grows regularly. I am testing all cells in any specifc row across all the included columns; then the same in the next row, and so on.

    BTW: Is there a way to add a NOT() [or any other Excel function, for that matter] to a CountIf/CountIfs or SumIf/SumIfs function?

    Tx again!

    I am trying to be as thorough and clear as possible, so my problem statement is kind of long.

    I have a very challenging IF, COUNTIF, COUNTIFS, or SUMPRODUCT problem. I need to count the number of cells in a range that either meet or DON'T meet several
    criteria. So, I think I may need to add a NOT() to my COUNTIFS/SUMPRODUCT criteria. I've not been able to find anything on the Internet that tells how to
    NOT() a criteria within a COUNTIF, COUNTIFS, or SUMPRODUCTS function. It would be interesting to see the syntax for this whether this gets me to my ultimate solution or not.

    I tried COUNTIF, COUNTIFS, IF and SUMPRODUCT in one form or another using in Excel 2007 but I can't get the NOT() to get through the syntax of any of these.
    NOTE: I have to use a formula because the results of the formula is used for another function in the spreadsheet, so Excel's Conditional Formatting and Filtering features won't work.

    I am having a hard time deciding whether to write this statement so that it ANDs together multiple FALSEs or ORs together multiple TRUES. It depends which
    syntax will clear the COUNTIF, COUNTIFS or SUMPRODUCT statements, I think.

    The criteria for counting a cell is if each of the following is not true. If any one of these exact data types are in the cell, the cell is not counted:
    1) cell contains any number (positive, negative or zero)
    2) cell is a blank cell [Should I Use ISBLANK() or ="" to test?]
    3) cell contains all space characters [Shoul I use TRIM to test?]
    3) cell contains the single letter "e", "E", "i" or "I"
    4) cell contains the string of letters the first 3 of which are either "inc*", "Inc*", "exc*", "Exc*"

    Thanks in advance for your dealing with this tricky question.

    Re: Return Matches & Non-Matches From Delimited Cells Against List Range

    Q1: Neat solution, but my implelemtation is only working on the MatchEm and not the NonMatchem. On my NonMatchem i get #VALUE!

    I copied all code from your downloaded worksheet, SORTING,XLS into my worksheet Module and used everything identical except, in both functions, I substituted ", " for the "^" in vArray statements (for the input sources).

    So instead of vArray = Split(rCell, "^"), I have vArray = Split(rCell, ", ").

    I left the carets "^" in for the output data.

    Q2: How would you modify the routine to return the row #s in the matrix where the matches are found separated by "^" rather than returning the content of the cell?