CODEWITHSUNDEEP
vbaexcel
user1977802
user1977802

Reputation: 353

Print VBA Array in to Excel Cells

I am trying to print a VBA created array into cells of an excel spreadsheet. Percolation and Runoff values keep getting "subscript out of range" errors Am I creating hose arrays correctly? I have merged the calculation and print sub function into one.

 NumMonth = 12
 Dim col As Long
 Dim rw As Long
 rw = 4
 col = 13

 Range(Cells(rw, col), Cells(rw + NumMonth - 1, col)).Value = _
    Application.Transpose(WC)

Range(Cells(rw, col + 1), Cells(rw + NumMonth - 1, col + 1)).Value = _
    Application.Transpose(Runoff)

   Range(Cells(rw, col + 2), Cells(rw + NumMonth - 1, col + 2)).Value = _
     Application.Transpose(Percolation)
   End Sub

Upvotes: 1

Views: 14162

Answers (2)

glh
glh

Reputation: 4972

Your Precip and RefET are always equal, is this ment to be?

Precip(i) = Cells(4 + i, 2).Value
RefET(i)  = Cells(4 + i, 2).Value

Also, when you set the Runoff and Percolation arrays they do not get set to anything if your if statement is not met (below), and I could meet it with the data provided:

If (fc - WC(j - 1) + RefET(i) * dz < Precip(i) * dz) then

I'd add somthing to make sure there is always a value in them:

If (fc - WC(j - 1) + RefET(i) * dz < Precip(i) * dz) Then
    Runoff(i) = (Precip(i) - (fc - WC(j - 1) + RefET(i)) * dz) * 0.5
    Percolation(i) = (Precip(i) - (fc - WC(i - 1) + RefET(i)) * dz) * 0.5
    WC(j) = fc
Else
    Runoff(i) = 0
    Percolation(i) = 0
    WC(j) = WC(j - 1) + Precip(i) - RefET(i) / dz
End If

Once doing this I found out you hadn't ReDim'd the Runoff and Percolation variables. you'll need to add the following to your WaterBalanceRead sub.

ReDim Runoff(1 To NumMonth + 1)
ReDim Percolation(1 To NumMonth + 1)

Upvotes: 1

scott
scott

Reputation: 2275

Its application.transpose instead of worksheetfunction

  Range(Cells(rw, col), Cells(rw + NumMonth - 1, col)).Value = _
      application.Transpose(WC)

Here is a test sub

Sub test()
Dim myarray As Variant

'this is a 1 row 5 column Array
myarray = Array(1, 2, 3, 4, 5)

'This will fail because the source and destination are different shapes
Range("A1:A5").Value = myarray


'If you want to enter that array into a 1 column 5 row range like A1:A5

Range("A1:A5").Value = Application.Transpose(myarray)
End Sub

You will get an error if you call an array you created in another sub. The reason for this can be seen in the locals window when you step through your code. When you run the sub that creates your array it will be shown in locals, when the sub ends it will disappear, meaning it is no longer stored in memory. To refer to a variable or array from another sub you must pass it.

Passing arrays or variables can be done in different ways here are a few links Here And Here

Upvotes: 2

Related Questions