VBA looping through specific columns

pvman

New Member
Joined
Apr 4, 2006
Messages
37
Office Version
  1. 2010
Platform
  1. Windows
Hello All
I am looking for a way to loop through specific columns
i.e. columns # 27,30,38,39,47,48,50,52,53,54,61,72,74,75,76,77,78,79
and in each of those columns, copy a formula from the first cell at the top of that specific column, down to all the rows in that column (lets say from line 3 to line 1000).
Each column have a different formula at the first upper cell.
Any thoughts how to go about it?
TIA
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hello,

I see 3 ways of doing it

1) You have to select a cell with a formula in desired column then we can do macro which will copy this formula down in selected column
2) You can use a row (is second row) where you can put i.e. 1 in every cell of every column you want to copy a formula, then based on this row we can create macro which will look for value 1 in second row and if found we can copy a formula
3) You can use another worksheet which will be a kind of vocabulary. There you can make a list of columns you want to copy a formula. Then we can make a macro which will check this info and do what is required

One question. Will all columns have the same range to copy a formula to?
 
Upvote 0
How about
Code:
Sub pvman()
    Dim Txt As String
    Dim UsdRws As Long
    
    Txt = "AA:AA,AD:AD,AL:AM,AU:AV,AX:AX,AZ:BB,BI:BI,BT:BT,BV:CA"
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    Intersect(Range(Txt).EntireColumn, Rows("3:" & UsdRws)).FillDown
End Sub
This will copy the formula on row 3 to the last used row, based on col A
 
Upvote 0
@nardagus
Appreciate the input. I don't have any room for an "index" line (with "1"s).
I don't know how to create the "vocabulary" you mentioned. This is exactly they type of "loop" I am looking for.
Right now, the number of rows in each column is the same although in the future it might not be.
If I will know how to create the proper loop - I will also be able to deal with a different number of rows.

@Fluff
Thanks for the code but I am not sure it does exactly what I need.
I need it to copy a formula from "AA1", for example, to "AA3:AA1000" (skip AA2 because there is something there).
It seems to me that the code you wrote copies from row #3 downwards (until "last row", which is fine, BTW). Do I understand correctly ?
 
Upvote 0
Well, I'd create another worksheet named "vocabulary". It'd looked like this:

columns to copy formulalast row in range you want to copy formula to
271000
301000
381000
391000
471000
481000
501000
521000
531000
541000
611000
721000
741000
751000
761000
771000
781000
791000

<tbody>
</tbody>

Then I'd used this code:

Code:
Sub pvman()
Dim lr As Integer
Dim ws As Worksheet
Dim vc As Worksheet

'assign main sheet to variable
Set ws = Sheets("sheet1")

'assing vocabulary sheet to variable
Set vc = Sheets("vocabulary")

'check how many rows with column number should we check in vocabulary
With vc
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
End With

'iterate through all column addresses from vc
    For i = 2 To lr

'use column numbers in formula copy in main sheet
        With ws
            .Cells(1, vc.Cells(i, 1)).Copy
            .Range(.Cells(3, vc.Cells(i, 1)).Address & ":" & .Cells(vc.Cells(i, 2), vc.Cells(i, 1)).Address).PasteSpecial xlPasteFormulas
        End With
    Next i


End Sub

Replace sheet1 with the name of a worksheet with formulas to copy.

In vocabulary sheets, column A you can type number of all columns you want to use for formula copy.
Column B keeps info of last rows in range you want to copy a formula to. For each column you can specify different row number.

Nothing fancy, but I hope it does the trick.
 
Last edited:
Upvote 0
Well, I'd create another worksheet named "vocabulary". It'd looked like this:

Thank you SO MUCH for your time and efforts.
I think I understand what you did and it looks like this will do the job. I will give it a try and see how it works.

All the best.
 
Upvote 0
It seems to me that the code you wrote copies from row #3 downwards (until "last row", which is fine, BTW). Do I understand correctly ?
You do indeed understand correctly :)
It does that because that's what you said you wanted.

To copy the formulae from row 1 use
Code:
Sub pvman()
    Dim Txt As String
    Dim UsdRws As Long
    
    Txt = "AA:AA,AD:AD,AL:AM,AU:AV,AX:AX,AZ:BB,BI:BI,BT:BT,BV:CA"
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    Intersect(Range(Txt).EntireColumn, Rows("3:" & UsdRws)).Formula = Intersect(Range(Txt).EntireColumn, Rows(1)).Formula
End Sub
 
Upvote 0
@Fluff
Cheers :)
This is a bit more complicated than the original "intersect" code you posted, but I will study it and give it a try, as well.
Thanks and all the best
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
@Fluff
Well, I tried the code, but apparently the code is taking the formula from AA1 and uses it for ALL the columns.
It also copies it with an "offset" (meaning in row 10 it points to row 8, Row 11 points to row 9, etc...)
It is as if when it copies the code - it does not take into account the relative position, but starts as if row 3 is actually row 1

This is the code I am using (copied from YOUR code, but with my specific columns):
Code:
Dim Txt As String
Dim UsdRws As Long
    
Txt = "AA:AA,AD:AD,AL:AM,AU:AV,AZ:BA,BB:BB,BI:BI,BT:BT,BV:CB,CD:DA"
UsdRws = Range("B" & Rows.Count).End(xlUp).Row
Intersect(Range(Txt).EntireColumn, Rows("3:" & UsdRws)).Formula = Intersect(Range(Txt).EntireColumn, Rows(1)).Formula

Any thoughts?
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top