Help with DESIGN a VBA procedure [SOLVED]

[arishy]

Rather than describe the worksheet I am referring to it in my DropBox.


https://www.dropbox.com/sh/5oc913rc25r4yql/YCHilxK3oF


The core procedure is storing specific repeated range ( the yellow part) into an array where I do some complicated calculations. This one I need no help with.


What I need is a way to process ALL the repeated blocks and create a control array with the following elements:

1. The address of the top left corner of the yellow block

2. the number of rows in that block


This control array will drive the looping and call the core procedure for each selected yellow block.


I repeat, the control block array will allow me to know how many yellow blocks in the sheet I have, and the property of each (top left cell and number of rows).


Having that, I use the control array data to loop each yellow block and do the core calculations.


Your help, if you decide to pursue it, is creating that control array.

 

[SirJB7]

Hi, arishy!


Following Peter Graves' instructions, here's the code:

-----

[pre]

Option Explicit

Sub KaosArrayBySigfrid()
' constants
Const ksWS = "Sheet1"
' declarations
Dim lArray() As Long
Dim I As Long, J As Long, K As Long
' start
I = 0
K = 1
' process
With Worksheets(ksWS)
Do Until J = .Rows.Count
' block start
J = .Cells(K, 1).End(xlDown).Row
' end?
If .Cells(J, 1).Value <> "" Then
' next block
I = I + 1
' one more entry
ReDim Preserve lArray(2, I)
lArray(1, I) = J
' last?
If J = .Rows.Count Then
K = J
Else
' single or multiline
If .Cells(J + 1, 1).Value = "" Then
K = J
Else
K = .Cells(J, 1).End(xlDown).Row
End If
End If
' block count
lArray(2, I) = K - J + 1
' cycle
J = K
End If
Loop
End With
' end
Beep
End Sub

[/pre]
-----


Just advise if any issue.


Regards!

 

[arishy]

It seems I am hanging out here with geniuses......

Your quick response amazed me, in addition I got the code too.

I tested the code; gave me EXACTLY what I wanted...( very little fine tuning ) and I am ready to roll


Appreciate very much your support.

 

[SirJB7]

Hi, arishy!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: The output wasn't exactly as required? Bidimensional array with start & length block row values...