Type Mismatch error

[#1]

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

[swuehl]

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.

[#3]

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

[swuehl]

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

[#5]

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

[swuehl]

Specifying a range to do what?

[#7]

Specifying which range the code should act upon

[swuehl]

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.

[#9]

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.

[MarcoWedel]

Hi,

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

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