Inserting Formula into every second cell

[Amy2011]

Hi everyone..
Am new to the forum and fairly new to VBA i've mainly been working off macros that other people have already written and figuring it out like that but i'm stumped on this one and have searched everywhere on this forum (i think!!!)

I need to insert a formula into every second column on the third row (A3,C3,E3,G3... and so on)

I have about 500 columns in total in my spreasheet so trying to avoid having to type in every 2nd cell name!)

Thanks
Amy

 

[Weaver]

Do the columns need to be inserted? If not, something like

for r = 3 to 500 step 3 'change the 500 to whatever number you need
  for c = 1 to 500 step 2
    cells.(r,c).formula = "=" & r & "*" & x 'formula goes here
  next c
next r

HTH

 

[Amy2011]

Thanks Weaver!

No, the columns do not need to be inserted. However... I just realised that the formula will not be the same every time

so in cell A3, i want the formula to be A1/73.64
then C3 = C1/73.64
E3 = E1/73.64

etc...

This is probably basic stuff I just don't have anyone around me that I can get to help out!

 

[FishingIsMoreFun]

Here is a rather rough and ready solution.
You will have to designate the start cell and of course change the formula.
This will add a formula to every second cell/column there after.

I have made it enter 100 formulas, hence it loops 100 times, change this to your needed number.

It will be better than entering 500 individual formulas.

I have highlighted the values in blue you can edit for your own use.

Cheers

Sub EnterFormula()


    Range("[COLOR="Blue"]A1[/COLOR]").Select

      For i = 1 To [COLOR="blue"]100[/COLOR]

        ActiveCell.Offset(0, 2).Select
        ActiveCell.Formula = "[COLOR="blue"]=SUM(A1:A6)[/COLOR]"
   
      Next i
    
End Sub

--Hmmm just noticed I been beaten to an answer yet again, sorry bout that---
--You must have posted while I was writing.---

--Cheers--

 

[mikerickson]

Sub test()
    Dim i As Long
    Dim formRange As Range
    
    With Sheet1.Rows(3)
        Set formRange = .Cells(1, 1)
        For i = 3 To 500 Step 2
            Set formRange = Application.Union(formRange, .Cells(1, i))
        Next i
    End With
    
    formRange.FormulaR1C1 = "=R[-2]C/73.65"
End Sub

 

[Amy2011]

thanks fishing.

I tried your code and the formatting is correct. it will put my formula in C3,E3,G3 etc.... for some reason it misses A3??

My only other problem is as I mentioned above... the formula will change each time A3 = A1/73.64
C3 = C1/73.64 etc

 

[FishingIsMoreFun]

Yes it will just start at A3 , you just enter it manually, that should not take long.

I have seen your reply and I am looking into it though it may be beyond the scope of my capabilities without the code looking too complex and messy.

Hopefully someone will reply with a better suggestion, I will give it a go.

cheers

 

[Amy2011]

Thanks for all of your help guys

mike's code worked perfectly i'm slowly getting the hang of it! jeez it's tough when you're first starting out !

This is what I had in the end (changed a few things from Mike's because of my formatting!)

Sub test()
Dim i As Long
Dim formRange As Range

With Sheet2.Rows(3)
Set formRange = .Cells(2, 1)
For i = 1 To 16 Step 2
Set formRange = Application.Union(formRange, .Cells(2, i))

Next i
End With
formRange.FormulaR1C1 = "=R[-2]C/73.64"

End Sub

 

[FishingIsMoreFun]

Glad you got a reply.

Mikes answer looks pretty sweet.

Cheers