How can I shortcut all this code into a loop of some kind please.

[tungchiu]

Hi everybody!
I have columns of data that are generated by formulae inserted by the following code.

Private Sub XtendData()
Dim w As Integer

‘Insert formulae in Col 22
For w = 12 To 200 Step 2
Cells(w, 22).FormulaR1C1 = "=IF(OR(RC[-14]="""", RC[-8] = """"), """", IF(RC[-14] = R8C22, RC[-8], """"))"
Next

‘Insert formulae in Col 23
For w = 12 To 200 Step 2
Cells(w, 23).FormulaR1C1 = "=IF(OR(RC[-15]="""", RC[-9] = """"), """", IF(RC[-15] = R8C23, RC[-9], """"))"
Next

‘Insert formulae in Col 24
For w = 12 To 200 Step 2
Cells(w, b).FormulaR1C1 = "=IF(OR(RC[-16]="""", RC[-10] = """"), """", IF(RC[-16] = R8C24, RC[-10], """"))"
Next

And so on ad infinitum for 25 columns. I know there must be a more efficient way to write this but I can’t figure it out. Can anybody help please as I am facing the prospect of even more columns!
Thanks in advance!

 

[Andrew Poulsom]

You can enter a formula in an entire range in one step, eg:

Range("V12:V200").FormulaR1C1 = "=IF(OR(RC[-14]="""", RC[-8] = """"), """", IF(RC[-14] = R8C22, RC[-8], """"))"

If you use appropriate absolute/relative column references you can do all 25 columns in one step.

 

[tungchiu]

Hi Andrew!
Many thanks for that. I now have 25 lines of code rather than pages but more importantly the code only takes seconds to execute rather than the previous minutes. I'm not sure that this is what you had intended (25 lines of code) but even if it isn't - it works like a breeze!
Really appreciate the solution
Cheers
Tungchiu

 

[Peter_SSs]

Hi Andrew!
Many thanks for that. I now have 25 lines of code rather than pages but more importantly the code only takes seconds to execute rather than the previous minutes.

But if using Andrew's concept don't you now have those formulas in every row whereas your original code only placed the formulas in every second row?

If you are happy to have the formulas in every row then, as Andrew suggested, you can do all 25 columns at once (assuming the formulas all follow the same pattern as those shown)

Sub Insert_Formulas_In_All_Columns_At_Once()
  Range("V12:V200").Resize(, 25).FormulaR1C1 = "=IF(OR(RC8="""",RC14=""""),"""",IF(RC8=R8C,RC14,""""))"
End Sub

 

[tungchiu]

But if using Andrew's concept don't you now have those formulas in every row whereas your original code only placed the formulas in every second row?

If you are happy to have the formulas in every row then, as Andrew suggested, you can do all 25 columns at once (assuming the formulas all follow the same pattern as those shown)

Sub Insert_Formulas_In_All_Columns_At_Once()
  Range("V12:V200").Resize(, 25).FormulaR1C1 = "=IF(OR(RC8="""",RC14=""""),"""",IF(RC8=R8C,RC14,""""))"
End Sub

Hi Peter!
The penny finally dropped and wow! That's absolutely amazing. I still don't understand how the Resize property works in this context but I'm hard at work reading tutorials. I really appreciate the input - many thanks again!

 

[tungchiu]

PS. How do I use this code to place formula into every second row - not every row?

 

[Peter_SSs]

I still don't understand how the Resize property works in this context..

.Resize(,25) takes the original range (V12:V200) and resizes it to 25 columns wide. I could have written it more simply without the Resize as ..

Range("V12:AT200").FormulaR1C1 = "=IF(OR(RC8="""",RC14=""""),"""",IF(RC8=R8C,RC14,""""))"

.. but at the time I was too lazy to work out that the required final column was column AT.

 

[Peter_SSs]

PS. How do I use this code to place formula into every second row - not every row?

Well, you can't do it in a single simple line of code like that, which is why I made these comments before:

But if using Andrew's concept don't you now have those formulas in every row whereas your original code only placed the formulas in every second row?

If you are happy to have the formulas in every row then ...

Methods to place the formula only in every second row depend on what is currently in the in-between rows.
So, before the code is to run, do rows 13, 15, 17, etc contain data or formulas, or are they completely empty?

 

[tungchiu]

Well, you can't do it in a single simple line of code like that, which is why I made these comments before:

Methods to place the formula only in every second row depend on what is currently in the in-between rows.
So, before the code is to run, do rows 13, 15, 17, etc contain data or formulas, or are they completely empty?

The in-between cells are completely empty

 

[Peter_SSs]

The in-between cells are completely empty

In that case, try this

Sub Insert_Formulas_Every_Second_Row()
  Range("V12:AT12").FormulaR1C1 = "=IF(OR(RC8="""",RC14=""""),"""",IF(RC8=R8C,RC14,""""))"
  Range("V12:AT13").AutoFill Destination:=Range("V12:AT200"), Type:=xlFillDefault
End Sub