マクロ 選択範囲と同一の範囲を違う列で選択する

質問

いつも回答して頂き感謝感謝です。
今回の質問ですが、下記のマクロで日付が入力されている列の範囲選択が出来ましたが、この選択範囲を元に列を任意に変更し同等の行範囲で選択したいのですがどうしたらよろしいでしょうか？思いつく範囲でマクロを記述しましたがさっぱりでした。御指導の程宜しくお願い致します。

・B4から下方向に日付が連続で入力されている。
・C3から右方向に商品名が入力されている。
・上記の交差する箇所に商品の使用回数が入力されている。

Sub 範囲の選択5()
Dim Date1 As Date
Dim Date2 As Date
Dim 商品名 As Variant
Dim b1 As Variant
Dim b2 As Variant
Dim b3 As Variant
With Worksheets("集計用")
s1:
Date1 = Application.InputBox("最初の日付を2012/12/1のように入力してください。")
Set b1 = .Columns("B").Find(Date1, , xlValues, 1)
If b1 Is Nothing Then MsgBox "入力した日付が見当たりません。再度入力して下さい。": GoTo s1
s2:
Date2 = Application.InputBox("最後の日付を2012/12/31のように入力してください。")
Set b2 = .Columns("B").Find(Date2, , xlValues, 1)
If b2 Is Nothing Then MsgBox "入力した日付が見当たりません。再度入力して下さい。": GoTo s2
.Range(b1, b2).Select
s3:
商品名 = Application.InputBox("商品名を入力して下さい。")
Set b3 = .Rows("3").Find(商品名, , xlValues, 1)
If b3 Is Nothing Then MsgBox "入力した日付が見当たりません。再度入力して下さい。": GoTo s3
.Range(b3).Select
End With
End Sub

回答No.1

tsubuyuki · ベストアンサー

頑張っていらっしゃるようで＾＾
ここまで思いつけるのはなかなか素晴らしい限りです＾＾

さて、お節介を少々させていただきます。
まず、条件の整理から。

> ・B4から下方向に日付が連続で入力されている。
とのことですが、コードを拝見する限り、開始日も終了日も
「Ｂ列を上から検索して、最初にヒットするセル」を持ってきていますね。
つまり、日付が重複することは無い、との判断で良いですか？
それが前提なら、このやり方で問題ないだろうと思います。

続いて、やりたいことの整理。
行範囲「開始日から終了日」・列「商品名」の範囲
つまり検索結果が
・開始日：１２／１＝Ｂ４セル
・終了日：１２／１０＝Ｂ１３セル
・対象商品：Ｄ３セル
と返ってきた場合、最終的に「Ｄ４：Ｄ１３」の範囲を選択して、マクロ終了。
という事で良いでしょうか。

だとすると、考え方として（ご自身のコードから少々変更して）
・変数r1,r2,c1を整数Integerあるいは長整数Longで宣言
・開始日を指定（インプットボックス）=>Ｂ列から検索
　　=>ヒットしたセルの行番号を取得、変数（開始行：ｒ１）に格納
　　r1 = b1.Row

・終了日を指定（インプットボックス）=>Ｂ列から検索
　　=>ヒットしたセルの行番号を取得、変数（終了行：ｒ２）に格納
　　r2 = b2.Row

・商品を指定（インプットボックス）=>３行目から検索
　　=>ヒットしたセルの列番号を取得=>変数（商品列：ｃ１）に格納
　　c1 = b3.Column

・「商品列・開始行：商品列・終了行」の範囲を選択
Range(Cells(r1, c1), Cells(r2, c1)).Select

と、こんな感じの流れでしょうか。

好みを押し付けるのはよろしくないのですが、
まとめると以下のような感じになろうかと思います。
（検証しておりませんので、バックアップは取っておいてくださいね。）

Sub 範囲の選択5()
Dim Date1 As String, Date2 As String
Dim SName As String
Dim b1 As Variant, b2 As Variant, b3 As Variant

　　With Worksheets("集計用")
s1:
　　　　Date1 = Application.InputBox("最初の日付を2012/12/1のように入力してください。")
　　　　Set b1 = .Columns("B").Find(Date1, , xlValues, 1)
　　　　If b1 Is Nothing Then MsgBox "入力した日付が見当たりません。再度入力して下さい。": GoTo s1
　　　　r1 = b1.Row

s2:
　　　　Date2 = Application.InputBox("最後の日付を2012/12/31のように入力してください。")
　　　　Set b2 = .Columns("B").Find(Date2, , xlValues, 1)
　　　　If b2 Is Nothing Then MsgBox "入力した日付が見当たりません。再度入力して下さい。": GoTo s2
　　　　r2 = b2.Row

s3:
　　　　SName = Application.InputBox("商品名を入力して下さい。")
　　　　Set b3 = .Rows("3").Find(SName, , xlValues, 1)
　　　　If b3 Is Nothing Then MsgBox "入力した商品が見当たりません。再度入力して下さい。": GoTo s3
　　　　c1 = b2.Column

　　　　.Range(Cells(r1, c1), Cells(r2, c1)).Select
　　End With
End Sub

こんな感じですね。

個人的に思う事をもう一つ。
これだと、日付や商品が一致しない場合、ループから抜けられなくなるかもしれません。
キャンセルされた場合も同様ですね。
なので、この「一致しない=>キャンセル」の流れも考慮に入れておくと良いかもしれませんね。

お礼

