FellowExcellor
Board Regular
- Joined
- May 17, 2005
- Messages
- 59
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
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