| |
後から分からなくなった時のために、ちょっと修正したコードを載せておきます。
Option Explicit
Sub 罫線がある限り_罫線の間の合計()
Dim scell As Range
Dim acell As Range
Dim c As Range
Dim a As Range
Dim s As Range
Dim i As Integer
Set scell = ActiveCell
For i = 1 To 100
If scell.Borders(xlEdgeBottom).LineStyle <> xlNone Then
If scell.Borders(xlEdgeBottom).LineStyle <> xlNone Then
Set acell = scell
End If
Set s = scell
Set c = s
Do While c.Borders(xlEdgeTop).LineStyle = xlNone And c.Row > 1
Set c = c.Offset(-1)
Loop
s.Formula = "=SUM(" & c.Offset(, -1).Address & ":" & s.Offset(, -1).Address & ")"
Else
End If
Set scell = scell.Offset(1)
'scell.Select
Next
End Sub
Sub 罫線の間の合計1() '合計の列に罫線有り
Dim s As Range
Dim c As Range
Set s = Selection(1)
Set c = s
Do While c.Borders(xlEdgeTop).LineStyle = xlNone And c.Row > 1
Set c = c.Offset(-1)
Loop
s.Formula = "=SUM(" & c.Offset(, -1).Address & ":" & s.Offset(, -1).Address & ")"
End Sub
Sub Sample() '数字の列に罫線有り
Dim s As Range
Dim c As Range
Set s = Selection(1)
Set c = Selection(1).Offset(, -1) 'Selectionでもいいけど念のため
Do While c.Borders(xlEdgeTop).LineStyle = xlNone And c.Row > 1
Set c = c.Offset(-1)
Loop
s.Formula = "=SUM(" & c.Address & ":" & s.Offset(, -1).Address & ")"
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
