Solved: Bulk entry of FormulaR1C1 problem

[mikerickson]

Odd behaviour. I'm putting formulas into cells. I have created an array of strings that are formulas.
When I set a large range's .FormulaR1C1 property with that array, I see text in the cells (like "=R[-1]C18"). The right string goes in the right cell, but I want formulas not text. This happens even if the General Preferences is set to R1C1 style.

If I loop through the range, putting each element in the array in its cell, it reads them like formulas.
What's going on? Is there a reliable way that I can put formulas in cells in bulk fashion?

Dim xRay as Range, formulaRRay() as String, testSize as Long
ReDim formulaArray(1 To testSize)

For i = 1 To testSize
    formulaArray(i) = "=R[-1]C" & CStr(xRay.Cells(i).Column)
Next i

   Rem fills cells with text like "=R[-1]C12"
xRay.Offset(1, 0).FormulaR1C1 = formulaArray
Exit Sub

   Rem fills cells with formulas
For i = 1 To testSize
     xRay.Cells(2, i).FormulaR1C1 = formulaArray(i)
Next i

More oddness, I tried putting some dummy values in the routine above for this post. AND IT WORKS FINE. but the orgininal code still behaves as described.

Sub test()
Dim xRay As Range, formulaRRay() As String, testSize As Long, i As Long

Set xRay = Range("a1")
testSize = 12
Set xRay = xRay.Resize(1, testSize)

ReDim FormulaArray(1 To testSize)
For i = 1 To testSize
    FormulaArray(i) = "=R[-1]C" & CStr(xRay.Cells(i).Column)
Next i

   Rem fills cells with text like "=R[-1]C12"
xRay.Offset(1, 0).FormulaR1C1 = FormulaArray
Exit Sub

   Rem fills cells with formulas
For i = 1 To testSize
     xRay.Cells(2, i).FormulaR1C1 = FormulaArray(i)
Next i
End Sub

Excel 2004 for Mac.

[Dave]

This syntax works for me. HTH. Dave
[VBA]
Dim FormArray(1) As Variant
FormArray(1) = Sheets("Sheet1").Cells(1, 2).Formula
[sheet1!B1] = FormArray(1)
[/VBA]

[malik641]

Hey Mike,

You have to transpose the array you made. The array you made is like a single column of cells, and you're putting the formulas into a single row rather than a single column. I guess Excel VBA can't handle this, and treats your vertical array as string literals...which is weird because if you enter into the cell (F2) and press enter the formula works out fine. Here's what I used to fix this:

[VBA]xRay.Offset(1, 0).FormulaR1C1 = WorksheetFunction.Transpose(FormulaArray)[/VBA]

Hope this helps

[mikerickson]

For Values, one transposes a one dimensional array to write to a column.
For Formulas its different? I'll give it a shot when I get to the right computer with the right files.

Heck, I'll even try dimming FormualArray (1 to 1, 1 to N). (Your reminder that this is realy working with 2-D arrays got that thought flowing.)

[malik641]

After some more testing...I'm actually not sure why the method I gave you works.

I'm trying to read up on Arrays and how they are oriented in VBA. I will report back when I find something.

This is real interesting stuff I'm lovin' it.

[malik641]

Hey Mike,

Take a look at these procedures. It seems that the Transpose method will change A1 style formulas...even $A$1 style formulas. It's confusing. And the Help documentation doesn't help much (see attached image).

Here's the code:
[vba]Option Explicit

