PDA

View Full Version : filling array from multi dimensional array



amateur1902
02-25-2008, 08:32 AM
Hallo everyone,

I've got a question.

I've got the folowing array's

date1>company1>price
>Company2>price
>company3>price
>enz

Date2>company1>price
>company2>price
>enz

enz.

But these are not the array's that i need for a correlation that i want to make.

Is it possible to transform the array's above to an array such as:

Company>date1>price
>date2>price
>date3>price
>enz

Company2>date1>price
>date2>price
>enz

If it is not quite clear what i mean feel free to ask.

Thanks

Bob Phillips
02-25-2008, 09:08 AM
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To iLastRow

If .Cells(i, "A").Value = "" Then

.Cells(i, "A").Value = .Cells(i - 1, "A").Value
End If
Next i

.Columns("A:C").Sort key1:=.Range("B1"), Order1:=xlAscending, header:=xlNo
.Columns("B:B").Cut
.Columns("A:A").Insert Shift:=xlToRight
For i = iLastRow To 2 Step -1

If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then

.Cells(i, "A").Value = ""
End If
Next i

End With

Application.ScreenUpdating = True

End Sub

amateur1902
02-25-2008, 09:19 AM
Hello,

Thank you for youre reaction. But i think that the solution that is offered does not work the way i would like to.

I would like to get the dimensions from Vba and not from excel sheets.

I wil show you what i've got so far. This is the first week that i have been trying to work with vba so it is possible that some things don't match up by what you should do.

thanks.


Private Sub CommandButton1_Click()
Range("g7").Select
Range(Selection, Selection.End(xlDown)).Select
totalrows = ActiveSheet.UsedRange.Rows.Count - 1
Dim Tict() As String
ReDim Tict(totalrows)
Range("g7").Select
i = 0
Do While i <= totalrows
Tict(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
arraySecurities = Tict

End Sub

Sub correl()
Dim arr1, arr2, arr3, arrMaster, i As Long
arr1 = Array(1, 2, 3, 4)
arr2 = Array(3, 6, 1, 6)
arr3 = Array(6, 7, 8, 9)
Range(Selection, Selection.End(xlDown)).Select
totalrows = ActiveSheet.UsedRange.Rows.Count - 1
ReDim arrMaster(totalrows)
arrMaster(0) = arr2
arrMaster(1) = arr3
For i = LBound(arrMaster) To UBound(arrMaster)
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Application.correl(arr1, arrMaster(i))
Next i
End Sub

So i hope as you can see is that i need the array's from the first function and then transform within vba so i can us them to fill the last function.

Thanks.

mdmackillop
02-25-2008, 12:56 PM
Hi,
When you post code, select it and click the VBA button to format it as shown.
Regards
MD

amateur1902
02-26-2008, 01:05 AM
Hi,

Thanks for putting my vba in code. The next time i wil surely remember it.

greetz

Ps. Is my problem clear to you all. If there is anything that you would like to know please ask.