Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Type Mismatch error

I am using this code to change 1's to green and 0's to red. This works fine in Excel, but when I transfer the VBA code to qlikview, it no longer works, giving me the error "Type Mismatch:Range" at the third line. Any help would be appreciated.

Sub changeTextColor()  

    Dim v, clr, rng, x

    Set rng = Range("Verifier Concat")                //Problem at this line with "Type Mismatch"

    Do While rng.Value <> ""

        v = rng.Value  

        For x = 1 To Len(v)

            Select Case Mid(v, x, 1)

               Case "1": clr = RGB(0, 128, 0)

               Case "0": clr = RGB(255, 0, 0)

            End Select

                    

            rng.Characters(x, 1).Font.Color = clr

            rng.Characters.Font.Bold = True 

                       

        Next

    

        Set rng = rng.Offset(1, 0)

    Loop

End Sub

Tags (2)
9 Replies
MVP
MVP

Re: Type Mismatch error

Well, Range is an Excel function, AFAIR.

What would you like to do with this code? I assume it should be possible to avoid the macro code completely.

Not applicable

Re: Type Mismatch error

Well I want to do similiar in Qlikview what I have done in Excel; turn a string of 1's and 0's such as 10010 into two different colours, red and green. It would be helpful if macros could be avoided altogether

MVP
MVP

Re: Type Mismatch error

Qlik can't use per character text formatting, AFAIK, so what you are trying to do is not possible, I would assume.

Not applicable

Re: Type Mismatch error

Thanks for the help. Is there any way to specify a range in Qlikview like in Excel?

MVP
MVP

Re: Type Mismatch error

Specifying a range to do what?

Not applicable

Re: Type Mismatch error

Specifying which range the code should act upon

MVP
MVP

Re: Type Mismatch error

In QlikView, you would use either a conditional in your aggregations, like

Sum(If (Country = 'USA', Amount))

or a set expression

Sum({<Country = {'USA'}>} Amount)

to filter your records used in the aggregation.

Not applicable

Re: Type Mismatch error

Thanks again for the quick response.

Just to clarify, in Excel we set the range to column 'B', which included the values like 10001, 111, 000, etc. using the line:


Set rng = Range("B")


We would color code 1's with Green and 0's with Red, like in the image below. After looking up in Qlik Community, we realized that QV could not implement the individual character formatting like you said, so we pulled the same Excel field into QV as a column called 'Verifier Concat', and decided to go the macro route to try and implement similar functionality.


So in short, we just need to set the 'rng' variable to the 'Verifier Concat' field, instead of column 'B', on which we can apply the color coding for individual characters.


VerifierMacro.PNG

Re: Type Mismatch error

Hi,

depending on your requirements a solution might be a pivot table showing single characters of your string using individual colouring:

QlikCommunity_Thread_220605_Pic1.JPG

QlikCommunity_Thread_220605_Pic2.JPG

QlikCommunity_Thread_220605_Pic3.JPG

QlikCommunity_Thread_220605_Pic4.JPG

LOAD *,

    RecNo() as StringID,

    Mid(String,IterNo(),1) as Char,

    IterNo() as CharNo

Inline [

String

100010

011010

1101

100

00101

01010

01111

10011

10

01

111

]

While IterNo()<=Len(String);

hope this helps

regards

Marco

Community Browser