[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『異なるシートをチェックしてコメント(入金日)を表示させるには』(ようちゃん)
過去ログを調べたのですが「これ!」というものがヒットしませんでしたので 教えてください。
ひとつのファイルにデータが登録されています。 売上データと入金データのふたつのシートがあります。
売上データのシートには、売上が完了したデータが年ごとに足されていきます。 入金データのシートには、入金が確認できたデータを月ごとに抽出し落としこんでいます。
したいことは、毎月の入金データが加えられたシートと売上データのシートを比較し 過去の売上データに対して入金が確認できたら入金日を表示させ、売り上げたものに 対して入金があったかどうかを記録する、ということです。
データ配列は次のようになっています。 売上データ 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.