1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
|
Sub Extract_data()
Dim InpRng As Range
Dim Subname As String
Dim FlatData() As Variant
Dim Rown As Long, RowTab As Long, TabInd As Long, Coln As Long
'Init
Subname = "Extract_data"
On Error GoTo Err_Extract
' Set the Input range, exit if no data (assuming tittle row)
Set InpRng = Worksheets("Document").Range("A1").CurrentRegion
Debug.Print InpRng.Address
If InpRng.Rows.Count < 2 Then
MsgBox "No data to proceed in sheet " & InpRng.Worksheet.Name, vbExclamation, "ERROR: " & Subname
End If
' Redim the array according to the Input data's
ReDim FlatData(1 To 5, 1 To Worksheets("Document").Range("F1").Value) 'Sum of all the /Freq fields
' Parse the input range, store the result in an array then repeat the values
TabInd = 0
For Rown = 2 To InpRng.Rows.Count 'Assuming the records are continuous and the column 4 is with integer
For RowTab = 1 To InpRng(Rown, 4)
TabInd = TabInd + 1
For Coln = 1 To 4
FlatData(Coln, TabInd) = InpRng(Rown, Coln) 'Not really needed, if a specific calculation is needed
Worksheets("Result").Cells(TabInd, Coln) = InpRng(Rown, Coln).Value
Next Coln
Next RowTab
Next Rown
Err_Extract:
If Err.Number <> 0 Then
MsgBox Err.Number & vbTab & Err.Description, vbCritical, "ERROR: " & Subname
Err.Clear
Exit Sub
End If
End Sub |
Partager