CODEWITHSUNDEEP
arraysloopsexcelrangevba
user2027945
user2027945

Reputation: 1

VBA Excel Store Range as Array, extract cell values for formula. Offset for other variables

I'm a bit new at this. How would I take the column and put the cell data of which is an integer and go through all values in that range to put it into a function to output the result into another column in the excel workbook. So my output column will be the entire Comm column using columns G, J and K for inputs into the function =100000*slotNumber+300*xpos+ypos

  A    B     C      D     E    F       G          H    I       J    K
1 Proc Equip Operat Shift Comm Casette SlotNumber Diam Measure XPos YPos
2
3'

So thought if I took the values of each and made a for loop I could take the values and somehow do all this, just not sure how! Please and thank you!

EDIT: I have all columns stored, now I must pass the Array values into the function one by one, for the formula Z = 100000*slotArr(i)+300xList(i)+yList(i) or maybe I can just place it in the for loop.

EDIT: Having placed the function in the loop...I am getting an object out of range error...at the line of the function.

Sub cmdMeans_Click()
Dim i As Long, j As Long
Dim slotList As Range, slotArr() As Variant, xList As Range, xArr() As Variant
Dim yList As Range, yArr() As Variant, cArr() As Variant

Set slotList = Range("P2", Range("P2").End(xlDown))
slotArr() = slotList.Value

Set xList = slotList.Offset(0, 4)
xArr() = xList.Value

Set yList = slotList.Offset(0, 5)
yArr() = yList.Value

'Only one counter required because of the dependancy on the range slotList
For i = 2 To UBound(slotArr, 1)
    'Dimensioning Array
    ReDim cArr(UBound(slotArr, 1), 1)
    cArr(i, 1) = (100000 * slotArr(i, 1)) + (300 * xList(i, 1)) + yList(i, 1)
    'MsgBox ("Comment Cell Value" & cArr(i, 1))
Next

'Resizing Array
ReDim Preserve cArr(i)
'This is where the new values will be written to the comment column

Dim cRng As Range
Set cRng = Range(Cells(14, 1), Cells(UBound(cArr(i))))
cRng.Value = Application.Transpose(cArr)

End Sub

Upvotes: 0

Views: 29594

Answers (1)

bonCodigo
bonCodigo

Reputation: 14361

I get worried to look at your sample - appolgy but really not decipherable... So I stick with your question title and comment:

VBA Excel Store Range as Array, extract cell values for formula. Offset for other variables.

How store Range as Array:-

Dim vArray as Variant

vArray = Sheets(1).Range("A2:G50").Value)

How to pass array into a function that takes an array as a parameter and returns an array:-

Function passArray(ByRef vA as Variant) as Variant
   Dim myProcessedArray as Variant
   '----your code goes here

   passArray = myProcessedArray
End Function

Output Single Dimensional array to worksheet Range:-

Sheets(1).Range("E2").Resize(1, _
         UBound(Application.Transpose(singleDArray))) = singleDArray

Output Multi Dimensional array to worksheet Range:-

Sheets(1).Range("E2").Resize(UBound(multiDArray) + 1, _
UBound(Application.Transpose(multiDArray))) = multiDArray

Upvotes: 2

Related Questions