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

Macro for Border (Part 2)

sunny_talwar — Wed, 04 Jan 2017 12:42:14 GMT

Thanks to tamilarasu‌ and marcus_sommer‌ I was able to get the first part of the issue under my belt Macro for Border. Now, the next part is to assign this code to a cell which corresponds to a particular value in another cell. For example

Header 1	Header 2
XYZ	10
ABC	20
Total	30

Now the idea is to apply the border to the cell where Header 1 = 'Total'

Re: Macro for Border (Part 2)

sunny_talwar — Wed, 04 Jan 2017 12:59:38 GMT

Got it working using this:

Set Rng = XLSheet.Cells.Find("Total").Offset(0, 2)

Re: Macro for Border (Part 2)

marcus_sommer — Wed, 04 Jan 2017 13:03:48 GMT

Hi Sunny,

one possibility is to query which of your cells contained the value 'Total' within a loop maybe in this way:

for i = 1 to EndOfYourRows

if XLSheet.Range("B" & i).Value = 'Total' then

Set Rng = XLSheet.Range("C" & i).Select

....

end if

whereby EndOfYourRows could be generated through something like "UsedRange.Columns.Count". It will be depend on your concrete case which method will be needed for it. An alternative could be to count the number of rows before in qlik within a variable like: count(Dim1&Dim2&Dim3) - which meant you could avoid the loop and address the cells directly.

- Marcus