Public Sub TryMe()
Cells.Clear
Range("A1:J1").Value = Array(11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
Range("A1:A10").Value = WorksheetFunction.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

Dim arr1() As Long
Dim arr2() As Long
Dim arr3() As Variant
Dim i As Long

ReDim arr1(1 To 10)
ReDim arr2(1 To 10)
arr3 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

For i = 1 To 10
arr1(i) = Cells(1, i).Value
arr2(i) = Cells(i, 1).Value
Next

' Set arr1()
Range("C3").Resize(1, 10).Value = arr1
Range("C5").Resize(10, 1).Value = arr1
Range("D5").Resize(10, 1).Value = WorksheetFunction.Transpose(arr1)

' Even though we looped through a vertical range of cells,
' it is not setting the values properly.
Range("A20").Resize(10, 1).Value = arr2

' We have to transpose the arr2
Range("B20").Resize(10, 1).Value = WorksheetFunction.Transpose(arr2)

' Set arr3
Range("C20").Resize(10, 1).Value = arr3
Range("D20").Resize(10, 1).Value = WorksheetFunction.Transpose(arr3)
End Sub

Public Sub TryMyFormulaArray_RelativeReference()
Cells.Clear
Range("B1:J1").Value = Array(11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
Range("A1:A10").Value = WorksheetFunction.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

Dim arr1() As String
Dim arr2() As String
Dim i As Long

ReDim arr1(1 To 10)
ReDim arr2(1 To 10)

For i = 1 To 10
arr1(i) = "=A" & i
Next


Range("C3").Resize(1, 10).Formula = arr1
Range("C4").Resize(10, 1).Formula = arr1

' This line of code has the column references changed
Range("A15").Resize(1, 10).Formula = WorksheetFunction.Transpose(arr1)
' This line of code works fine
Range("A17").Resize(10, 1).Formula = WorksheetFunction.Transpose(arr1)
End Sub

Public Sub TryMyFormulaArray_AbsoluteReference()
Cells.Clear
Range("A1:J1").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Range("A1:A10").Value = WorksheetFunction.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

Dim arr1() As String
Dim arr2() As String
Dim i As Long

ReDim arr1(1 To 10)
ReDim arr2(1 To 10)

For i = 1 To 10
arr1(i) = "=$A$" & i
Next

' No formula placed as a formula, but arr1 is unchanged
Range("C3").Resize(1, 10).Formula = arr1
' This shows a need for Transpose
Range("C4").Resize(10, 1).Formula = arr1

' This line of code has the row number changed
Range("A15").Resize(1, 10).Formula = WorksheetFunction.Transpose(arr1)
' This line of code works fine
Range("A17").Resize(10, 1).Formula = WorksheetFunction.Transpose(arr1)
End Sub[/vba]

[Reafidy]

Mike,

Try dimensioning your Array as a variant and not as a string.

Also there is Typo which may explain why your second code worked because it wasnt being dimensioned as a string due typo:

[vba]formulaRRay() as String[/vba]
should be
[vba]formulaaRRay() as String[/vba]

So try this:

[vba]
Sub test()
Dim xRay As Range, FormulaArray() As Variant, testSize As Long, i As Long

Set xRay = Range("a1")
testSize = 12
Set xRay = xRay.Resize(1, testSize)

ReDim FormulaArray(1 To testSize)
For i = 1 To testSize
FormulaArray(i) = "=R[-1]C" & xRay.Cells(i).Column
Next i

xRay.Offset(1).FormulaR1C1 = FormulaArray

End Sub
[/vba]

[Reafidy]

Reading further if you want your formulas in a column then:

[vba]
Sub test()
Dim xRay As Range, FormulaArray() As Variant, testSize As Long, i As Long

Set xRay = Range("a1")
testSize = 12
Set xRay = xRay.Resize(testSize, 1)

ReDim FormulaArray(1 To testSize)

For i = 1 To testSize
FormulaArray(i, j) = "=R[" & xRay.Cells(i, j).Row & "]C[1]"
Next i

xRay.Offset(, 1).FormulaR1C1 = FormulaArray
End Sub
[/vba]

or two dimensional:

[vba]
Sub test()
Dim xRay As Range, FormulaArray() As Variant, testSize As Long, i As Long, j as long

Set xRay = Range("A1")
testSize = 10
Set xRay = xRay.Resize(testSize, testSize)

ReDim FormulaArray(1 To testSize, 1 To testSize)

For i = 1 To testSize
For j = 1 To testSize
FormulaArray(i, j) = "=R[" & xRay.Cells(i, j).Row & "]C[" & xRay.Cells(i, j).Column & "]"
Next j
Next i
xRay.FormulaR1C1 = FormulaArray
End Sub
[/vba]

[mikerickson]

The Variant idea seems like a good one.

[malik641]

That does make sense, since Transpose returns a Variant.
Thanks for the solution Reafidy

I'm still curious as to how Excel VBA's "Formula" and "FormulaR1C1" properties works with arrays (behind the scenes)...

[mikerickson]

The variant option works.

But the trick of filling a multi-row range with a one dimesioned array (same entries in each column) of values, doesn't seem to work with .FormulaR1C1, (in my case the relative row reference is misajusted)

If a mod could PM me with instructions on marking the topic SOLVED...

Thanks All!

[Reafidy]

But the trick of filling a multi-row range with a one dimesioned array (same entries in each column) of values, doesn't seem to work with .FormulaR1C1, (in my case the relative row reference is misajusted)

Can you elaborate more mike, I dont quite follow what you are trying to do, and perhaps I can help further.

Maybe a code example.

[mikerickson]

The trick I was refering to (works with Values not Formulas):

Range("a1:d3").Value=Array("a","b","c","d")

results in
a b c d
a b c d
a b c d

What I am doing is (example) :

1)Create the array of formulas {R[-1]C1, R[-1]C2, R[-1]C3, R[-1]C4, R[-1]C5}

2)Permute that array (eg.) {R[-1]C3, R[-1]C5, R[-1]C1, R[-1]C2, R[-1]C4}

3)Put a b c d e in A1:E1

4)Put that array in A2:E2 (.FormulaR1C1), fill down to A7:E7. (row count depends on 2))

5)The result shows the full cycle of the permutation from 2) acting on the elements in A1:E1 .

a b c d e - base Array (row 1 (strings))
c e a b d - permuted (row 2 (formulas))
a d c e b - permutation repeated (row 3 (same formulas as row2))
c b a d e - p^3 (same formulas as row2)
a e c b d - p^4 (same formulas as row2)
c d a e b - p^5 (same formulas as row2)
a b c d e - p^6 = identity permutation (same formulas as row2)

It's a way to access permutation cycle techniques through spreadsheet formulas.
The way it came up was as a demo for a clsPermutation that I have written. Going from step 1 to step 2 requires a one dimensional array of formulas and the time looping through it to get a 2D array of formulas to put on the sheet would be better spent (IMO) with a FillDown.

I am content with the situation. I've learned something about .FormualR1C1 property (it takes Variant better than string; Row ajustment is iffy, so don't "cheat".)

[malik641]

Just FYI, if you are in this thread, click on "Thread Tools" and select the "Mark Thread Solved" radio button and click "Perform Action" to mark this thread solved.

That's pretty interesting with what you're doing, by the way.