連続データの計算式かVBAの質問

質問

お世話になります。

セルC1には年月（２０１３年１１月など）と入力されています。

そしてセルB９～B３９には数字の連続データ（１～３１）が入力されています。

(1)C1のセルを次の月に変換した場合、B9～B３９のデータを連続データの続き（３２～）に自動変換する計算式やVBAは、どの様に組めば宜しいでしょうか？

(2)そして、B９～B39どこかのセルを空白にした場合、空白にしたセル以降のセルも同様に空白に自動変換する計算式やVBAも分かりません。

(3)(2)で空白になったセルのどれかに「１」と再び入力したら、１を入力したセル以降が再び連続データで自動入力される様にするにはどうすれば良いか分かりません。

ご教授宜しくお願いします。

回答No.3

tom04 · ベストアンサー

No.2です。

補足の件に関して・・・
結局小の月の場合は31日までないので、月末までの数値を表示したい！
というコトですよね？

質問の
＞～B３９のデータを連続データの続き（３２～）に自動変換する計算式
の部分がいまだによく判らないのですが、この部分は無視させていただいて・・・

前回のコードを↓に変更してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i As Long, k As Long, myNum As Long
If Intersect(Target, Range("C1,B9:B39")) Is Nothing Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If IsDate(Range("C1")) Then
myNum = Day(WorksheetFunction.EoMonth(Range("C1"), 0))
End If
With Target
If .Column = 3 Then
Range("B9:B39").ClearContents
For i = 1 To myNum
Cells(i + 8, "B") = i
Next i
Else
i = .Row
If .Value = "" Then
Range(Cells(i + 1, "B"), Cells(39, "B")).ClearContents
Else
For k = i + 1 To myNum + i
Cells(k, "B") = Cells(k - 1, "B") + 1
If k = 39 Or Cells(k, "B") = myNum Then
Exit For
End If
Next k
End If
End If
End With
Application.EnableEvents = True
End Sub 'この行まで

※　とりあえずある行を消去するとそれ以降のB39セルまでを消去！という操作は残しています。

※　単にC1セルに日付データを入力するとB9セル以降にその月の月末までを表示！
というコトがご希望であれば数式だけで対応できますが、
とりあえず今回はこの程度で。m（_ _）m

お礼

補足事項の問題点も無事解決できました。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, k As Long, myNum As Long
If Intersect(Target, Range("C1,B9:B39")) Is Nothing Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
If .Column = 3 Then
myNum = WorksheetFunction.Max(Range("B9:B39"))
If IsDate(.Value) Then
For i = 9 To 39
If Cells(i, "A").Value = "" Then
Cells(i, "B").Value = ""
Else
Cells(i, "B") = myNum + i - 8
End If
Next i
End If
Else
i = .Row
If .Value = "" Then
Range(Cells(i + 1, "B"), Cells(39, "B")).ClearContents
Else
For k = i + 1 To 39
If Cells(k, "A").Value = "" Then
Cells(k, "B").Value = ""
Else
Cells(k, "B") = Cells(k - 1, "B") + 1
End If
Next k
End If
End If
End With
Application.EnableEvents = True
End Sub
で出来ると思います。この度はご丁寧な回答ありがとうございました。

補足

すごいです、確かに小月の表示がされなくなりました！！
＞～B３９のデータを連続データの続き（３２～）に自動変換する計算式
の部分がいまだによく判らないのですが、この部分は無視させていただいて・・・

と言うのを詳しく説明しますと、２０１３年１１月はB9～B38まで「１～３０」の数字が表示されているとします。
それをC1セルの日付を２０１３年１２月に変更すると、B9には１０１３年１１月のB30で表示されていた「３０」の続きの数字の「３１」以降を自動表記させると言う事です。つまり、２０１３年１２月は３１日までありますので、B9～B39に「３１～６１」の数字を自動表記させると言う訳です。
この様に、年月を次の月に更新するに従い、連続データを永遠と該当するセルに表記させ、B9～B39のどこかのセルを空白にしたら、それ以降のセルも空白表記させるVBAを求めています。

回答No.2

tom04

こんにちは！
お望み通りの動きになるかどうかわかりませんが・・・
VBAでの一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストし、データを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i As Long, k As Long, myNum As Long
If Intersect(Target, Range("C1,B9:B39")) Is Nothing Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
If .Column = 3 Then
myNum = WorksheetFunction.Max(Range("B9:B39"))
If IsDate(.Value) Then
For i = 9 To 39
Cells(i, "B") = myNum + i - 8
Next i
End If
Else
i = .Row
If .Value = "" Then
Range(Cells(i + 1, "B"), Cells(39, "B")).ClearContents
Else
For k = i + 1 To 39
Cells(k, "B") = Cells(k - 1, "B") + 1
Next k
End If
End If
End With
Application.EnableEvents = True
End Sub 'この行まで

※　細かい動きを検証していませんので、
ご希望通りにならなかったらごめんなさいね。m（_ _）m

お礼

このコードで私が求める動きをしてくれました。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, k As Long, myNum As Long
If Intersect(Target, Range("C1,B9:B39")) Is Nothing Or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
If .Column = 3 Then
myNum = WorksheetFunction.Max(Range("B9:B39"))
If IsDate(.Value) Then
For i = 9 To 39
If Cells(i, "A").Value = "" Then
Cells(i, "B").Value = ""
Else
Cells(i, "B") = myNum + i - 8
End If
Next i
End If
Else
i = .Row
If .Value = "" Then
Range(Cells(i + 1, "B"), Cells(39, "B")).ClearContents
Else
For k = i + 1 To 39
If Cells(k, "A").Value = "" Then
Cells(k, "B").Value = ""
Else
Cells(k, "B") = Cells(k - 1, "B") + 1
End If
Next k
End If
End If
End With
Application.EnableEvents = True
End Sub
非常に参考になりました、ありがとうございました。

補足

凄いです、ありがとうございます。
ちなみに、A９～A３９にはC1で選択した年月の１日～３１日を入力しています。C1セルの年月を変更すると２９日～３１日や３１日が必要無い月（２０１３年２月や２０１３年１１月など）があります。
そこはカウントせずに、C1セルの年月を変更した場合、B９の連続データを表示させる事も可能なのでしょうか？

回答No.1

bin-chan

(２)(３)については
セルB10に式［=IF(B9=””，””，B9＋1)］を入力して、
セルB10を囲む枠線右下の小さな■を下方向セルB39までドラッグ

(１)は12月の場合、それ以降の予想がつかないので割合。
手入力でセルC1とB9をかえるほうが早そう

お礼

参考になりました。
非常に早い回答ありがとうございます、助かりました。

補足

ありがとうございます。
2013年12月以降（2014年1月～）も続きの連続データを入力します。
回答者様の回答では（2）の指定範囲の何処かの場所を空白にしようとすると計算式が消えてしまうので・・・VBAで組まないとダメでしょうか？