# 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)``

Code
``````Sub Macro1()
Dim StrRange As String
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
.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.