[[20090403163037]] 『異なるシートをチェックしてコメント(入金日)を』(ようちゃん) >>BOT

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

 

『異なるシートをチェックしてコメント(入金日)を表示させるには』(ようちゃん)

 過去ログを調べたのですが「これ!」というものがヒットしませんでしたので
 教えてください。

 ひとつのファイルにデータが登録されています。
 売上データと入金データのふたつのシートがあります。

 売上データのシートには、売上が完了したデータが年ごとに足されていきます。
 入金データのシートには、入金が確認できたデータを月ごとに抽出し落としこんでいます。

 したいことは、毎月の入金データが加えられたシートと売上データのシートを比較し
 過去の売上データに対して入金が確認できたら入金日を表示させ、売り上げたものに
 対して入金があったかどうかを記録する、ということです。

 データ配列は次のようになっています。
 売上データ
 A支払方法 B売上返品区分 C売上返品日 D顧客a@E氏名 F売上伝票a@G受注伝票a@H商品額 I請求額 J入金日 K入金額
 入金データ
 A入金日 B顧客a@C氏名 D受注伝票a@E入金額

 ひとつの顧客bノ対して、複数の売上・入金データが存在することもあるので
 売上データのシートと入金データのシートの受注伝票にてマッチングします。

 売上データの中で、入金があった場合には入金データのシートに記録されている入金日と入金額を
 売上データシートのJ・Kに反映させたいのですが、今まではVLOOKUP関数を使って作業していました。
 この作業を後輩に引き継ぐにあたり、マクロ(VBA?)で処理できるようにしたいのですが
 あいにくその知識を持ち合わせていません。
 今までは自動記録で作れる簡単なものしか作ったことがない為、これを機会に勉強しようと
 マニュアル本を読み漁りましたが、お手上げ状態です。

 こんな未熟者ですが、お力をお貸しいただきたいと思います。
 よろしくお願いします。

 ちなみにEXCELのバージョンは2002です。


下記コードを入金データシートモジュールへコピペ

'入金データシートのE列に金額入力で売上データシートに入金日と金額を転記

 Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C
With Target
   If .Column <> 5 Then Exit Sub
   If .Row < 2 Then Exit Sub
   If .Count <> 1 Then Exit Sub
   If .Value = "" Then Exit Sub
End With

With Worksheets("売上データ").Columns(7)

   Set C = .Find(Target.Offset(, -1).Value)              '受注伝票bFind
   If Not C Is Nothing Then

   If MsgBox("売上シートの" & C.Address & "に該当受注番号があります 転記しますか", vbOKCancel) _
      = vbCancel Then Exit Sub

      C.Offset(, 3).Value = Target.Offset(, -4).Value    '入金日の転記
      C.Offset(, 4).Value = Target.Value                 '入金額の転記

   End If
End With
End Sub

'注意!受注伝票bノ重複があると上書きされます

(クリップ)


クリップ様

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

 別の作業をしておりまして、ご返信が遅くなり申し訳ございません。

 早速コードを入金データシートのモジュールにコピッてみたのですが…
 何も起こりません。

 教えていただいたコードは、どこか変更する必要があったのでしょうか?
 無知なもので、教えていただけるとありがたいです…

 お願い致します。 
 (ようちゃん)

 こんな塩梅でどうでしょうか。
 でも、引き継ぐのにマクロでっていうのは抵抗あります。(ROUGE)
 
Sub マイッチング()
Dim tbl, i As Long, rng As Range
With Sheets("入金データ")
    tbl = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Resize(, 5).Value
End With
With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(tbl, 1)
        If IsDate(tbl(i, 1)) Then
            .Add tbl(i, 4), Array(tbl(i, 1), tbl(i, 5))
        End If
    Next
    For Each rng In Sheets("売上データ").Range("G1", _
        Sheets("売上データ").Range("G" & Rows.Count).End(xlUp))
        If .Exists(rng.Value) Then
            rng.Offset(, 3).Resize(, 2).Value = _
                Application.Transpose(Application.Transpose(.Item(rng.Value)))
        End If
    Next
End With
Erase tbl
End Sub

