How to apply a macro to columns in selected range only

[FellowExcellor]

I have put together the code below to insert the transpose function into a range of cells based on the selected columns but cannot get it to work. Basically I select the range/ columns on the sheet that I want to insert formula's in and then run the macro. The issue seems to be with the line "For Each i In selection" which generates the error message: "For each control Variable must be Variant or Object"

I've traied various variations instead such as "For Each i In activerange" "For Each i In Range("k508:s508")" but it generates the same error. Ideally what I want to do is first manually select a range on the spreadsheet to apply the macro to (e.g K505 to S508) to define the columns and then run the macro.

Does anyone know what code changes I need to get it to work?

Thanks,

Fellow Excellor
-------------------------------------------------
Sub Insert_formula2()
Dim lastcell As Integer
Dim firstcell As Integer
Dim i As Integer
Dim rowref As Integer
Dim cell, selection As Range

lastcell = 609
firstcell = 510
rowref = Cells(508, i) '

For Each i In selection
Range(Cells(firstcell, i), Cells(lastcell, i)).FormulaArray = "=Transpose(F" & rowref & ":Db" & rowref & ")"
Next i
Application.ScreenUpdating = True
End Sub

 

[Weaver]

In this case, i would be a cell reference, not an integer.

If you run a loop:

dim i as variant
for each i in selection
debug.print i.value
next i

you might get an idea of what I'm getting at.

HTH