VBA looping through specific columns

[pvman]

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

 

[nardagus]

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?

 

[Fluff]

How about

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

 

[pvman]

@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 ?

 

[nardagus]

Well, I'd create another worksheet named "vocabulary". It'd looked like this:

[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD]columns to copy formula[/TD]
[TD]last row in range you want to copy formula to[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]48[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]52[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]53[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]61[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]74[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]76[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]77[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]79[/TD]
[TD="align: right"]1000[/TD]
[/TR]
</tbody>[/TABLE]

Then I'd used this 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.

 

[pvman]

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.

 

[Fluff]

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

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

 

[pvman]

@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

 

[Fluff]

You're welcome & thanks for the feedback

 

[pvman]

@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):

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?