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

tungchiu

New Member
Joined
Nov 20, 2005
Messages
34
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You can enter a formula in an entire range in one step, eg:

Code:
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.
 
Upvote 0
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
 
Upvote 0
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? :confused:

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)
Code:
Sub Insert_Formulas_In_All_Columns_At_Once()
  Range("V12:V200").Resize(, 25).FormulaR1C1 = "=IF(OR(RC8="""",RC14=""""),"""",IF(RC8=R8C,RC14,""""))"
End Sub
 
Upvote 0
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? :confused:

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)
Code:
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!
 
Upvote 0
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 ..
Code:
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. :-P
 
Upvote 0
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? :confused:

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?
 
Upvote 0
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
 
Upvote 0
The in-between cells are completely empty
In that case, try this
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top