返事が遅れて申し訳ありません。
私の質問内容から色々推測し、的確なアドバイスをして頂きありがとうございました。特定のセル番号のRowとかColumnを取り出して、指定してやる事も可能なんですね。勉強になりました。

個人的に思う事をもう一つ。
これだと、日付や商品が一致しない場合、ループから抜けられなくなるかもしれません。キャンセルされた場合も同様ですね。なので、この「一致しない=>キャンセル」の流れも考慮に入れておくと良いかもしれませんね。
上記のアドバイスですが、具体的にどのような記述を追加したらよろしいのでしょうか？頭が足りないので御指導願います。

あと、この質問で記載したマクロ記述は、OKWAVEの親切な回答者様の模範解答をほぼ全て使わせてもらっています。私はただ少し変更、又は、類似のマクロ作成の時の参考にさせてもらっている程度です。

回答No.4

tsubuyuki

Date1 = Application.InputBox("最初の日付を2012/12/1のように入力してください。")
　　　　　　　　If Date1 = "False" Then
　　　　　　　　　　　　MsgBox "キャンセルされました。", vbOKOnly
　　　　　　　　　　　　Exit Sub
　　　　　　　　End If

この部分のお話ですね。
失礼しました。
Date1は日付型で宣言された変数でしたね。
この場合、InputBoxがキャンセルされるた時の返り値は「０ゼロ」ですね。
よってここは「キャンセルされたらメッセージを出し、終了する」ですので、
　　If Date1 = 0 Then
の方がよろしいようです。

変数Date1が文字列型だったら、Falseと言う文字列が返ります。
私も混同していたようで、申し訳ないです＾＾；

お礼

返事が遅くなって申し訳ありませんでした。回答ありがとうございました。

回答No.3

tsubuyuki

> s1:
> 　　　　Date1 = Application.InputBox("最初の日付を2012/12/1のように入力してください。")
> 　　　　Set b1 = .Columns("B").Find(Date1, , xlValues, 1)
> 　　　　If b1 Is Nothing Then MsgBox "入力した日付が見当たりません。再度入力して下さい。": GoTo s1
> 　　　　r1 = b1.Row

お試しいただけるとわかりやすいのですが、
このままでいくと、変数b1が空っぽ（該当が無い）の時にs1の処理に戻ってしまって
ここから抜けられない状態になってしまいますね。（ｓ２・ｓ３も同様ですね。）
※日付の中にあり得ない文字列・・「2111/13/32」などを検索してみてください。
　該当が無い場合、無条件にs1に戻ってやり直してしまいますね。

なので、ここに（私なら）
「入力した日付が見つからなかったら、入力をやり直すか、止めるか」
の選択肢を入れます。

s1:
　　　　Date1 = Application.InputBox("最初の日付を2012/12/1のように入力してください。")
　　　　　　　　If Date1 = "False" Then
　　　　　　　　　　　　MsgBox "キャンセルされました。", vbOKOnly
　　　　　　　　　　　　Exit Sub
　　　　　　　　End If

　　　　Set b1 = .Columns("B").Find(Date1, , xlValues, 1)
　　　　　　　　If b1 Is Nothing Then
　　　　　　　　　　　　If MsgBox("入力した日付が見当たりません。" & vbNewLine & _
　　　　　　　　　　　　　　　　　"再度入力しますか？", vbYesNo) = vbYes Then
　　　　　　　　　　　　　　　　GoTo s1
　　　　　　　　　　　　Else
　　　　　　　　　　　　　　　　MsgBox "処理を中止しました", vbOKOnly
　　　　　　　　　　　　　　　　Exit Sub
　　　　　　　　　　　　End If
　　　　　　　　End If
　　　　r1 = b1.Row

具体的には、こう書きかえてやります。
これで、
・インプットボックスをキャンセル→マクロ終了
・検索結果が空白（該当が無い）→メッセージボックス表示（YesNo選択）
　　　Yes→入力やり直し
　　　No→マクロ終了
と言う流れが出来ました。
s2・s3に関しても同様のことが言えます。
特にs3は商品番号を「完全一致」で探しに行きますから、結構シビアな入力を求められますね。
なので、こう言った、キャンセルで抜ける処理も必要かなぁ、と思った次第です。

細かい話ですが、日付は文字列で持っているのですよね。
日付を表す数値で持っていると、検索の段階でエラーを返すかもしれません。
ここもちょっとだけ注意が必要ですね。

お礼

回答して頂きありがとうございます。
INPUTBOXでYes,Noを表示させ(こんな事出来るんですね。)、Ifで行き先を指定してやる事も可能なんですね。勉強になりました。

あと、日付の表示ですが、書式設定で日付が選択されているので、『If Date1 = "False" Then』の【False】を【Date】に変更して正常動作が可能になりました。少し疑問なんですが、ここは【Date】で良かったんですか？商品名の入力は【False】で実行し問題ありませんでした。

回答No.2

tom04

こんばんは！

直接の回答ではありませんが、考え方だけ！

今、B列のある行が選択されている状態だとして・・・

3行目の「商品」と一致する列を検索し、B列から○列右を選択！というやり方はどうでしょうか？

Dim j As Long
j = WorksheetFunction.Match(b3, Worksheets("集計用").Rows(3), False)
Selection.Offset(, j - 2).Select

といった感じのコードを追加してみては？

※　検証していませんので、ちゃんと動かなかったらごめんなさいね。m(_ _)m

お礼

返事が遅れて申し訳ありませんでした。
考え方でいろんな記述が出来るんですね。ありがとうございました。