[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『指定した番号の行を取得したい ※列は固定』（にのさん）
12/23『vlookupで飛ばした先のシートを自動印刷したい』で質問したものです。
前回の投稿実行はできましたが、期待する結果がでなかったので、質問させていただきました。
支出伺いを毎回印刷するのが手間なため、自動処理したいと思っています。
シートは「入力シート」、「当月支払シート」、「未払いシート」に分かれており、当月支払か未払いかは入力シートの15列目が空欄かどうかで判断できます。
入力シートに記入されている番号は一定ではなく、
1
2
3
3
4
5
のようになることもあるため、変数にいれた数字が存在する行番号の取得も必要になります。列は2列目固定です。
現在コード
Private Sub 印刷_Click()
Dim 番号 As Integer
Dim r_num As Integer
a = TextBox1.Value
n = TextBox2.Value
For 番号 = a To n
r_num = Sheets("入力").Range("B:B").Find(番号).Row
'ここがちゃんと見つけられていないと思います。前回のアドバイスでfindを
を使わないほうがいいと言われたため、できれば使わない方法も教えてほしいです。
If Sheets("入力").Cells(r_num, 15).Value = "" Then
Sheets("印刷様式 (当月)").Range("C1").Value = 番号
Sheets("印刷様式 (当月)").PrintOut
Else
Sheets("印刷様式 (未払い)").Range("C1").Value = 番号
Sheets("印刷様式 (未払い)").PrintOut
End If
Next 番号
Unload Me
End Sub
■イミディエイトウィンドウ結果
?Sheets("入力").Range("B:B").Find(番号).Row
3002 ※期待する値は２です。3002はデータが入っている最終行です。 ご教授お願いします。
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
Private Sub 印刷_Click()
Dim myDic As Object
Set myDic = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = TextBox1.Value To TextBox2.Value
myDic.Add i, ""
Next
Dim inputSht As Worksheet
Dim targetSht As Worksheet
Set inputSht = Sheets("入力")
Dim searchRng As Range
With inputSht
Set searchRng = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))
End With
Dim rng As Range
For Each rng In searchRng
If myDic.Exists(rng.Value) Then
Select Case rng.Offset(0, 13).Value
Case "": Set targetSht = Sheets("印刷様式 (当月)")
Case Else: Set targetSht = Sheets("印刷様式 (未払い)")
End Select
With targetSht
.Range("C1").Value = rng.Value
.PrintOut
End With
End If
Next
Unload Me
End Sub
（tkit） 2021/12/27(月) 12:01
表現がおかしい。
参照するシート名が解らなければ、vlookup関数は使えないと思うのだが。
飛ぶわけないし・・・。
それにvlookupで。セルの位置は解りません。
期待する参照セルの値を返すだけだと思いますけど。
何列目を返すかは、作者が指定するするから、事前に知っているはず。
indexでも使かう？？
Matchなら、範囲の何の何番目を探すかは解ります。
（思考が変） 2021/12/27(月) 13:17
参照するシート名が解らなければ、vlookup関数は使えないと思うのだが
⇒入力シートの15列目が空白ならば Sheets("印刷様式 (当月)")
そうでなければSheets("印刷様式 (未払い)")のC1セルに飛ぶようにしたつもりですが、そういう意味ではないですか？
（にのさん） 2021/12/27(月) 13:47
だから、飛ばないって・・・。
参照するシートを変えたって意味。
例えば、Ａ１が未入力なら、Ｂ１の値を足しこむとかの感じ。
（思考が変） 2021/12/27(月) 13:54
勝手な憶測に過ぎませんが、きっとシートにC1セルの値を参照するVLOOKUP関数が記入されていて
マクロでC1セルの値を変えることで、それと連動してVLOOKUP式の入力してあるセルの値を変えたいのかな
と理解しています。
＞にのさん
それはそうと、前回はエラーが出る／出ないの話だけで終わっていましたが
変数aとnが宣言されていないのでそれだけでもエラー含み（文字列が入っているとすぐあとのForNextループも回りません）ですし
FindメソッドはCtrl+Fの検索と同一の処理なので、細かくプロパティを設定せずに実行すると
その前にCtrl+Fで検索したりしていると同一の条件で検索してしまうため
部分一致検索のつもりが全体一致検索をしていたり、値で検索するつもりが数式で検索してしまったり
という、想定しないミスが発生する恐れがあるという点でも推奨されません。
また、
For 番号 = a to n (処理) Next
をa=1,n=5でループを回すと、変数「番号」には1,2,3,4,5が各1回入ります。
1,2,3,3,4,5という値があったときに二つ目の3は無視されます。
もし1〜5の間の値になっているセルをすべて処理したいのであれば、コードの設計が間違っています。
どんな処理をお望みなのかが当初の文面から察することが難しいので、ひとまずお返事はここまでになります。
（きまぐれおじさん） 2021/12/27(月) 14:22
勝手な憶測に過ぎませんが、きっとシートにC1セルの値を参照するVLOOKUP関数が記入されていて
マクロでC1セルの値を変えることで、それと連動してVLOOKUP式の入力してあるセルの値を変えたいのかな
と理解しています。
⇒そのとおりです。説明が足りずすみません。
findのデメリットは前回教えていただいたサイトで確認しました。できれば変更したいと思っていたのですが、すぐには対応できませんでした。
1,2,3,3,4,5という値があったときに二つ目の3は無視されます。
⇒各1回入ればOKな仕様になっているので、大丈夫です。ありがとうございます。
（にのさん） 2021/12/27(月) 14:38
どう変更すれば各数字を1回だけ読み込むようにできるのでしょうか。
For Each rng In searchRng
If myDic.Exists(rng.Value) Then
Select Case rng.Offset(0, 13).Value
Case "": Set targetSht = Sheets("印刷様式 (当月)")
Case Else: Set targetSht = Sheets("印刷様式(未払)")
End Select
ここの範囲で設定できるとは思うのですが、変更コードが思いつきません・・・。
（にのさん） 2021/12/27(月) 16:26
＞ここの範囲で設定できるとは思うのですが、変更コードが思いつきません・・・。
では、貴方が提示したコードを箇条書きで説明してください。 （tkit） 2021/12/27(月) 17:00
参考に
Private Sub 印刷_Click()
Dim 番号 As Long
Dim r_num As Variant
Dim a As Long
Dim n As Long
a = TextBox1.Value
n = TextBox2.Value
With Sheets("入力")
For 番号 = a To n
r_num = Application.Match(番号, .Range("B:B"), 0)
If IsError(r_num) Then
MsgBox 番号 & " が見つかりません", vbExclamation
Else
If .Cells(r_num, 15).Value = "" Then
Sheets("印刷様式 (当月)").Range("C1").Value = 番号
Sheets("印刷様式 (当月)").PrintOut
Else
Sheets("印刷様式 (未払い)").Range("C1").Value = 番号
Sheets("印刷様式 (未払い)").PrintOut
End If
End If
Next 番号
End With
Unload Me
End Sub
（ピンク） 2021/12/27(月) 23:38
（tkit）さん
こんな感じでしょうか？
For Each rng In searchRng
'Set searchRng = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))で指定したB1から
B最終行の範囲内で下記処理を繰り返す
If myDic.Exists(rng.Value) Then
’ここがよくわからないです。
Select Case rng.Offset(0, 13).Value ’B列から13列右に移動
Case "": Set targetSht = Sheets("印刷様式 (当月)") 'ブランクの場合当月シートで印刷
Case Else: Set targetSht = Sheets("印刷様式(未払)") ’ブランクでない場合未払シートで印刷
End Select
（にのさん） 2021/12/28(火) 09:18
（ピンク）さん ありがとうございます！
実行してみます
（にのさん） 2021/12/28(火) 09:20
（ピンク）さん 実行できました！
エラー処理までありがとうございます！
（にのさん） 2021/12/28(火) 10:44
＞こんな感じでしょうか？
何をやっているか分かれば、 貴方の求める事が実現できると思い、聞きました。 解説を加えます。
'▼Rangeオブジェクト変数(searchRng)の各セル(rng)を繰り返す
For Each rng In searchRng
'▼連想配列Dictionary(myDic)のキーにセルの値(rng.Value)が存在したら
If myDic.Exists(rng.Value) Then
'▼セル(rng)の同行、右に13列目のセルの値が
Select Case rng.Offset(0, 13).Value
'▼""(空文字列)だったら ： Worksheet変数(targetSht)にSheets("印刷様式 (当月)")を代入
Case "": Set targetSht = Sheets("印刷様式 (当月)")
'▼上記Case以外だったら ： Worksheet変数(targetSht)にSheets("印刷様式 (未払い)")を代入
Case Else: Set targetSht = Sheets("印刷様式 (未払い)")
End Select
'▼Worksheet変数(targetSht)
With targetSht
'▼のC1セルの値にセル(rng)の値を代入
.Range("C1").Value = rng.Value
'▼を印刷
.PrintOut
End With
End If
Next
当初は、番号に重複があることは提示されていて、処理については何も無かったですよね。 コードに判定を加えようと思いましたが、重複を無視する必要があるなら、そもそもシートに 入力されていることが問題なのでは、と思った次第です。 VBAで何でもかんでも出来てしまいますが、そもそもの表の設計を見直してからの方が良くないですかね。
もし、表設計が見直せなく、私が提示したコードを流用したいのであれば、私なら上記のコードに判定を 加えるのではなく、別途重複していないデータを準備するコードを加えます。
重複した場合のルールはどうなのかなど、私は面倒だと思うんですよね。 （tkit） 2021/12/28(火) 11:04
前提の説明不足でした。
重複があるのは、同じ支出伺いの中に税率の違うものが混在する場合です。
Ｃ列には常に1が入っており、税率が違うものが存在した場合は2を入力することでＢ列の数字が上と同じ数字を引っ張るよう数式を入れています。
右記のような感じです。
Ａ列：1-1 Ｂ列：1 Ｃ列：1 税率8% 単価 消費税 合計金額
Ａ列：1-2 Ｂ列：1 Ｃ列：2 税率10% 単価 消費税 合計金額
そのため、重複する数字が生まれます。非課税も入れると最大３つ重複します。
もし、表設計が見直せなく、私が提示したコードを流用したいのであれば、私なら上記のコードに判定を 加えるのではなく、別途重複していないデータを準備するコードを加えます。
⇒その発想はありませんでした。コードは思いつきませんが、1度Ｂ列を取得したあとに重複を削除し、その中から検索するということですね。
（にのさん） 2021/12/28(火) 11:29
であれば、ピンクさんが提示されたコードに、
C列の条件を加えた方が簡単ですね。
（tkit） 2021/12/28(火) 12:51
いろいろと付け足して下記コードに落ち着きました。
始めて数週間の超初心者なのでわからないことだらけでしたが、勉強になりました。C列の条件はまた考えてみます。
Private Sub CommandButton1_Click()
Dim プリンタ As Boolean
プリンタ = Application.Dialogs(xlDialogPrinterSetup).Show
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not Chr(KeyAscii) Like "[0-9]" Then
KeyAscii = 0
End If
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not Chr(KeyAscii) Like "[0-9]" Then
KeyAscii = 0
End If
End Sub
Private Sub 印刷_Click()
Application.ScreenUpdating = False
Dim 番号 As Long
Dim r_num As Variant
Dim a As Long
Dim n As Long
If TextBox1.Value = "" Or TextBox2.Value = "" Then
GoTo blank
End If
a = TextBox1.Value
n = TextBox2.Value
With Sheets("入力")
For 番号 = a To n
r_num = Application.Match(番号, .Range("B:B"), 0)
If IsError(r_num) Then
MsgBox 番号 & " が見つかりません", vbExclamation
Else
If .Cells(r_num, 15).Value = "" Then
Sheets("印刷様式 (当月)").Range("C1").Value = 番号
Sheets("印刷様式 (当月)").PrintOut
Else
Sheets("印刷様式 (未払い)").Range("C1").Value = 番号
Sheets("印刷様式 (未払い)").PrintOut
End If
End If
Next 番号
End With
Unload Me
blank: MsgBox "印刷範囲を入力してください"
Exit Sub
Application.ScreenUpdating = True
End Sub
（にのさん） 2021/12/28(火) 17:13
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.