[[20141117095817]] 『シート間の文字列を項目毎に判定後結果を別シート』(けん) ページの最後に飛ぶ

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

 

『シート間の文字列を項目毎に判定後結果を別シートに貼り付け』(けん)

はじめて投稿させていただきます。

当方VBAはまったくドシロウトでして、マクロの記録程度しかできません。
お手数おかけいたしますが、どなたかお分かりになるかた、書き方のご教授お願いいたします。

内容:

シート1のセル内の文字列(項目)を判定して正しい場合、別シートであるシート2に結果
(残り)のみコピー後、値の貼り付けを実行したいです。
別シート2には、予め項目は入力されております。
別シートの文字列はランダムに並び替わっており
シート間での文字列判定後、結果(残り)をシート2に貼り付けたいと考えています。

なお、シート1→シート2→シート3→シート4と前のシートから残りを
ドンドン減らしていくイメージです。

シート1

 項目	  数量	 出荷	 sh1残
 XYW56        100	 20	 80
 SDC236       200	 30	 170
 FFF11        300	 40	 260
 BNGJ598      400     50	 350
 KOLP021      500	 60	 540

シート2

 項目	   sh1残	 出荷	 sh3残
 XYW56         80	 20	 60
 BNGJ598      350	 10 	 340
 FFF11        260	 5	 255
 SDC236       170	 20	 150
 KOLP021      540	 30	 510

このような感じです。

お尋ね方法、記述の方法など失礼な点があるかもしれませんが、宜しくお願いいたします。

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


 シート2のsh3残はsh2残の間違い??

 要は
 sh?残 = [数量(前シート残)] - [出荷]
 になればいいんだよね?
 便宜的に項目をA列、sh?残をD列にした場合、

 1)D列は、値がいいの? 数式がいいの?
 2)実行するタイミングは、入力したとき? ボタンを押したとき?

(稲葉) 2014/11/17(月) 10:50


早速のご確認ありがとうございます。

シート2のsh3残はsh2残の間違い??→すみません、sh2の間違いです。
sh?残 = [数量(前シート残)] - [出荷]の通りです。

 1)D列は、値がいいの? 数式がいいの?
→値がいいです。

 2)実行するタイミングは、入力したとき? ボタンを押したとき?
→ボタンをおした時です。

 説明不足で申し訳ありませんでした。

(けん) 2014/11/17(月) 10:54


 ボタンを押す前はA:C列は入力済で、
 D列は空白ということですかね?

 で、ボタンを押すとSH1のD列が計算され、SH2のA列に従い、B列に転記される

 転記後、B列を入力しボタンを押すとD列に計算され・・・

 というフローになると思いますが、
 1)シートが複数あった場合、全部やり直すのでしょうか?
   例えばSH4まで有って、SH1が書きかえられたら、SH2〜4の結果も変わってきますよね?
   もし最新のシートのみでよいならば、「最新のシート」であるキーが必要です。
   例えば左のシートから見て、D列が入力されていない最初のシート等
 2)列に関しては、私の推測通りですか?
   A:D列のコードを提示するつもりです。

 ちなみに、値ではなく計算式で良ければ、VBAでなくても可能です。
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1396152783

(稲葉) 2014/11/17(月) 11:08


 知恵袋までご提示いただき、ありがとうございます。今回はVBAを希望しております。

 1)シートが複数あった場合、全部やり直すのでしょうか?
   例えばSH4まで有って、SH1が書きかえられたら、SH2〜4の結果も変わってきますよね?
   もし最新のシートのみでよいならば、「最新のシート」であるキーが必要です。
   例えば左のシートから見て、D列が入力されていない最初のシート等

 →最新のシートのみです。

 2)列に関しては、私の推測通りですか?
   A:D列のコードを提示するつもりです。
 →はい。推測通りです。A:D列で大丈夫です。

 変更になる場合、ご提示頂いたコードを参考に調べて、勉強します。
(けん) 2014/11/17(月) 11:23

 だから最新のシートはどうやって判断するんですか?
(稲葉) 2014/11/17(月) 11:36

 すみません。
 「左のシートから見て、D列が入力されていない最初のシート」
 ご提示頂きましたこの条件で問題ありません。
