Excel VBA - Simple Array Question

  • Thread starter Thread starter ajlove20
  • Start date Start date
A

ajlove20

Hi,

I have a macro that calculates numbers in a loop and places the answer
in an array. Is there a way that I can get the answers to be entere
into a range on the sheet (I am using M10:M110).

Thanks in advance.

a
 
Hi,

Here is a portion of the code:

x = InputBox("Please enter the starting number for the X Value"
"STARTING POINT", "", 100, 1)
y = InputBox("Please enter the ending number for the X Value"
"FINISHING POINT", "", 100, 1)


For i = 1 To 101
t = ((y - x) / 101)
z = z + t
MsgBox z
Next i



And I want each solution for z to be put into M10 down to M110.

Thank you.

a
 
Just one of many ways. You mentioned that z was an Array. I would remove
the constant from the loop.
I have a macro that calculates numbers in a loop and places the answers
in an array.
Click to expand...

Sub Demo()
Dim x, y, k
Dim r As Long
Dim z(1 To 101)

x = InputBox("Start")
y = InputBox("End")

k = ((y - x) / 101)

z(1) = k
For r = 2 To 101
z(r) = z(r - 1) + k
Next r
[M10].Resize(UBound(z)) = WorksheetFunction.Transpose(z)
End Sub

Would some other options help?

Sub Demo2()
Dim x, y, k
x = InputBox("Start")
y = InputBox("End")

k = ((y - x) / 101)

[M10] = k
[M11:M110].FormulaR1C1 = "=R[-1]C+R10C13"
End Sub
 
Hi,

Thank you so much. It worked perfectly. I appreciate all your help.

Thanks again.

a
 
Back
Top