VBA (Noob): Automatically select the next row

[Kristian]

Hi,

Im quite new to VBA, and have some problems with the loop concept in VBA.

In this case I have a matrix of percentage-numbers, say 20x4 (as in the picture), where the sum of each of the four columns sum to 100%. I want to, in a new worksheet, select a row-range according to the proportion of the percentage-number in a certain range (say 200 rows) and border each of the selected ranges. So the output will be 200x4 with 20 squares that are bordered in each column. (Yes, this is an attempt to create a Marimekko-chart, but I dont what any help to figure out how to make one, im just trying and failing my own way).

Example; if the row in the first column start with 10%, 20% (instead of 9% and 13% in the picture)...., then I want to select a range of 20 rows (from A1), border the range (with a regular outside border), and then go on to the next 40 cells (from cell A21 to A40) and border that range, and so on..., until I have exhausted the first column, and then I want to do exactly the same with the next column, from B1 to B200.

I have tried so many ways for so long, so hopefully someone here can help me. I understand that the explanation was quite bad, so please ask me questions if its unclear.
Capture.JPG
BTW, forget about the column widths, I have managed to fix that myself.

Kristian

[mdmackillop]

Results don't always = 200 due to rounding errors

Option Explicit
Sub Test()
    Dim r As Range, cel As Range
    Dim x As Long, y As Long, i As Long
    Dim col As Long
    x = 200
    Set r = Sheet1.Cells(1, 1).CurrentRegion
    For col = 1 To r.Columns.Count
    i = 1
    For Each cel In r.Columns(col).Cells
        y = Round(x * cel, 0)
        With Sheet2.Cells(i, col).Resize(y)
            .Borders.LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).LineStyle = xlNone
        End With
        i = i + y
    Next cel
    Next col
End Sub

[mdmackillop]

I'd refer you to the Chart Guru here

[Kristian]

Thank u mdmackillop. Im not that into VBA yet, so I have to read a bit more to understand that code. Do u know why I get an error on the 11th line - With Sheet2.Cells(..?

I have done the tutorial from the Chart Guru, but I want a more editable and scalable model.
Thanks again

[mdmackillop]

Can you post a workbook showing your data layout

Results don't always = 200 due to rounding errors

This was wrong; your data columns don't total 100%

[Kristian]

Capture.JPG Here's a screenshot of the workbook. Sheet2 is empty

[mdmackillop]

Please post a workbook, not a picture.

[Kristian]

VBA test.xlsm

Here it is

[mdmackillop]

Your workbook is behaving strangely. Cells(1,1).CurrentRegion is returning a single cell. I've copied your data to a new workbook.

Also, I never took account of 0 values (causing Resize to fail); now corrected.