- ベストアンサー
連続データのVBAの質問
- VBAを使用して連続データを操作する方法についての質問です。
- セルC1に年月を表記し、そのC1セルの年月を変更した場合にB9～B39のセルが自動的に連続データの数字を記入する方法についての質問です。
- また、B9～B39のどこかのセルを空白にすると、そのセル以降も空白になってしまいます。C1の年月を次の月に更新した際にB39で示された数字以降の連続データをB9に表示させる方法についても質問しています。
- Excel（エクセル）
- 回答数8
- ありがとう数8
- みんなの回答 （8）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (7)
- tom04
- ベストアンサー率49% (2537/5117)
親の仇のように顔を出します。 Excel2003以前では EOMONTH関数は分析ツールなのでVBAではダメなのかもしれません。 ＞myMax = Day(WorksheetFunction.EoMonth(Range("C1"), 0)) の行を ＞myMax = Day(DateSerial(Year(Range("C1")), Month(Range("C1")) + 1, 1) - 1) に変更してみてください。 おそらくこれで動くと思います。m(_ _)m
お礼
この度はありがとうございました。 VBAに関してはあまり詳しくない為、勉強になりました。 おっしゃる通りです、これでは私の勉強になりません。頼りすぎました。 今後、自分で勉強し今度は自分でしっかりとVBAを組める様に努力します。 その上で分からない事があれば、また宜しくお願いします。 この度は長い間付き合って下さり感謝申し上げます。
補足
いえいえ、とんでもございません。非常に感謝しています。 仮にB９～B３９の何処かの場所を「０（ゼロ）」と入力すると、現在ではゼロと入力したセルの下のセルから連続データ「１、２、３、４・・・」となりますが、ゼロの場合だけ、永遠とゼロの値が自動入力される事も出来るのでしょうか？
- tom04
- ベストアンサー率49% (2537/5117)
No.5の補足 ＞月末の３１日（年月によっては２８～３０日）を手動で空白状態にしていた場合、C1セルを更新した際B9～も空白状態にする事は出来るのでしょうか？ の件について・・・ ん～～～ 最初の質問が C1セルに変更があれば、 B9～B39セルに数値があった場合は最後の番号の次からの連番を表示 なければ「1」からの連番を表示！ という内容だっと解釈しています。 そうなるとNo.5の補足と最初の質問は矛盾してしまいますよね？ どちらがご希望なのでしょうか？ いずれにしても両方の操作はできませんので、どちらか一方の操作だけになると思います。 ※ イマイチ何をしたいのかこちらでは理解できていませんので、 質問と回答でいささか行き違いがあるように思えるのですが。 とりあえず文面から理解できる範囲での回答ですので、 この程度でごめんなさいね。m(_ _)m
お礼
この度はありがとうございました。 VBAに関してはあまり詳しくない為、勉強になりました。 おっしゃる通りです、これでは私の勉強になりません。頼りすぎました。 今後、自分で勉強し今度は自分でしっかりとVBAを組める様に努力します。 その上で分からない事があれば、また宜しくお願いします。 この度は長い間付き合って下さり感謝申し上げます。
補足
すいません、現在のVBAで満足です。ありがとうございました。 最後の補足になると思いますが、このVBAをエクセル２００３で使用すると「オブジェはこのプロパティまたはメソッドをサポートしていません。」と表示され「myMax = Day(WorksheetFunction.EoMonth(Range("C1"), 0)) 」の部分で止まってしまいます。エクセル２０１３はちゃんと動きます。 宜しければご教授お願いします。
- tom04
- ベストアンサー率49% (2537/5117)
続けてお邪魔します。 B列の消去・R列の対応もやってみました。 尚、A列が「空白の場合・・・」の条件はおそらく、この月末部分の対応ではないかと思いましたので 勝手にその部分は割愛しています。 必要であればその部分も追加しておいてください。 Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long, k As Long, myNum As Long, myMax As Long If Intersect(Target, Range("C1,B9:B39,R9:R39")) Is Nothing Or Target.Count > 1 Then Exit Sub Application.EnableEvents = False If IsDate(Range("C1")) Then myMax = Day(WorksheetFunction.EoMonth(Range("C1"), 0)) End If With Target Select Case .Column Case 3 If IsDate(.Value) = True And .Value > Date - Day(Date) Then myNum = Cells(40, "B").End(xlUp) Range("B9:B39").ClearContents For i = 1 To myMax Cells(i + 8, "B") = myNum + i Next i End If Case 2 k = .Row If .Value = "" Then Range(Cells(k, "B"), Cells(39, "B")).ClearContents Else For i = k + 1 To myMax + 8 Cells(i, "B") = Cells(i - 1, "B") + 1 Next i End If Case Else k = .Row Range(Cells(k + 1, "R"), Cells(39, "R")).ClearContents Range(Cells(k + 1, "R"), Cells(myMax + 8, "R")) = .Value End Select End With Application.EnableEvents = True End Sub 徐々にご希望の形に近づいているでしょうかね？m(_ _)m
お礼
この度はありがとうございました。 VBAに関してはあまり詳しくない為、勉強になりました。 おっしゃる通りです、これでは私の勉強になりません。頼りすぎました。 今後、自分で勉強し今度は自分でしっかりとVBAを組める様に努力します。 その上で分からない事があれば、また宜しくお願いします。 この度は長い間付き合って下さり感謝申し上げます。
補足
凄いですね、感動しました。 非常に素晴らしい形になりました、感謝申し上げます。 月末の３１日（年月によっては２８～３０日）を手動で空白状態にしていた場合、C1セルを更新した際B9～も空白状態にする事は出来るのでしょうか？
- tom04
- ベストアンサー率49% (2537/5117)
No.2・3です。 小の月の対応ができたいなかったようでごめんなさい。 もう一度コードを載せてみます。 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,R9:R39")) Is Nothing Or Target.Count > 1 Then Exit Sub Application.EnableEvents = False With Target Select Case .Column Case 3 If IsDate(.Value) = True And .Value > Date - Day(Date) Then myNum = Cells(40, "B").End(xlUp) For i = 1 To Day(WorksheetFunction.EoMonth(.Value, 0)) '←この行を変更（C1セルの月末数値まで） Cells(i + 8, "B") = myNum + i Next i End If Case 2 k = .Row If .Value = "" Then Range(Cells(k, "B"), Cells(39, "B")).ClearContents Else For i = k + 1 To 39 If Cells(i, "A") <> "" Then Cells(i, "B") = Cells(i - 1, "B") + 1 End If Next i End If Case Else k = .Row Range(Cells(k + 1, "R"), Cells(39, "R")) = .Value End Select End With Application.EnableEvents = True End Sub ※ 月末部が表示されている場合の消去は不要なのか？ ※ R列も同様に対応しなければならないのか？ という疑問を持ちつつ・・・m(_ _)m
お礼
この度はありがとうございました。 VBAに関してはあまり詳しくない為、勉強になりました。 おっしゃる通りです、これでは私の勉強になりません。頼りすぎました。 今後、自分で勉強し今度は自分でしっかりとVBAを組める様に努力します。 その上で分からない事があれば、また宜しくお願いします。 この度は長い間付き合って下さり感謝申し上げます。
補足
素早い対応ありがとうございます。 ※ 月末部が表示されている場合の消去は不要なのか？ ※ R列も同様に対応しなければならないのか？ そうですね、これも必要になりますね。上記２つが出来れば完璧なVBAですね。
- tom04
- ベストアンサー率49% (2537/5117)
No.2です。 前回のコード内の ＞If IsDate(.Value) = True And .Value > Date Then を ＞If IsDate(.Value) = True And .Value > Date - Day(Date) Then に変更してください。 もう一度以前の質問を拝見すると、こうしないとお望み通りにならないと思います。 どうも失礼いました。m(_ _)m
お礼
この度はありがとうございました。 VBAに関してはあまり詳しくない為、勉強になりました。 おっしゃる通りです、これでは私の勉強になりません。頼りすぎました。 今後、自分で勉強し今度は自分でしっかりとVBAを組める様に努力します。 その上で分からない事があれば、また宜しくお願いします。 この度は長い間付き合って下さり感謝申し上げます。
補足
すいません、わざわざありがとうございます。 残り１つ不具合があります。２９～３１日が無い月（２０１４年２月など）や他の３１日が無い月の部分も表示されてしまうのですが・・・。
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは！ 今までの質問を拝見しましたが、それぞれの回答で解決済みだと思い 敢えて顔を出しませんでした。 今までの質問を総合的にやってみると、↓のような感じでよいのでしょうかね？ 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,R9:R39")) Is Nothing Or Target.Count > 1 Then Exit Sub Application.EnableEvents = False With Target Select Case .Column Case 3 If IsDate(.Value) = True And .Value > Date Then myNum = Cells(40, "B").End(xlUp) For i = 1 To 31 Cells(i + 8, "B") = myNum + i Next i End If Case 2 k = .Row If .Value = "" Then Range(Cells(k, "B"), Cells(39, "B")).ClearContents Else For i = k + 1 To 39 If Cells(i, "A") <> "" Then Cells(i, "B") = Cells(i - 1, "B") + 1 End If Next i End If Case Else k = .Row Range(Cells(k + 1, "R"), Cells(39, "R")) = .Value End Select End With Application.EnableEvents = True End Sub ※ ご希望通りの動きにならなかったらごめんなさいね。m(_ _)m
お礼
この度はありがとうございました。 VBAに関してはあまり詳しくない為、勉強になりました。 おっしゃる通りです、これでは私の勉強になりません。頼りすぎました。 今後、自分で勉強し今度は自分でしっかりとVBAを組める様に努力します。 その上で分からない事があれば、また宜しくお願いします。 この度は長い間付き合って下さり感謝申し上げます。
- mt2008
- ベストアンサー率52% (885/1701)
提示されたコードは話に出てこないA列やR列などが出てきて何がしたいのか良く解らなかったので１から作り直しました。 こんな感じです。 Private Sub Worksheet_Change(ByVal Target As Range) '複数セルが更新された場合は処理なし If Target.Count > 1 Then Exit Sub Application.EnableEvents = False If Target.Address = "$C$1" And IsDate(Target) Then '変更したのがC1で日付型の値だった場合の処理 '最終値を取得 nData = 0 If WorksheetFunction.Count(Range("B9:B39")) > 0 Then nData = Range("B40").End(xlUp).Value End If '最終値の続きの値をB9:B39に入れる For i = 1 To 31 nData = nData + 1 Cells(8 + i, "B") = nData Next i ElseIf Target.Column = 2 And Target.Row >= 9 And Target.Row <= 39 Then '変更したのがB9:B39のどれかだった場合の処理 If Target = "" Then 'セルの値を空白にしたらそれ以下のセルも空白にする Range("B" & Target.Row & ":B39") = "" ElseIf Target = 1 Then 'セルの値を1にしたらそれ以下のセルは1からの連番にする nData = 0 For j = Target.Row To 39 nData = nData + 1 Cells(j, "B") = nData Next j End If End If Application.EnableEvents = True End Sub
お礼
この度は回答ありがとうございました。 参考になりました、今後VBAに関して勉強し自分でしっかりコードが組める様に努力します。 この度はありがとうございました。
お礼
この度はありがとうございました。 VBAに関してはあまり詳しくない為、勉強になりました。 おっしゃる通りです、これでは私の勉強になりません。頼りすぎました。 今後、自分で勉強し今度は自分でしっかりとVBAを組める様に努力します。 その上で分からない事があれば、また宜しくお願いします。 この度は長い間付き合って下さり感謝申し上げます。