Loop through columns and paste an array formula

Question

I am working on a problem to loop through a certain number of columns and paste in an array formula. For every new column, I have to change the formula to reflect that column address. However, when I try to run it now, I keep getting a 1004 (select method of range class failed) error. Here is what I have written:

Sub Testlee()
Dim i As Integer
Dim LastColumn As Long
Dim rng As Range
Dim colStr As String

LastColumn = 10
For i = 1 To LastColumn
colStr = Replace(Split(Columns(i).Address, ":")(0), "$", "")
ThisWorkbook.Sheets("Data Validation").Range(colStr & "2:" & colStr &  "500").Select
Selection.FormulaArray = "=IF(LEN(Agent1!" & colStr & "2:" & colStr & "500) + LEN(Agent2!" & colStr & "2:" & colStr & "500) = 0,"""",(IF(Agent1!" & colStr & "2:" & colStr & "500=Agent2!" & colStr & "2:" & colStr & "500, ""YES"", Agent1!" & colStr & "2:" & colStr & "500&""||""&Agent2!" & colStr & "2:" & colStr & "500)))"

Next i

End Sub

Any help would be appreciated : )

Update: I was able to get it working using a combination of the two approaches. Here is the code that works:

For i = 1 To LastColumn
colStr = Replace(Split(Columns(i).Address, ":")(0), "$", "")
    With ThisWorkbook.Sheets("Data Validation").Range("A2:A500")
        ThisWorkbook.Sheets("Data Validation").Range(colStr & "2:" & colStr & "500").FormulaArray = "=IF(LEN(Agent1!RC:R[498]C)+LEN(Agent2!RC:R[498]C) = 0,"""",(IF(Agent1!RC:R[498]C=Agent2!RC:R[498]C, ""YES"", Agent1!RC:R[498]C&""||""&Agent2!RC:R[498]C)))"
    End With
Next i

Thank to everyone for their help!

You can `Select` cells only in active sheet. You don't need select at all [link](http://stackoverflow.com/questions/10714251) — BrakNicku, Oct 05 '15 at 06:48
Hi BrakNicku, I tried not using Select, and just doing having it as Sheets(Range).FormulaArray = "Formula" but it didn't work either. — FrenchConnections, Oct 05 '15 at 07:03
psychicebola, I didn't use R1C1 reference in the formula. I'm not sure how to implement that in an array formula with changing columns. — FrenchConnections, Oct 05 '15 at 07:05

SierraOscar · Answer 1 · 2015-10-05T08:25:56.810

1

Try this instead:

Sub MM()

Const LastCol As Integer = 10 '// Column number to extend to

With Sheets("Data Validation").Range("A2:A500")
    .Resize(500, LastCol).FormulaArray = "=IF(LEN(Agent1!RC:R[498]C)+LEN(Agent2!RC:R[498]C) = 0,"""",(IF(Agent1!RC:R[498]C=Agent2!RC:R[498]C, ""YES"", Agent1!RC:R[498]C&""||""&Agent2!RC:R[498]C)))"
End With

End Sub

Use R1C1 Notation to make the formula relevant to each cell without looping.
Also, you can use Resize() method to resize an existing range - again, saves looping. Info on Resize method here
Finally, as already mentioned - 99.99% of the time there is no need to .Select anything in vba - you can access an object's properties and methods directly without making it the Selection

edited Oct 05 '15 at 08:25

answered Oct 05 '15 at 07:42

SierraOscar

17,507
6
40
68

Hi @Macro Man, thanks for the advice! I think you're right that I need to use R1C1, but when I implement it in this way: With Sheets("Data Validation").Range("A2:A500") .Resize(500, LastCol).FormulaArray = "=IF(LEN(Agent1!RC:R[498]C)+LEN(Agent2!RC:R[498]C) = 0,"""",(IF(Agent1!RC:R[498]C=Agent2!RC:R[498]C, ""YES"", Agent1!RC:R[498]C&""||""&Agent2!RC:R[498]C)))" Then I end up with a range of A500:J with exactly the same formula (in this case the R1C1 evaluates to A2:A500). It doesn't seem to change according to the column. – FrenchConnections Oct 05 '15 at 08:04
Hi @Macro Man I put in the R1C1 and it still only copies in the same formula in the range A2:J500. – FrenchConnections Oct 05 '15 at 16:56

score 0 · Answer 2 · edited Oct 05 '15 at 08:05

From what I see, you're probably selecting the columns of Sheets 'Data Validation' while the active sheet is another worksheet.

You need to activate Data Validations Sheet first which means you add a line

ThisWorkbook.Sheets("Data Validation").Select

before the line

ThisWorkbook.Sheets("Data Validation").Range(colStr & "2:" & colStr & "500").Select

This is provided you don't need to run anymore code which uses the current activesheet.

Loop through columns and paste an array formula

2 Answers2