VBA Excel:每第n列插入一个新列，该列中填充了一个公式，该公式将立即数列向左 [英] VBA Excel: Insert a new column every nth column filled with a formula which refrences the immediate column to the left

问题描述

我想每隔一列插入新列大约260次，然后我需要用一个公式来填充新列，该公式引用左边的立即数列.这是我必须插入新列的内容:

I would like to insert a new column every other column about 260 times, then I need to fill the new columns with a formula which references the immediate column to the left. Here is what I have to insert a new column:

Sub insert_column_every_other()
For colx = 2 To 266 Step 2
Columns(colx).Insert Shift:=xlToRight
Next
End Sub

但是我仍然坚持公式，它只是按原样复制公式.我需要更改C3值并引用左侧的立即数列(其他部分可能不是100％，我是VBA的新手，所以我更正，不胜感激):

But I am stuck on the formula, it merely copies the formula as is.. I need the C3 value to change and reference the immediate column to the left (other parts might not be 100% I am new to VBA so all corrections are appreciated):

Sub Repeat()
For ColNum = 3 To 2000 Step 2
    Range(Cells(2, ColNum), Cells(21, ColNum)).FormulaR1C1 ="=AVERAGE(OFFSET(C3,1,0,2,1))"
Next ColNum
End Sub

推荐答案

我认为下一个代码应该可以满足您的要求

I think the next code should do what you want

Sub insert_column_and_Formula()
   Dim colx As Long
   Dim H As Worksheet
   Set H = H3 'Replace H3 with the sheet that contains your data

   For colx = 2 To 266 Step 2
      'Insert the Column' 
      Call H.Columns(colx).Insert(Shift:=xlToRight)
      'Put the formula in the new Column'
      H.Range(H.Cells(2, colx), H.Cells(21, colx)).FormulaR1C1 = "=AVERAGE(OFFSET(RC[-1],1,0,2,1))"

   Next colx
End Sub

希望这对您有帮助，如有任何问题，请告诉我

Hope this help you, any question let me know please

这篇关于VBA Excel:每第n列插入一个新列，该列中填充了一个公式，该公式将立即数列向左的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！