クリップです

 >コードを入金データシートのモジュールにコピッてみたのですが…
 何も起こりません。

 提示のコードはシートチェンジイベントなので入金データシートの入金額(E列)に数値を入力しEnterキーで動きます


 >この作業を後輩に引き継ぐにあたり、マクロ(VBA?)で処理できるようにしたい
 は、
 >マニュアル本を読み漁りましたが、お手上げ状態
 この状態なら
 >今まではVLOOKUP関数を使って作業していました
 こっちの方を引継いだ方がよいでしょう、
 後になって仕様変更が出てきて質問されても困りませんか?
 (dack)

クリップ様
 ありがとうございます。試してみます。

 ROUGE様
 こちらも試してみました。何とか希望通りに動きそうです。

 dack様
 おっしゃることもごもっとも…なのですが、上司からの指示もあり…。
 またVLOOKUPだと配列を変更しなければならず(それぞれのシートの配列を変えたくないので)
 マクロに挑戦することになりました。

 皆様、ありがとうございました。
 いただいた内容をもとに、勉強してみます。
 またなにかありましたら、宜しくお願い致しますm(__)m

 (ようちゃん)

 > またVLOOKUPだと配列を変更しなければならず...
 
データ配列を変更しなくても良い裏技もあります。
 
[[20070427203222]]『不思議なVLOOKUP』(代奈)
 
また、INDEX関数、MATCH関数を使えば、おそらく変更不要になると思います。
 
(ROUGE)

 ROUGE様、ありがとうございます。
 INDEX関数、MATCH関数を使う方法をログで調べてみました。何とか思い通り動きそうです。

 いまさらですが、以前教えていただいたコードのことで質問です。

 > With CreateObject("Scripting.Dictionary")
 データキーと組みになる項目を格納するオブジェクトと思いますが、変数の宣言をする必要はないのでしょうか?

 >  If .Exists(rng.Value) Then
        rng.Offset(, 3).Resize(, 2).Value = _
            Application.Transpose(Application.Transpose(.Item(rng.Value)))
 Transpose関数を使用しているという認識でいいのでしょうか?
 Excelでは、指定した行を1次元配列にするという関数?と記憶してますが、ここでのTransposeの使い方がわかりません。
 また、Offset(, 3).Resize(, 2).なんですが、Offset(, 3).で選択範囲を右側へ3列ずらして、そのあとに
 Resizeは対象のセル範囲のサイズを変更するという意味だから…??? う〜ん、わかりません(汗)

 今回はINDEX関数、MATCH関数を使う方法で切り抜けたいと思いますが、後学のために教えてください。
 これを機会に真剣にマクロを勉強しますので… お願いします。

 (ようちゃん)


 > > With CreateObject("Scripting.Dictionary")
 > データキーと組みになる項目を格納するオブジェクトと思いますが、変数の宣言をする必要はないのでしょうか?
 宣言は別にしてもしなくてもOKです。
 Dim dic As Object
 Set dic = CreateObject("Scripting.Dictionary")
 With dic
 〜
 End With
 としても同じです。

 参照設定でMicrosoft Scripting Runtimeにチェックを入れておけば、
 With CreateObject("Scripting.Dictionary")
 は
 With New Dictionary
 と書け、メソッドなどが自動的に表示されます。

 > ...ここでのTransposeの使い方がわかりません。
 1次元配列をセルに書き出すために利用しています。
 ワークシート関数のTRANSPOSE関数と同じですよ。
 (ROUGE)

 ROUGE様

 ありがとうございました。
 もともとの質問は、INDEX関数、MATCH関数で処理することにしまして作業が終わりました。
 そこで、ROUGE様のコードを使ってマクロを実際につくってみよう!と思いコピペしてみたのですが
 何も起こりません…
 マクロを実行しても反応がないので、該当データが存在しないのかと思い検索してみました。
 一致するデータは存在しますが、なぜ反応しないのでしょうか?
 (ようちゃん)


 なんででしょうね?
 Bookはこちらにないので、何とも原因は分かりません。
 (ROUGE)

 ROUGE様
 ごもっとも
 勉強のためにも自分で解決目指します。
 ありがとうございました(ようちゃん)

コメント返信:

[ 一覧(最新更新順) ]


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