topic Re: Macro for Border (Part 3) in QlikView

Macro for Border (Part 3)

sunny_talwar — Wed, 04 Jan 2017 13:19:43 GMT

Now in the third part (after Macro for Border and Macro for Border (Part 2)) I need to apply formating to more than one row. For example

Header 1	Header 2	Header 3
XYZ	10	20
ABC	30	40
Total1	40	60
PQR	30	40
Total2	10	20

Now my goal is to add the border for Header 2 and Header 3 which corresponds to the cell which says Total1 and Total2

I guess I can write a long code which will work, but is there a for loop I can use here to avoid writing a huge code?

tamilarasu‌, marcus_sommer‌

Thanks,

Sunny

Update: Highlighted the text in red to show which cells need to be formatted

Re: Macro for Border (Part 3)

sunny_talwar — Wed, 04 Jan 2017 13:35:45 GMT

So I have handled this partially for each of the search word, now I need to get this for two columns

WhatToFind = Array("Total1", "Total2")

For iCtr = LBound(WhatToFind) To UBound(WhatToFind)

Set Rng = XLSheet.Cells.Find(WhatToFind(iCtr)).Offset(0, 2)

Re: Macro for Border (Part 3)

sunny_talwar — Wed, 04 Jan 2017 13:38:56 GMT

I think I got this one as well using a second loop

WhatToFind = Array("Total1", "Total2")

For iCtr = LBound(WhatToFind) To UBound(WhatToFind)

For vOffset = 2 to 3

Set Rng = XLSheet.Cells.Find(WhatToFind(iCtr)).Offset(0, vOffset)

Re: Macro for Border (Part 3)

tamilarasu — Wed, 04 Jan 2017 14:18:42 GMT

Hi Sunny,

Again sorry for the delayed reponse. Here is the code.

Set objCells=XLSheet.Cells
With objCells
Set c = .Find ("Total*")
If Not c Is Nothing Then
firstAddress = c.Address
Do
   Set Rng = XLSheet.Range("B" & c.row & ":C" & c.row)
   With Rng.Borders(8)
     .LineStyle = 1
     .Weight = 2
     .ColorIndex = 0
     .TintAndShade = 0
    End With

   With Rng.Borders(9)
    .LineStyle = -4119
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = 4
   End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With