How to apply a macro to columns in selected range only


Board Regular
May 17, 2005
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?


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

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().


Well-known Member
Sep 10, 2008
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.


Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...