Calculate Percentage of Text Occurences

  • Hi,


    I am having problems with a macro I wrote that basically fills things in two columns based on a set of values from other columns.


    Lets say it looks like this (column B is based on column A):


    A1 = Hi
    A2 = Hello
    A3 = Hi
    A4 = Hello
    A5 = Hi
    A6 = Hello
    A7 =
    A8 = Hi
    A9 = Hello
    A10 = Bye


    After the macro runs, column B should look like this:


    B1 = 10%
    B2 = 20%
    B3 = 10%
    B4 = 20%
    B5 = 10%
    B6 = 20%
    B7 = 0%
    B8 = 10%
    B9 = 20%
    B10 = 100%


    The problem is this. When I run the macro, it stops at B7 and an error saying "Type Mismatch" pops up.


    I am using if statements, and I tried all the posible combinations I could think of (if a7 = "", or if a7 is empty or if a7 = empty, etc).


    Basically, I want to know what causes that error if possible in my case, if not just in general.


    Does anyone know?


    Thanks.

  • Re: Vba: Type Mismatch


    it should be

    Code
    if isempty(variable)


    Added By Admin for others;

    Code
    Sub Macro1()
    Dim StrRange As String
        With Range("A1", Cells(Rows.Count, 1).End(xlUp))
            StrRange = .Address(ReferenceStyle:=xlR1C1)
            .Offset(0, 1).FormulaR1C1 = "=COUNTIF(" & StrRange & ",RC[-1])/COUNTA(" & StrRange & ")"
            .Offset(0, 1).NumberFormat = "0%"
            .Offset(0, 1) = .Offset(0, 1).Value
        End With
    End Sub

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  • Re: Vba: Type Mismatch


    A type mismatch error occurs when a variable is assigned a value that is not the type declared for that variable. For instance, assigning a string (like Yes or No) to an Integer type variable.


    Without seeing your code, it is next to impossible to tell you exactly why this is occurring.


    Regards,

  • Re: Vba: Type Mismatch


    Without seeing your code I assume you are storing the values in column A in a string variable. When it hits row 7 it tries to store a null value in the string variable. That is a type mismatch. Either declare your variable as a variant or test the length of the cell to be greater than zero prior to trying to store the value of the cell in the string variable. This also looks remarkably like a homework assignment...

  • Re: Vba: Type Mismatch


    Quote from Mavyak

    Without seeing your code I assume you are storing the values in column A in a string variable. When it hits row 7 it tries to store a null value in the string variable. That is a type mismatch. Either declare your variable as a variant or test the length of the cell to be greater than zero prior to trying to store the value of the cell in the string variable. This also looks remarkably like a homework assignment...


    This was not a homework assignment. I just can't use the real data so I simplified it and made up some values :).



    Quote from h1h

    it should be


    VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
    If isempty(variable)


    That's it! Thanks a lot. I tried so many combinations I lost track. I'll definetely never forget this syntax now.



    Quote from Brandtrock


    A type mismatch error occurs when a variable is assigned a value that is not the type declared for that variable. For instance, assigning a string (like Yes or No) to an Integer type variable.


    Without seeing your code, it is next to impossible to tell you exactly why this is occurring.


    Regards,


    I did have the variable set as a string before, and I thought about what you said. It makes sense. I knew somehow the variable type was not the same, but I didn't know what to assign it as so I left it blank in the end. I didn't know what type of variable I should assign to a empty cell (value).


    Thanks again.

  • Re: Vba: Type Mismatch


    FYI - when in doubt about what a type of info a variable will hold, or if it might hold more than one type, you can simply use a variant type.


    This can be explicitly declared like

    Code
    Dim MyVariable as Variant


    or leave it like this

    Code
    Dim MyVariable


    and VBA assigns type Variant by default.


    Regards,

  • Re: Vba: Type Mismatch


    Quote from Brandtrock

    FYI - when in doubt about what a type of info a variable will hold, you can simply use a variant type.


    I know there are plenty of people who agree with this, but IMHO, not knowing what type of info a variable will hold is usually (though not always) a result of poor programming.

  • Re: Vba: Type Mismatch


    I don't disagree with turtle that variants should not be used as a substitute for good programming, or with Brandtrock that variants should be used when acceptable data types can vary.


    I've come around to using variants more frequently lately. For procedures, declaring arguments as variants offers considerable flexibility in receiving, ranges, arrays, and single values, and interpreting each as appropriate. Declaring functions as variants allows them to return meaningful errors (CVErr(xlErrxxx)) when the intended result cannot be returned. For worksheet UDFs, that could also include returning a string error description.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Vba: Type Mismatch


    Quote from shg

    For procedures, declaring arguments as variants offers considerable flexibility in receiving, ranges, arrays, and single values, and interpreting each as appropriate.


    I agree variants are very useful for arrays.

    Quote from shg

    Declaring functions as variants allows them to return meaningful errors (CVErr(xlErrxxx)) when the intended result cannot be returned.


    I often declare functions as variants for this very reason.


    shg, you've made some good points. I didn't mean to imply that variants don't have their place in good programming, I was just suggesting that they should only be used if you have a good reason to use them. I don't consider "Not knowing what type of information a variable will hold" to be a good reason.

  • Re: Vba: Type Mismatch


    Quote from turtle44

    ... not knowing what type of info a variable will hold is usually (though not always) a result of poor programming.


    Agreed, and shg's points are well taken also. Given the OP's assumed experience, I wasn't sure if the default to Variant type was known to the OP and I knew if I only put that hint in that someone would follow up with the explicit declaration.


    Knowing what type of data is going into a variable is good programming practice that I not only follow, but recommend. The use of variant type variables for the uses described is also, IMHO, good programming.


    Thanks for the additional comments as they make the thread more useful to anyone searching the forum in the future.


    Regards,

  • Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.


    In future, please take 1 minute of your time to read the text on the New Thread page.


    Also, Please Read http://www.ozgrid.com/forum/showthread.php?t=74932


    REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!