[[20170222154600]] 『縦と横を探したい』(さくら) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『縦と横を探したい』(さくら)

 お分かりになる方がいらっしゃいましたらお願いします。

 下記データを上から順に横方向へをサーチしたいのですがどのようにすれば良いかがわかりません。

 1.検索したい箇所は行1で言うと、"5SC01"をsheet"fuyou_code"から検索しあった場合ににはa列に"不要"と入れたいです。
 いつもランダムでyからになったりもします。
 2行目だと"5sd02"を検索したいです
 2.検索場所はsheet"fuyou_code"  のa列にあります

	a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q
1	abcd	123456789	DUMY(GUARD A)			Y	Y								5SC01		
2	abcd	123456789	DUMY(GUARD A)				Y									5SD02	
3	abcd	123456789	DUMY(GUARD A)	Y	Y	Y	Y										6TA01
4	abcd	123456789	DUMY(GUARD A)	Y	Y	Y	Y										6TA02A
5	abcd	123456789	DUMY(GUARD A)	Y	Y	Y	Y										6TA03A
6	abcd	123456789	DUMY(GUARD A)	Y	Y	Y	Y										6TA06J
7	abcd	123456789	DUMY(GUARD A)	Y		Y							2SRC03				
8	abcd	123456789	DUMY(GUARD A)	Y		Y							2SRP03				
9	abcd	123456789	DUMY(GUARD A)	Y			Y						2SRP04A				
10	abcd	123456789	DUMY(GUARD A)	Y	Y	Y	Y							4SA999			
11	abcd	123456789	DUMY(GUARD A)	Y	Y	Y	Y							4SAA30			
12	abcd	123456789	DUMY(GUARD A)	Y	Y	Y	Y							4SAA33			

記載漏れがありましたらごめんなさい。どうぞ宜しくお願い致します。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 H〜Y列範囲、範囲内データ一つとして。

 A1 =IFERROR(IF(COUNTIF(fuyou_code!A:A,LOOKUP("ー",H1:Y1)),"不要",""),"")
 
(GobGob) 2017/02/22(水) 16:17

 GobGob様

 御回答ありがとうございました。

 説明不足で申し訳ございません、今マクロを組んでいましてその途中なのでvbaで教えて頂けないでしょうか?

 宜しくお願い致します。

(さくら) 2017/02/22(水) 16:22


 >今マクロを組んでいましてその途中なのでvbaで教えて頂けないでしょうか?

 その場合はその途中までのコードを提示すべきでは?
 作ったコードの中でどこが分からないのかを質問すべきだと思います。

 作ってる途中ってのが嘘で、丸投げしたいなら別ですが。
(nazo) 2017/02/22(水) 16:34

 >行1で言うと、"5SC01" 、2行目だと"5sd02"

 この意味は 1行目では "5SC01" 『だけを』検索するのですか?
 この場合、データシートのそれぞれの行で何を検索するかという判断が必要ですが、"fuyou_code"シートのA列の
 行番号を用いる?

 それとも、どこかの行のどこかのセルに、"fuyou_code"シートのA列にある文字列の『いずれかが』あれば
 fuyou_codeシートの該当のコードに 不要 とセットするのですか?

( β) 2017/02/22(水) 16:41


 もう1つ。

 不要といれるのは データがあるシートのA列ですか? fuyou_codeシートのA列ですか?

( β) 2017/02/22(水) 16:51


 β様

 いつもお世話になりありがとうございます。

 おっしゃる通りです。

 >この意味は 1行目では "5SC01" 『だけを』検索するのですか?
 今回はたまたま"5SC01"  だけですが、横方向に続いています。

 >この場合、データシートのそれぞれの行で何を検索するかという判断が必要ですが、"fuyou_code"シートのA列の 行番号を用いる?
 下記の様にsheet "fuyou_code"があります

 	5SC01
 	5SC04P
 	5SD01
 	5SD02
 	6TB06F
 	6TB9930439
 	6TB9930765
 	6TB9940621
 	6TB9940652
 	6TB9950498
 	6TC9930067

 >それとも、どこかの行のどこかのセルに、"fuyou_code"シートのA列にある文字列の『いずれかが』あれば
 fuyou_codeシートの該当のコードに 不要 とセットするのですか?

 その通りです。  いずれかがあれば  不要  とセットしたいです。

 >不要といれるのは データがあるシートのA列ですか? fuyou_codeシートのA列ですか?

  fuyou_codeシートのA列にあります。

 宜しくお願い致します。
