将公式插入空行
问题描述:
我有VBA代码来插入用户定义数量的空行,并且它工作正常。我需要添加到代码中的是在插入第一个空白行后插入公式的方法。具体来说,我需要在列J + T,K + U,L + V,M + W,N + T,O + U,P + W,Q + U,R + W和S + V插入到插入的每个空白行中。将公式插入空行
如果有帮助,用于插入空行的VBA代码低于:
Dim NumRowsToInsert, FirstRow As Long
Dim RowIncrement As Long
Dim ws As Excel.Worksheet
Dim LastRow As Long
Dim LastEvenlyDivisibleRow
Dim i As Long
Do
FirstRow = InputBox("Please indicate at which row you want to start.")
Loop Until IsNumeric(FirstRow)
NumRowsToInsert = InputBox("How many rows would you like to insert _
between each row of data?")
Do
Loop Until IsNumeric(NumRowsToInsert)
RowIncrement = InputBox("How many rows of data between line inserts?")
Do
Loop Until IsNumeric(RowIncrement)
Set ws = ActiveSheet
With ws
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastEvenlyDivisibleRow = Int(LastRow/RowIncrement) * RowIncrement
If LastEvenlyDivisibleRow = 0 Then
Exit Sub
End If
Application.ScreenUpdating = False
For i = LastEvenlyDivisibleRow To FirstRow Step -RowIncrement
.Range(i & ":" & i + (NumRowsToInsert - 1)).Insert xlShiftDown
Next i
End With
Application.ScreenUpdating = True
End Sub
任何帮助,不胜感激!
答
一个公式添加到你会做一些这方面的细胞
'this makes the value of Row 20 Column 1 the sum of B2 through B10
Sheets(1).Cells(20, 1).Formula = "=Sum(B2:B10)"
+0
我原来的帖子可能没有很好地解释自己。上面的VBA代码所做的是创建由空行(有时为多个空行)分隔的多行数据。我需要的VBA代码是在每行数据之后的第一个空行插入公式。上面列出了我需要添加的列。我希望这能更好地描绘出我想要完成的事情。 – dougdrex
您可以发布您的代码,试图插入公式? – admdrew