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 — BrakNicku
– BrakNicku, Commented Oct 5, 2015 at 6: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
– FrenchConnections, Commented Oct 5, 2015 at 7: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
– FrenchConnections, Commented Oct 5, 2015 at 7:05

SierraOscar · Accepted Answer · 2015-10-05 08:25:56Z

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 5, 2015 at 8:25

answered Oct 5, 2015 at 7:42

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FrenchConnections Over a year ago

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 Over a year ago

Hi @Macro Man I put in the R1C1 and it still only copies in the same formula in the range A2:J500.

SierraOscar · Accepted Answer · 2015-10-05 08:05:45Z

0

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.

edited Oct 5, 2015 at 8:05

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

answered Oct 5, 2015 at 7:42

Stanley

2,8266 gold badges28 silver badges45 bronze badges

Collectives™ on Stack Overflow

Loop through columns and paste an array formula

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related