VBA: Referencing Arrays in Formula
I am trying to create a macro that loops through columns to apply a formula. In the last part of the formula, I need to multiply by a value. This value changes for each column (but is consistent for each row within the column). I have attempted to define an array to do this (containing the different multipliers), but get a #NAME? error, and can see that the name of the array is appearing in the formula bar as opposed to the actual value. My code is below. I appreciate the help - I'm very new at this! Sub CotData() Dim Rng1 As Range Dim Element As Integer, ColNum As Integer Dim slope_array As Variant Set Rng1 = Sheets("Co(t) OREAS 901").Range("A48:A127") ActiveWorkbook.Names.Add Name:="slope", RefersTo:=Rng1 Range("A1").Select ActiveCell.Offset(2, 3).Range("A1").Select slope_array = Range("slope") Element = 0 For ColNum = 4 To 320 Element = Element + 1 ActiveCell.FormulaR1C1 = "=('Co(t) OREAS 901'!RC[-2])/('Co(t) OREAS 901'!RC[-1]* (slope_array(Element,1)))" ActiveCell.Select Selection.AutoFill Destination:=ActiveCell.Range("A1:A40") ActiveCell.Range("A1:A40").Select Next ColNum End Sub
Is there a way to initialise an array using an expression/function in R
How are these arrays being used in this Fortran algorithm?
How to assign an array to a row of 2d array in Scala
logstash extract matched strings into array
A class contains an array of objects. Do I need to set each of them to nothing on terminating the containing class?
remove items from array not working
Split Function using an Excel row as delimiter
AS3 Array issue and Arduino
Google apps script - get A1 cell notation of each element in an array
find lowest value for each element given some intervals and its values
Error when adding 2-D array and 2-D subarray of 3-D array in Julia
Deleting Columns Between Two Bounds in Matrix in Matlab?
Find the subset of an array such that the elements in the subset have a common difference
Passing a string parameter to a func to access a property of an array
Dividing an array into two subsets of equal sizes having minimum difference in sum of values
Haskell - build a three-dimensional array from a one-dimensional list of coordinate-tuples