(さくら) 2017/02/22(水) 16:57

 う〜ん・・よくわからなくなりました。

 >検索場所はsheet"fuyou_code"  のa列にあります

 とありますので fuyou_code の A列には、不要なコードが、ずっと下のほうまで列挙されているんですよね?

 で、

 >不要といれるのは --> fuyou_codeシートのA列にあります。

 あります という表現がよくわかりませんが、fuyou_codeシートのA列に 不要 といれると、元々あったコードが
 上書きされてしまいますが?

 そうではなく、データシートの A列、アップされたサンプルでは abcd と書かれているところに
 不要 と上書きするんですか?

( β) 2017/02/22(水) 17:26


 とりあえず要件を誤解しているかもしれませんが、データがあるシートのA列に不要と書きこみます。
 不要コードを探し出す開始列、先頭の Const で規定していますので、実際の列記号になおしてください。
 また、データがあるシート名を "Sheet1" にしています。ここも実際のものに直してください。

 Sub Sample()
    Const STCOL As String = "I" '★コードの検索開始列
    Dim a As Range
    Dim r As Range
    Dim c As Range
    Dim v As Variant
    Dim z As Variant

    With Sheets("fuyou_code")
        Set a = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    End With

    With Sheets("Sheet1")                               '★データがあるシート
        With .Range("A1", .UsedRange)
            v = .Columns(1).Value
            On Error Resume Next
            Set r = .Offset(, Columns(STCOL).Column - 1).Resize(, .Columns.Count - (Columns(STCOL).Column - 1)).SpecialCells(xlCellTypeConstants)
            On Error GoTo 0
            If r Is Nothing Then
                MsgBox "検索すべき値がありません"
                Exit Sub
            End If
            For Each c In r
                z = Application.Match(c.Value, a, 0)
                If IsNumeric(z) Then v(c.Row, 1) = "不要"
            Next
            .Columns(1).Value = v
        End With
    End With

 End Sub

( β) 2017/02/22(水) 17:52


Sub main()
    Dim dic As Object
    Set dic = CreateObject("scripting.dictionary")
    For Each c In Sheets("fuyou_code").Range("A:A").SpecialCells(xlCellTypeConstants)
        dic(c.Value) = True
    Next c
    For Each c In Cells.SpecialCells(xlCellTypeConstants)
        If dic(c.Value) = True Then c.Offset(, 1 - c.Column).Value = "不要"
    Next c
End Sub
(mm) 2017/02/22(水) 18:14

 失礼します。

 To mmさん

 If dic(c.Value) = True Then 

 だめじゃないですけど、Dictionary を扱う上では If dic.Exists(c.Value) Then  が『正統派』ですかね。

 登録のないキーに対して参照すると、そのキーで、空白の値を持つエントリーが追加されてしまいますし。
 (このコードでは実害ないですけど)

( β) 2017/02/22(水) 21:50


 β様、mm様

 出来ました!ありがとうございました。

 1つ教えてください。

 下記データがありますが、

 a      b        c              d  
 	6TB05B	 NN	

 作成済	部位コード ユニット	        名称
 不要	K0210	 K0210@@@@@	DUMY(GUARD A)
 不要	K0210	 X420121250	DUMY(GUARD A)

 下のマクロを動かすとエラー91オブジェクト変数またはwith ブロックが・・・と出てしまうのですが何故かがわかりません。

 Dim w As Range   
 y = Range("b:b").Find("部位コード").Row     '行の取得

 下のメッセージですと、行取得ができます。

    MsgBox Range("b:b").Find("部位コード").Row

