Multi-Dimensional Array Procedures

F

floydbates

All:

First let me say that I am a programming neophyte, but I still need
your assistance. I have tried to do my homework; however, I am
certain that I am missing something.

My goal is to:
1. Read a range into a multi-dimensional array, 4 columns by 500
rows. Each column is or could be an array within the multi-
dimension array.
2. Modify input data such that the original array is different than
the current.
3. Add the original array to the modified array.
4. Repeat this addition of previous array and current array for a
fixed number of iterations.
5. Write the cumulative summation array to a worksheet.

My challenges are:
1. How to create and test a multi-dimensional array.
2. How to add two multi-dimensional arrays.
3. How to write a multi-dimensional array to a worksheet.

Any guidance would be greatly appreciated.

Here is what I have so far:

Sub BuildCompositeProductionProfiles()
Dim NumberOfPatterns As Integer
Dim NumberOfForecastedMonths As Integer
Dim OilRateArray As Variant
Dim WaterRateArray As Variant
Dim GasRateArray As Variant
Dim InjWaterArray As Variant

Dim AllStreamsArray() As Variant

'Initialize counters
NumberOfPatterns = 75
NumberOfForecastedMonths = 500

'Call routine to build multi-dimensional array
Call BuildAllStreams(AllStreamsArray, NumberOfForecastedMonths,
OilRateArray, WaterRateArray, GasRateArray, InjWaterArray)

End Sub

Sub BuildAllStreams(AllStreamsArray, NumberOfForecastedMonths As
Integer, OilRateArray, WaterRateArray, GasRateArray, InjWaterArray)
Dim AllStreamsRange As Range

With Worksheets("ThruPut")
Set AllStreamsRange = .Range("AO8:AR" &
NumberOfForecastedMonths + 7)
End With