(けん) 2014/11/17(月) 11:39

 下記のコードでいかがですか?
    Sub ken()
        Dim sh1 As Worksheet, sh2 As Worksheet, n As Long
        Dim dic As Object, a, i As Long

        '//左のシートから見て、D列に入力がないシートを変数にセットする
        For n = 1 To Sheets.Count
            If Sheets(n).Range("D2").Value = "" Then
                If Sheets(n).Next Is Nothing Then MsgBox "次のシートがありません。": Exit Sub
                Set sh1 = Sheets(n)
                Set sh2 = Sheets(n).Next
                Exit For
            End If
        Next n

        Set dic = CreateObject("Scripting.Dictionary")

        '//前のシートの項目一覧をdicに入れる
        With sh1
            a = .Range("A1", .Cells(Rows.Count, "A").End(xlUp).Resize(, 4)).Value
            For i = 2 To UBound(a, 1)
                a(i, 4) = a(i, 2) - a(i, 3)
                If dic.exists(a(i, 1)) Then MsgBox "重複を無くしてから再度実行してください": Exit Sub
                dic.Add a(i, 1), a(i, 4)
            Next i
            .Range("A1").Resize(UBound(a, 1), 4).Value = a
        End With

        '//該当する項目に値を入れる
        With sh2
            a = .Range("A1", .Cells(Rows.Count, "A").End(xlUp).Resize(, 2)).Value
            For i = 2 To UBound(a, 1)
                If dic.exists(a(i, 1)) Then
                    If dic(a(i, 1)) = Chr(2) Then MsgBox "出力先に重複があります。重複を無くしてから再度実行してください": Exit Sub
                    a(i, 2) = dic(a(i, 1))
                    dic(a(i, 1)) = Chr(2)
                End If
            Next i
            .Range("A1").Resize(UBound(a, 1), 2).Value = a
        End With
        MsgBox "転記できました"
    End Sub

(稲葉) 2014/11/17(月) 11:50


 ありがとうございます!				
 申し訳ないですが、もう1点ご教授お願いできないでしょうか?				

 シート2のsh1残に、シート1のsh1残実行と同時に転記させることも				
 可能でしょうか?				

 シート1				
 項目	数量	出荷	sh1残	
 XYW56	 100	20	80	
 SDC236	 200	30	170	
 FFF11	 300	40	260	
 BNGJ598 400	50	350	
 KOLP021 500	60	540	

 シート2				
 項目	sh1残	出荷	sh2残	
 XYW56		50	-50	
 BNGJ598	30	-30	
 FFF11		20	-20	
 SDC236		60	-60	
 KOLP021	100	-100	

(けん) 2014/11/17(月) 13:19


 それをやっていますが・・・

 実行前
 シート1
	[A]	[B]	[C]	[D]
[1]	項目	  数量	 出荷	 sh1残
[2]	XYW56	100	20	
[3]	SDC236	200	30	
[4]	FFF11	300	40	
[5]	BNGJ598	400	50	
[6]	KOLP021	500	60	

 シート2
	[A]	[B]	[C]	[D]
[1]	項目	sh1残	出荷	sh3残
[2]	XYW56			
[3]	BNGJ598			
[4]	FFF11			
[5]	SDC236			
[6]	KOLP021			

 実行後
 シート1
	[A]	[B]	[C]	[D]
[1]	項目	  数量	 出荷	 sh1残
[2]	XYW56	100	20	80
[3]	SDC236	200	30	170
[4]	FFF11	300	40	260
[5]	BNGJ598	400	50	350
[6]	KOLP021	500	60	440

 シート2
	[A]	[B]	[C]	[D]
[1]	項目	sh1残	出荷	sh3残
[2]	XYW56	80		
[3]	BNGJ598	350		
[4]	FFF11	260		
[5]	SDC236	170		
[6]	KOLP021	440		

(稲葉) 2014/11/17(月) 13:47


こちらではうまくいきませんでした。。。
シート2のsh1残にはなにも転記されずです。。。

 実行前
 シート1
 項目	数量	出荷	sh1残
 XYW56	100	20	
 SDC236	200	30	
 FFF11	300	40	
 BNGJ598 400	50	
 KOLP021 500	60	

 シート2
 項目	sh1残	出荷	sh3残
 XYW56			
 BNGJ598			
 FFF11			
 SDC236			
 KOLP021	