(さくら) 2017/02/23(木) 08:32


 すみません、Dim w As Range  ではなく  Dim y As Rangeです。

 Dim y As Range

    y = Range("b:b").Find("部位コード").Row     '行の取得

 でエラー91が出ます。
(さくら) 2017/02/23(木) 08:34

 まず 左辺 = 右辺 で値のセットをするわけですが、左辺が Object型(Range だったり WorkSheet だったり)の場合は

 Set 左辺 = 右辺 と、Set を使う必要があります。
 当然 左辺も右辺も Object型である必要があります。

 今回のケースでは As Range という宣言ですから y も Range ですし、右辺の Range("b:b").Find("部位コード").Row も Range型でなければいけません。

 そうではなく、右辺は 行番号(数値)ですから

 Dim y As Long と規定して

 y = Range("b:b").Find("部位コード").Row     '行の取得

 と記述してください。

 次に Findメソッドのことを。(一般論として。今回の要件では問題ありません)

 Find("部位コード")  これは "部位コード" という値(引数でいうと What:= にあたります)で検索をかけているわけですが、 
 Findメソッド与える引数には、他にも重要なものがあります。かつ、それらの条件が省略されると
 直前にエクセルで実行されたマクロ処理のFindメソッドやReplaceメソッドで使われたものが継承されます。
 あるいは、直前にシート上の操作で 検索・置換 を行ったときの条件が継承されます。

 とくに重要なのが 完全一致なのか部分一致なのか。
 指定を省略してしまうと、このコードが 完全一致で検索しているのか、部分一致で検索しているのか、
 保証がされない。その時の状況次第ということになります。

 ですから、最低でも、Find(What:="部位コード",LookAt:=xlWhole) といったっように LookAt を明示することが必要です。

 で、もう1つ。これも重要なことなんですが、検索で見つからなかった場合(今回はそういうことはないですけど)
 結果が Nothing になります。そうすると、Nothing の Row というのはありえませんから、エラーになります。
 今後、Findメソッドを使う場合は、検索結果があったかどうか(Nothing ではなかったか)を判定して
 適切な分岐処理をするようにしてください。

( β) 2017/02/23(木) 09:18


 β様

 出来ました!

 大変勉強になりました!ありがとうございました!!