AllStreamsArray = Application.Transpose(AllStreamsRange) [Error #1]

ReDim Preserve AllStreamsArray(1 To NumberOfForecastedMonths)
[Error #2]

[Error #3]
'Dim i As Integer
'For i = LBound(AllStreamsArray) To UBound(AllStreamsArray)
'Debug.Print AllStreamsArray(OilRateArray(i), WaterRateArray(i),
GasRateArray(i), InjWaterArray(i)), i
'Next i

End Sub


Floyd
 
D

Dave Peterson

If the range where you're picking up those values is contiguous, you can use a
single statement:

#1.
Dim myVals as variant
myvals = worksheets("sheet1").range("a1:d500").value

2&3. Loop through each element to add and plop it all back to the worksheet at
once:

dim rCtr as long
dim cCtr as long

dim myArr1(1 to 10, 1 to 4) as long
dim myArr2(1 to 10, 1 to 4) as long
dim myArr3(1 to 10, 1 to 4) as long

'put something in the arrays for testing
for rctr = lbound(myarr1, 1) to ubound(myarr1,1)
for cctr = lbound(myarr1,2) to ubound(myarr1,2)
myarr1(rctr, cctr) = rctr + cctr
myarr2(rctr, cctr) = rctr * cctr
next cctr
next rctr

'Add them
for rctr = lbound(myarr1, 1) to ubound(myarr1,1)
for cctr = lbound(myarr1,2) to ubound(myarr1,2)
myarr3(rctr, cctr) = myarr1(rctr, cctr) + myarr2(rctr, cctr)
next cctr
next rctr

'put the new array in E1:h10
activesheet.range("e1") _
.resize(ubound(myarr3,1)-lbound(myarr3,1)+1, _
ubound(myarr3,2)-lbound(myarr3,2)+1).value = myarr3


All untested, uncompiled--watch for typos!




All:

First let me say that I am a programming neophyte, but I still need
your assistance. I have tried to do my homework; however, I am
certain that I am missing something.

My goal is to:
1. Read a range into a multi-dimensional array, 4 columns by 500
rows. Each column is or could be an array within the multi-
dimension array.
2. Modify input data such that the original array is different than
the current.
3. Add the original array to the modified array.
4. Repeat this addition of previous array and current array for a
fixed number of iterations.
5. Write the cumulative summation array to a worksheet.

My challenges are:
1. How to create and test a multi-dimensional array.
2. How to add two multi-dimensional arrays.
3. How to write a multi-dimensional array to a worksheet.

Any guidance would be greatly appreciated.

Here is what I have so far:

Sub BuildCompositeProductionProfiles()
Dim NumberOfPatterns As Integer
Dim NumberOfForecastedMonths As Integer
Dim OilRateArray As Variant
Dim WaterRateArray As Variant
Dim GasRateArray As Variant
Dim InjWaterArray As Variant

Dim AllStreamsArray() As Variant

'Initialize counters
NumberOfPatterns = 75
NumberOfForecastedMonths = 500

'Call routine to build multi-dimensional array
Call BuildAllStreams(AllStreamsArray, NumberOfForecastedMonths,
OilRateArray, WaterRateArray, GasRateArray, InjWaterArray)

End Sub

Sub BuildAllStreams(AllStreamsArray, NumberOfForecastedMonths As
Integer, OilRateArray, WaterRateArray, GasRateArray, InjWaterArray)
Dim AllStreamsRange As Range

With Worksheets("ThruPut")
Set AllStreamsRange = .Range("AO8:AR" &
NumberOfForecastedMonths + 7)
End With

AllStreamsArray = Application.Transpose(AllStreamsRange) [Error #1]

ReDim Preserve AllStreamsArray(1 To NumberOfForecastedMonths)
[Error #2]

[Error #3]
'Dim i As Integer
'For i = LBound(AllStreamsArray) To UBound(AllStreamsArray)
'Debug.Print AllStreamsArray(OilRateArray(i), WaterRateArray(i),
GasRateArray(i), InjWaterArray(i)), i
'Next i

End Sub

Floyd
Click to expand...
 
C

cht13er

I'll reply to your three problems, and see if that helps you...

"My challenges are:
1. How to create and test a multi-dimensional array.
2. How to add two multi-dimensional arrays.
3. How to write a multi-dimensional array to a worksheet. "
'------------------------------------------
1. Here's a 2-D array being generated from cells A1:B10 and then
printed to columns c and e:
Public Sub sub1()
'Declarations
Dim Array1(1 To 10, 1 To 2) As Variant
Dim icounter As Integer

'Create 2-D array
For icounter = 1 To 10
Array1(icounter, 1) = Cells(icounter, 1)
Array1(icounter, 2) = Cells(icounter, 2)
Next icounter

'Print array to C1:C10 and E1:E10
For icounter = 1 To 10
Cells(icounter, 3) = Array1(icounter, 1)
Cells(icounter, 5) = Array1(icounter, 2)
Next icounter

End Sub
----------------------------------
2. How to add arrays?
for icounter = 1 to num1
for jcounter = 1 to num2
array3(icounter,jcounter) = array2(icounter,jcounter) +
array1(icounter,jcounter)
next jcounter
next icounter

'---------------------
3. Answered in part 1.

This should help? A little old-fashioned perhaps in that it's doing
everything one cell at a time, but it's easiest to see what's going on
and to debug/modify!

Chris

All:

First let me say that I am a programming neophyte, but I still need
your assistance.  I have tried to do my homework; however, I am
certain that I am missing something.

My goal is to:
1.      Read a range into a multi-dimensional array, 4 columns by 500
rows.  Each column is or could be    an array within the multi-
dimension array.
2.      Modify input data such that the original array is different than
the current.
3.      Add the original array to the modified array.
4.      Repeat this addition of previous array and current array fora
fixed number of iterations.
5.      Write the cumulative summation array to a worksheet.

My challenges are:
1.      How to create and test a multi-dimensional array.
2.      How to add two multi-dimensional arrays.
3.      How to write a multi-dimensional array to a worksheet.

Any guidance would be greatly appreciated.

Here is what I have so far:

Sub BuildCompositeProductionProfiles()
    Dim NumberOfPatterns As Integer
    Dim NumberOfForecastedMonths As Integer
    Dim OilRateArray As Variant
    Dim WaterRateArray As Variant
    Dim GasRateArray As Variant
    Dim InjWaterArray As Variant

    Dim AllStreamsArray() As Variant

'Initialize counters
    NumberOfPatterns = 75
    NumberOfForecastedMonths = 500

'Call routine to build multi-dimensional array
    Call BuildAllStreams(AllStreamsArray, NumberOfForecastedMonths,
OilRateArray, WaterRateArray, GasRateArray, InjWaterArray)

End Sub

Sub BuildAllStreams(AllStreamsArray, NumberOfForecastedMonths As
Integer, OilRateArray, WaterRateArray, GasRateArray, InjWaterArray)
Dim AllStreamsRange As Range

With Worksheets("ThruPut")
        Set AllStreamsRange = .Range("AO8:AR" &
NumberOfForecastedMonths + 7)
End With

   AllStreamsArray = Application.Transpose(AllStreamsRange) [Error #1]

    ReDim Preserve AllStreamsArray(1 To NumberOfForecastedMonths)
[Error #2]

[Error #3]
    'Dim i As Integer
    'For i = LBound(AllStreamsArray) To UBound(AllStreamsArray)
    'Debug.Print AllStreamsArray(OilRateArray(i), WaterRateArray(i),
GasRateArray(i), InjWaterArray(i)), i
    'Next i

End Sub

Floyd
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Array to Multiple Arrays 3
Dynamic multi-dimensional array 6
append multiple worksheets data into a single multi-dimensional array 4
How to read Excel Array to vb.net Arrays? 20
Baffled with assignment of range to array why won't it work??? Anyhelp appreciated 4
Error with calling Format sub 7
Multi-Dimensional Array 17
Search an array of sheets for an array of numbers & return count of numbers 11

Top