▽1
●
空腹おやじ ●100ポイント ベストアンサー |
こういう事でしょうか?
Public Sub sample() Dim i i = Sheet3.Cells(1, Columns.Count).End(xlToLeft).Column If Sheet3.Cells(1, i).Value <> "" Then i = i + 1 'A1が空白ならiを1とする Sheet2.Range("A1").CurrentRegion.Copy 'Sheet2のデータをコピー 'Sheet3に貼りつけ Sheet3.Cells(1, i).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Sheet2.Cells.Clear '記録用シートの初期化 Call getAverage(i) End Sub Private Sub getAverage(ByVal lBeginCol As Long) Const TARGET_SHEET_NAME As String = "Sheet3" Const COL_OFFSET As Long = 2 Dim sHeader As String Dim lCol As Long Dim lEndRow As Long Dim lTargetCol As Long lCol = lBeginCol With ThisWorkbook.Worksheets(TARGET_SHEET_NAME) sHeader = .Cells(1, lCol).Value Do Until sHeader = "" lEndRow = .Cells(1, lCol).End(xlDown).Row lTargetCol = lCol + 1 .Cells(1, lTargetCol).Value = WorksheetFunction.Average(.Range(.Cells(2, lTargetCol), .Cells(lEndRow, lTargetCol))) lCol = lCol + COL_OFFSET sHeader = .Cells(1, lCol).Value Loop End With End Sub