(さくら) 2017/02/23(木) 10:14

 すみません、先程は動きましたが

 Set r = .Offset(, Columns(STCOL).Column - 1).Resize(, .Columns.Count - (Columns(STCOL).Column - 1)).SpecialCells(xlCellTypeConstants)

 の部分の真ん中

 Resize(, .Columns.Count - (Columns(STCOL←

 STCOLでコンパイルエラー定数式が必要です。と出てしまいました。

 少し弄りまして下記の様になっています。

    Dim a As Range
    Dim r As Range
    Dim v As Variant
    Dim w As Range

    Const STCOL As String = "I" '★コードの検索開始列

    With Sheets("fuyou_code")

        Set a = .Range("A1", .Range("A" & Rows.Count).End(xlUp))

    End With

    '結果を表示するB列を空にする
    Columns("a").Insert

    With Sheets("FD15T-21_YANA_UNIT_K0210")                               '★データがあるシート

        With .Range("A1", .UsedRange)

            v = .Columns(1).Value

            'On Error Resume Next

            Set r = .Offset(, Columns(STCOL).Column - 1).Resize(, .Columns.Count - (Columns(STCOL).Column - 1)).SpecialCells(xlCellTypeConstants)

 申し訳ございません、教えて頂きたくよろしくお願い致します。
(さくら) 2017/02/23(木) 16:50

 On Error Resume Nextを'On Error Resume Next  にしていた為かと思い外してみましたがダメでした。   

 Dim a As Range
    Dim r As Range
    Dim v As Variant
    Dim w As Range

    Const STCOL As String = "I" '★コードの検索開始列    
    With Sheets("fuyou_code")    
        Set a = .Range("A1", .Range("A" & Rows.Count).End(xlUp))        
    End With    
    '結果を表示するB列を空にする
    Columns("a").Insert    
    With Sheets("FD15T-21_YANA_UNIT_K0210")                              '★データがあるシート    
        With .Range("A1", .UsedRange)        
            v = .Columns(1).Value            
            On Error Resume Next            
            Set r = .Offset(, Columns(STCOL).Column - 1).Resize(, .Columns.Count - (Columns(STCOL).Column - 1)).SpecialCells(xlCellTypeConstants)            
            On Error GoTo 0            
            If r Is Nothing Then
                'MsgBox "検索すべき値がありません"
                Exit Sub
            End If
            For Each c In r                
                'MsgBox c           
                z = Application.Match(c.Value, a, 0)
                If IsNumeric(z) Then v(c.Row, 1) = "不要"
            Next
            .Columns(1).Value = v
        End With
    End With
(さくら) 2017/02/23(木) 17:35

 >先程は動きましたが・・・・STCOLでコンパイルエラー定数式が必要です。と出てしまいました。

 『少し弄りまして』といわれるコードが再掲されたコードであれば、それはありえません。
 こちらで、そのままコピペしてコンパイルしましたがコンパイルエラーにはなりません。

 本当に、そういったエラーでしたか? 別のエラーだったということはないですか?

 > On Error Resume Nextを'On Error Resume Next  にしていた為かと思い

 コメントアウトしちゃ、だめですよ。もし、該当の領域がすべて空白なら(実際にはそういうことはないのでしょうけど)
 エラーになる、それをバイパスするためのエラートラップですから。
 ただし、この件とコンパイルエラーは関係がありません。

 ★それより、なぜ、変数 c や z を消したんですか? モジュールの先頭に Option Explicit は記述されていないのでしょうか?
  記述されていなかったとしても、 c や z を消してしまう理由はないですよね?

( β) 2017/02/23(木) 17:54


 >Const STCOL As String = "I" '★コードの検索開始列 

 ここを変えているということはないですか?
 もし、変えているなら、どう変えたかを教えてください。

( β) 2017/02/23(木) 18:18


 そのまま貼りつけたのので、貼りつけた通りなんですが・・

 私もβ様がおっしゃった通り別subを作成したら動きまして。。謎です。
 なのでcallで呼び出して作業をする事にしました。

 >コメントアウトしちゃ、だめですよ。
 ごめんなさい どこかでOn Errorは無い方がエラーが分かるので良いと見た気がして外してしまいました。最初は問題無かったんです。

 >★それより、なぜ、変数 c や z を消したんですか? モジュールの先頭に Option Explicit は記述されていないのでしょうか?
  記述されていなかったとしても、 c や z を消してしまう理由はないですよね?

 β様のマクロを少し改造してこのマクロの上に定義してあった為これには見えないだけです。消してないです。
 このマクロ長いんです。ごめんなさい。
(さくら) 2017/02/23(木) 18:28

 一応、解決 ということでよろしいのでしょうかね。

 ちなみに 【コンパイルエラー定数式が必要です】は、以下のような記述に対してでるエラーです。

 Const 定数名 As String = Range("A1").Address

 Range("A1").Address は "$A$1" ですから、なんとなく

 Const 定数名 As String = "$A$1"  というのと同じだと、そう思うかもしれませんが、こちらはエラーにはなりません。

 要は、Cosnt 定数名 に与えるものは【文字通り】定数 "Hoge" や 123 等でなければいけません。
 Range("A1").Address は あくまで Range("A1") というオブジェクトが持っている Address というプロパティで
 これは、定数ではなく変数です。(たまたま、その中身が "$A$1" という値だというだけです)

( β) 2017/02/23(木) 18:41


 β様

 はい、ありがとうございました。

 Const 初めて使いました。
 まだまだ勉強不足ですね。。頑張ります!

 又何かありましたら宜しくお願い致します。
(さくら) 2017/02/23(木) 19:52

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.