How to repeat a formula automatically every seven columns

**Capa** · 02-19-2010, 03:13 PM

Hello, this is a cross-post and the link to the original message is here:

http://www.excelforum.com/excel-new-...-few-rows.html

I posted in the new users forum but I think it's a more appropriate question here, and also it seems like there are not many viewers in the new users forum. Admins can delete the original post; I would do it myself but I don't think there's a way to do that. I won't cross-post anymore. Here is my question:

I have a formula that is working, but I want to repeat it every seven columns. I know I can drag it and release it (or drag a few and release them in the appropriate column), but at this point I have so many columns that it's quite cumbersome to do that. Is it possible to create a macro that will automatically repeat the formula in every cell seven columns to the right (the same row for all)?

My formula is in cell K7 is:

=IF(AND(K24<=0%,K24>=-2%,OR(J32="T",J32="F")),1,"FALSE")

Now I want that formula to repeat (with the proper adjustments) for cell R7, i.e.:
=IF(AND(R24<=0%,R24>=-2%,OR(Q32="T",Q32="F")),1,"FALSE")

and then to automatically continue like that for cells Y7, AF7, AM7, and so on.

Is there a way to do this? Thank you!

**JBeaucaire** · 02-19-2010, 03:29 PM

Copied how many times?

Copy K7 only? Or is this a range of cells....e.g. K7:K100 ?

**modytrane** · 02-19-2010, 04:33 PM

see attached sample.

it uses the following macro

Sub repeat_Formula()

times = Range("K2").Value
col = Range("K3").Value

For i = 1 To times
Range("K7").Copy _
    Destination:=Range("K7").Offset(0, i * col)
Next i
    

End Sub

you can define how many times and every so many columns to repeat.
modytrane

**modytrane** · 02-19-2010, 04:36 PM

here's a copy with RUN button to execute the macro.
modytrane

**Capa** · 02-19-2010, 04:18 PM

The range will actually be the 7th row starting with K7, not the K column. So for now let's make the range K7:AFE7. I want it to copy every seven columns over, Y7, AF7, AM7, etc.

**Capa** · 02-19-2010, 05:07 PM

Thank you for your help. Unfortunately it's not working when I run it, and I'm so new to Excel that I don't know what I'm doing wrong.

**Capa** · 02-19-2010, 05:13 PM

Nevermind, I realized what I did wrong. Works perfectly! Thank you for your help.

How to repeat a formula automatically every seven columns

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to repeat a formula automatically every seven columns

Re: How to repeat a formula automatically every seven columns

Re: How to repeat a formula automatically every seven columns

Re: How to repeat a formula automatically every seven columns

Re: How to repeat a formula automatically every seven columns

Re: How to repeat a formula automatically every seven columns

Re: How to repeat a formula automatically every seven columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions