PDA

View Full Version : VBA (Noob): Automatically select the next row



Kristian
04-19-2017, 03:42 AM
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.
18960
BTW, forget about the column widths, I have managed to fix that myself.

Kristian

mdmackillop
04-19-2017, 05:08 AM
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
04-19-2017, 05:11 AM
I'd refer you to the Chart Guru here (http://peltiertech.com/marimekko-charts/)

Kristian
04-19-2017, 08:48 AM
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
04-19-2017, 08:54 AM
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
04-19-2017, 09:17 AM
18962 Here's a screenshot of the workbook. Sheet2 is empty

mdmackillop
04-19-2017, 09:23 AM
Please post a workbook, not a picture.

Kristian
04-19-2017, 09:33 AM
18963

Here it is

mdmackillop
04-19-2017, 09:55 AM
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.