Home
Blog
Inquire
ＶＢＡ

ＶＢＡ--- 数式の設定


　エクセルで表題の項目の順序を変更したり、追加したりした場合に自動で数式を設定するツール
　該当するシートを選択した時に、数式を設定（説明は省略）

 Private Sub Worksheet_Activate()
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  vrange1 = "$J$3:J$140"
  vrange2 = "$X$3:X$140"
  ActiveSheet.Range("A1").Formula = "= N1 & " & Chr(34) & "売上データ" & Chr(34)
  For rowcnt = 3 To 101
   For colcnt = 2 To 13
    If ActiveSheet.Range("A" & rowcnt) <> "" Then
     colnm = Split(Cells(1, colcnt).Address, "$")(1)
     ActiveSheet.Cells(rowcnt, colcnt).Formula = "=" & "SUMIF('" & _
     ActiveSheet.Cells(2, colcnt) & "'!" & vrange1 & _
     "," & Chr(34) & ActiveSheet.Cells(rowcnt, 1) & Chr(34) & _
     ",'" & ActiveSheet.Cells(2, colcnt) & "'!" & vrange2 & ")"
     ActiveSheet.Range("N" & rowcnt).Formula = "=sum(B" & rowcnt & ":M" & rowcnt & ")"
    End If
   Next colcnt
  Next rowcnt
  For colcnt = 2 To 14                                                                  
   colnm = Split(Cells(1, colcnt).Address, "$")(1)                                         
   ActiveSheet.Range(colnm & 102).Formula = "=sum(" & colnm & 3 & ":" & colnm & "101)"                  
  Next colcnt
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
 End Sub

©2018KanazawaNoSakai