Storing values as array/matrix in VBA loop

[joejackson123]

Hi all, I have the following code

Sub sensitivity()
Dim a1, a2 As DoubleDim i As StringDim offerarray() As VariantDim arraycount As Long
a1 = Range("low").Valuea2 = Range("hi").Value

For i = a1 To a2 Step 0.001
Range("disc").Value = a1 + i
offerarray(i) = Range("propoffer").Value
Next i
End Sub

propoffer, disc low, and hi are all named cells within my spreadsheet. I can't figure out how to store the value from "propoffer" in each step of the loop in an array (which I then later want to post to another sheet). Any help is much appreciated, thank you!

 

[Dim Me as xlNoob]

I'm a bit confused with some stuff in the code, but I don't know what your overall goal is but to answer your question directly I would do something like this:

Sub sensitivity()
Dim a1 As Double, a2 As Double, i As Double
Dim offerarray() As Variant


a1 = Range("low").Value
a2 = Range("hi").Value


ReDim offerarray(1 To 1)


For i = a1 To a2 Step 0.001
Range("disc").Value = a1 + i
offerarray(UBound(offerarray)) = Range("propoffer").Value
ReDim Preserve offerarray(1 To UBound(offerarray) + 1)
Next i
ReDim Preserve offerarray(1 To UBound(offerarray) - 1)


End Sub

But a couple of extra notes:

When declaring a variable you need to explicitly declare the variable type for each variable otherwise anything not explicitly declared will be a variant.

e.g.

Dim a1, a2 As Double

In the above example only a2 is explicitly declared as Double, a1 is variant. The correct way to write this is:

Dim a1 As Double, a2 as Double

Also since you are stepping i through values that are between the range of a1, and a2, i should be declared as the same variable type as a1 and a2 - Double.

The code I've provided will build the offerarray one place at a time in the loop, putting the value of the propoffer cell in each position. However nothing in this code changes the value of porpoffer, is propoffer calculated from the disc value? What is it you're trying to achieve with this? I'd like to help you out I know arrays can be confusing but they are a very powerful tool in VBA.

 

[Marcelo Branco]

Or maybe

a1 = Range("low").Value
a2 = Range("hi").Value
ReDim offerarray(a1 To a2)

For i = a1 To a2 Step 0.001
    offerarray(i) = Range("propoffer").Value
Next i

M.

 

[Dim Me as xlNoob]

Or maybe

a1 = Range("low").Value
a2 = Range("hi").Value
ReDim offerarray(a1 To a2)

For i = a1 To a2 Step 0.001
    offerarray(i) = Range("propoffer").Value
Next i

M.

When referencing a position in an array, do you not have to use integers?

 

[Marcelo Branco]

When referencing a position in an array, do you not have to use integers?

oops... you are right. My bad

M.

 

[Marcelo Branco]

I was trying to avoid ReDim on each step, but of course the way i suggested is wrong (tks for pointing out)

Maybe using a Dictionary

Sub aTest()
    Dim a1 As Double, a2 As Double
    Dim dic As Object, i As Double
    
    Set dic = CreateObject("Scripting.Dictionary")
    a1 = 0.003
    a2 = 0.005
    
    For i = a1 To a2 Step 0.001
        dic(i) = 10 + i
    Next
    MsgBox dic(0.003)
    MsgBox dic(0.004)
    MsgBox dic(0.005)
   
End Sub

M.

 

[joejackson123]

Thank you all! This is very helpful.

What I'm trying to dop is a sensitivity test between the values in the cells"low" and "hi", which will assign the value for the range "disc", and range "propoffer" is a formula based on "disc" and a bunch of other cells not part of this formula. The end result I eventually want to get through is a matrix of "propoffer" values that span the range of the low >> hi values.

I'll try your code and see if I can make it work, and let you know...thanks again!

 

[joejackson123]

offerarray(UBound(offerarray)) = Range("propoffer").Value

Everything worked well until right here, it yielded an error "Method Range of Object_Global failed"

 

[Dim Me as xlNoob]

Possibly a spelling error with the named range "propoffer"? I think that error means it can't find the range that the code is referencing.

Marcelo's Dictionary solution is a pretty good way to go.

 

[joejackson123]

Possibly a spelling error with the named range "propoffer"? I think that error means it can't find the range that the code is referencing.

Marcelo's Dictionary solution is a pretty good way to go.

You were right, "propoffer" was misnamed, but also the code below solved my problem with printing. Thanks again!

Range("ar_1").Resize(UBound(offerarray)) = Application.Transpose(offerarray)