Storing values as array/matrix in VBA loop

joejackson123

New Member
Joined
Oct 27, 2017
Messages
16
Hi all, I have the following code

HTML:
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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Dim Me as xlNoob

Board Regular
Joined
Nov 12, 2017
Messages
107
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:

Code:
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.
Code:
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:

Code:
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.
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,524
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Or maybe

Code:
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,524
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Code:
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

New Member
Joined
Oct 27, 2017
Messages
16

ADVERTISEMENT

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

New Member
Joined
Oct 27, 2017
Messages
16
Code:
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

Board Regular
Joined
Nov 12, 2017
Messages
107
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.
 
Last edited:

joejackson123

New Member
Joined
Oct 27, 2017
Messages
16
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!

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

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top