Print VBA Array in to Excel Cells

Question

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

Month:1-12, Ref Et:0.4, Precip: 0.07, fc:0.3, pwp=0.1, WC(i)=0.15. I dont know of a better way to provide data?? — user1977802
– user1977802, Commented Feb 22, 2013 at 23:50

Community · Accepted Answer · 2018-05-08 21:48:18Z

2

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

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Feb 22, 2013 at 20:18

scott

2,2951 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user1977802 Over a year ago

No, application.Tanspose does not work. It is an array created in VBA

user1977802 Over a year ago

I think I have made the correct adjustments you were recommending to me code (see edits in original post) however, it is still not working. Am I doing this correctly?

scott Over a year ago

Is there an error? You may be running into an issue of using a range not assigned to a sheet.. such as sheets("sheet1").range("A1:A5").value = application.transpose(wc)

user1977802 Over a year ago

yes, it is the same error I was getting previously- invalid procedure or call error. If I change it to "Sheets("sheet1").Range("M5:M22").Value = Application.Transpose(WC)" I still am getting the same error.

user1977802 Over a year ago

I have declared it as global. I have included and edited my full code above.

|

glh · Accepted Answer · 2013-02-23 00:22:55Z

1

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)

edited Feb 23, 2013 at 0:22

answered Feb 23, 2013 at 0:01

glh

4,9824 gold badges26 silver badges42 bronze badges

2 Comments

user1977802 Over a year ago

Thanks for catching that data read error! If I use the data I provided you, my arrays will still not set?

glh Over a year ago

@user1977802 - the last line fixed mine!

Collectives™ on Stack Overflow

Print VBA Array in to Excel Cells

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related