この状態で実行すると、「重複をなくしてから〜」のメッセージがでました。

そこで、sh1にsh1残が入力されていないのが原因と考え、入力後実行しました。

 実行前
 シート1
 項目	数量	出荷	sh1残
 XYW56	100	20	80
 SDC236	200	30	170
 FFF11	300	40	260
 BNGJ598 400	50	350
 KOLP021 500	60	550

 シート2
 項目	sh1残	出荷	sh3残
 XYW56			
 BNGJ598			
 FFF11			
 SDC236			
 KOLP021

 実行後
 シート1
 項目	数量	出荷	sh1残
 XYW56	100	20	80
 SDC236	200	30	170
 FFF11	300	40	260
 BNGJ598 400	50	350
 KOLP021 500	60	550

 シート2
 項目	sh1残	出荷	sh3残
 XYW56			0
 BNGJ598		0
 FFF11			0
 SDC236			0
 KOLP021		0

とこのようになりました。
なにが原因でしょうか。
標準モジュールにコピペして実行しているだけなのですが。。
(けん) 2014/11/17(月) 14:21


そちらでの実行結果はまさに理想のものです。
(けん) 2014/11/17(月) 14:23

 項目に重複はありませんか?
 若しくは、計算式が入力されていて、空白にされているなど。

 項目列の最終行からCtrl+↑キーを押して、どこで止まるか確認してください。

 VBAでやりたいならせめてデバッグのやり方くらい覚えてください。
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_200.html

(稲葉) 2014/11/17(月) 14:58


 >この状態で実行すると、「重複をなくしてから〜」のメッセージがでました。 
 ご呈示のデータには重複は無いので そのメッセージが出るのはおかしいと思います。

 新しいブックの新しいシートに、ご呈示の表を作成し
 もう一度やってみられてはどうでしょう?

 データになにか問題があると思います。

   たとえば、A列の項目を =IF(○○="","",○○)
   とか =○○&"" の様に表示させているとか。。。

 >そこで、sh1にsh1残が入力されていないのが原因と考え、入力後実行しました。 
 これはダメですよね。
 「左のシートから見て、D列が入力されていない最初のシート」
 をsh1にするのですから、残を入力してしまったらそれはsh1になりません。
  
(HANA) 2014/11/17(月) 15:09

重複を確認したところ、20行目に半角スペースが入っておりました。
また、HANAさまアドバイス通り、新しいエクセルにコードをコピペ。実行しました。

 実行後
 シート1
 項目	数量	出荷	sh1残
 XYW56	100	20	80
 SDC236	200	30	170
 FFF11	300	40	260
 BNGJ598 400	50	350
 KOLP021 500	60	550
 シート2
 項目	sh1残	出荷	sh3残
 XYW56			
 BNGJ598		
 FFF11			
 SDC236			
 KOLP021		

sh1残は実行後、自動で表示されましたが、シート2への転記はされませんでした。
デバッグのやり方覚えてからでなおします。
ありがとうございます。

(けん) 2014/11/17(月) 15:28


 まさかとは思いますが、シート2はシート1の右隣りではない
 とかありませんよね?
 シート名が可変なので、該当するシートの右隣のシートに書き出すコードになっています。

(稲葉) 2014/11/17(月) 15:30


 大丈夫です。
 シート2はシート1の右隣りです。

 原因かどうかわかりませんが、項目名をシート1と
 シート2同じ並びにしたところ、シート2のsh1残に転記されました。
 しかし、そちらの実行結果では並びはランダムでも上手くいっているようですね。
(けん) 2014/11/17(月) 15:55

 ありがとうございます。
 解決しました。自分のデータ取り扱いの不備です。
 項目名の先頭に空白が入っていたことが原因でした。
 今は無事ランダムな項目名でも表示されるようになりました。
 本当にお手数おかけいたしました。ありがとうございます。
 デバッグしながら、自分で書けるように勉強します。
(けん) 2014/11/17(月) 15:59

コメント返信:

[ 一覧(最新更新順) ]


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