Curious to Auto-fill across columns with formula

[GeeWhiz7]

Hi folks,

Is there a way to auto-fill laterally across columns like there is down rows perhaps using a macro?

I was thinking this might be handy in some instances I've run into, but I'm not sure how to make the ActiveCell part work.

Sub FormulaFill()
'
   
    Dim rng As Range
    Dim y As Long
    
    For Each rng In Range("B2:E2").Columns  'intended to autofill the formula below across cell (2) in columns B through E.  Would be even better if it could just go to the last column that has data above it.
    
        ActiveCell = Cells(1, y)  'this part doesn't work, but I assume I need to activate the cell to apply the formula so wanted to just loop through 
    
        ActiveCell.Formula2R1C1 = _
            "=Formula Here()"  ' I don't have a particular formula in mind
            
y = y + 1
Next rng
    
End Sub

 

[Joe4]

If you want to populate the same formula across a column, you do not need to use Autofill or loops at all. You can set the formula for all the cells at once, i.e.

Range("B2:E2").Formula2R1C1 = "=Formula Here()"

 

[GeeWhiz7]

If you want to populate the same formula across a column, you do not need to use Autofill or loops at all. You can set the formula for all the cells at once, i.e.

Range("B2:E2").Formula2R1C1 = "=Formula Here()"

Thanks Joe4. I think I inadequately described my potential usage so it totally makes sense what you suggest.

I sometimes have to fetch data into columns from api’s using predefined functions. I’ve noticed that in a couple of cases as my number of excel columns gets larger and larger the api will stop getting the data properly. It may have to do with memory not getting released. As there are no ways for me to make the api wait I was thinking of forcing it to by the following.

Loop through each column, setting the formula one by one so that once one call to the api is done the next column gets its formula and so on. If it still struggles maybe use a wait in the vba code to give it time.

Any ideas?

 

[Joe4]

Whoa, that totally changes the complexity of your question! It is good to provide that level of detail up front so we know what we are dealing with.
I have never worked with Excel and APIs together, so I really don't have much advice to give on that (I probably would not have replied if I knew that is what you were working with).

I can help with things like formulas, loops, etc, but I really need to have a better idea of what we are working with here.

Can you post an sample of the data/formulas (being sure to "dummy up" any sensitive data first)?
What do these exact formulas look like?
How many rows of data do you want to go down?
How do you know how far across the column you want to go?

Note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

In looking at your original question again, I see in your comments of your code you asked about dynamically finding the last column with data for a particular row.
You can do that with VBA.

So, if you wanted to find the last column with data in row 1, you could do so like this:

Dim lc as Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column

then if you wanted to populate some formula in row 2 from column B to the last row, you could do so like this:

Range(Cells(2, 2), Cells(2, lc)).Formula2R1C1 = "=Formula Here()"

 

[GeeWhiz7]

In looking at your original question again, I see in your comments of your code you asked about dynamically finding the last column with data for a particular row.
You can do that with VBA.

So, if you wanted to find the last column with data in row 1, you could do so like this:

Dim lc as Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column

then if you wanted to populate some formula in row 2 from column B to the last row, you could do so like this:

Range(Cells(2, 2), Cells(2, lc)).Formula2R1C1 = "=Formula Here()"

Sorry for the confusion and let me see if I can get an example and post it when I’m logged into work in the morning.

I actually know how to loop through the columns but what I don’t know how to do is make the next column in the loop “active” (or it’s first cell) by iterating the count variable. This way I could set the formula each time.

This part of my original code below doesn’t work but if it did with the right syntax the the loop should work correctly.

Any ideas how to set active cell location with using a count variable (y in this case) for the column increment?

ActiveCell = Cells(1, y)

 

[Joe4]

Sorry for the confusion and let me see if I can get an example and post it when I’m logged into work in the morning.

I actually know how to loop through the columns but what I don’t know how to do is make the next column in the loop “active” (or it’s first cell) by iterating the count variable. This way I could set the formula each time.

This part of my original code below doesn’t work but if it did with the right syntax the the loop should work correctly.

Any ideas how to set active cell location with using a count variable (y in this case) for the column increment?

ActiveCell = Cells(1, y)

If you want to select the "y" column in row 1, you would simply use:

Cells(1, y).Select

However, in working with VBA, there is seldom a need to select the cells in order to work with them.
It is actually recommend that you NOT do that, as that often slows the code down.
You can set the formula directly to that cell like this:

Cells(1, y).Formula2R1C1 = "=Formula Here()"

(no need to select the formula first)

And if you wanted to reference the next blank cell in row 1, you can use the code from my previous post to find the last populated cell in row 1 like this:

Dim lc as Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column

You can then reference the next blank cell in that row like this:

Cells(1, lc + 1)

Does that help?

 

[GeeWhiz7]

If you want to select the "y" column in row 1, you would simply use:

Cells(1, y).Select

However, in working with VBA, there is seldom a need to select the cells in order to work with them.
It is actually recommend that you NOT do that, as that often slows the code down.
You can set the formula directly to that cell like this:

Cells(1, y).Formula2R1C1 = "=Formula Here()"

(no need to select the formula first)

And if you wanted to reference the next blank cell in row 1, you can use the code from my previous post to find the last populated cell in row 1 like this:

Dim lc as Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column

You can then reference the next blank cell in that row like this:

Cells(1, lc + 1)

Does that help?

Sorry for delay. I was having trouble with the API being updated. So interestingly, in this case in order to manage the API timing, I need to select the cells so Cells(1,y).select makes that work. Thanks for that syntax. Really the problem is the API as it will intermittently try to refresh the data using any function that is referenced in the spreadsheet, once that cell count starts to get big, then the problems begin. Using Range(Cells(),Cells()).Select helps me do that one column at a time, cut/paste values to get rid of formula and then move to the next column. A bit of a pain but it works.

Thanks for your help.

 

[Joe4]

OK. Glad you goit something worked out.