Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


3357 / 13644 ツリー ←次へ | 前へ→

【62737】データベースに蓄積していきたい ema 09/8/26(水) 19:47 質問[未読]
【62739】Re:データベースに蓄積していきたい ponpon 09/8/26(水) 21:18 発言[未読]
【62741】Re:データベースに蓄積していきたい ema 09/8/26(水) 22:04 回答[未読]
【62740】Re:データベースに蓄積していきたい kanabun 09/8/26(水) 21:32 発言[未読]
【62742】Re:データベースに蓄積していきたい ema 09/8/26(水) 22:07 回答[未読]
【62743】Re:データベースに蓄積していきたい kanabun 09/8/26(水) 22:36 発言[未読]
【62744】Re:データベースに蓄積していきたい kanabun 09/8/26(水) 23:44 発言[未読]
【62746】Re:データベースに蓄積していきたい ema 09/8/27(木) 5:38 お礼[未読]

【62737】データベースに蓄積していきたい
質問  ema  - 09/8/26(水) 19:47 -

引用なし
パスワード
   エクセルマクロ初心者です。宜しくお願いします。
Sheet4に「月」「顧客コード」「金額」を入力するとSheet2にランダムに並んでいる顧客コードに対応する月のところに金額を反映させるマクロを作成したいのです。
Sheet4(入力画面)
   A    B
1 月    1月
2 CD    101
3 顧客名 しめい ←CDを入力するとvlookupで検索します
4 金額  10,000

Sheet2(データベース)
   A    B    C    D・・・・・・N
1 CD   顧客名  1月   2月・・・・・12月
2 101   しめい  10,000

データベースは顧客CD順に並んでおりません。
また顧客の数は決まっておらず、増えていきます。
分かりづらい説明で申し訳ございませんが、アドバイスお願いします。

【62739】Re:データベースに蓄積していきたい
発言  ponpon  - 09/8/26(水) 21:18 -

引用なし
パスワード
   ▼ema さん:

>Sheet4(入力画面)
>   A    B
>1 月    1月
>2 CD    101
>3 顧客名 しめい ←CDを入力するとvlookupで検索します
>4 金額  10,000
>
>Sheet2(データベース)
>   A    B    C    D・・・・・・N
>1 CD   顧客名  1月   2月・・・・・12月
>2 101   しめい  10,000
>

CDにダブりはなく、ユニークなものとします。

Sub test()
  Dim FR As Variant
  Dim ws2 As Worksheet, ws4 As Worksheet
  Dim myCol As Long
  
  Set ws2 = Sheets("Sheet2")
  Set ws4 = Sheets("Sheet4")
  myCol = Replace(ws4.Range("B1").Value, "月", "") + 2
  FR = Application.Match(ws4.Range("B2").Value, ws1.Range(ws1.Cells(1, "A"), ws1.Cells(Rows.Count, 1).End(xlUp)), 0)
  If Not IsError(FR) Then
    ws1.Cells(FR, myCol).Value = ws4.Range("B4").Value
  Else
    MsgBox "そのCDはありません"
  End If
End Sub

【62740】Re:データベースに蓄積していきたい
発言  kanabun  - 09/8/26(水) 21:32 -

引用なし
パスワード
   ▼ema さん:

すでに ponponさんから回答がありますが、
Changeイベントを使うのでしょうか?

Sheet4(入力シート)の Worksheet_Changeイベントプロシージャに
記述してみてください。

'--------------------------------------------------------- Sheet4
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myRange As Range
  Dim c As Range
  Set myRange = [B1,B2,B4]
  Set c = Intersect(Target, myRange)
  If c Is Nothing Then Exit Sub
  If WorksheetFunction.CountA(myRange) < 3 Then Exit Sub
   
  Dim mon As Long
  Dim m
  Dim RefRange As Range
  mon = Val([B1].Value)
  Select Case mon
   Case 1 To 12
     With Worksheets("Sheet2")
       Set RefRange = .Range("A2", _
            .Cells(.Rows.Count, 1).End(xlUp))
     End With
     m = Application.Match([B2].Value2, RefRange, 0)
     If IsNumeric(m) Then
       RefRange(m, 2 + mon).Value = [B4].Value
     End If
  End Select
End Sub

【62741】Re:データベースに蓄積していきたい
回答  ema  - 09/8/26(水) 22:04 -

引用なし
パスワード
   ▼ponpon さん:
ありがとうございます。
参考にさせていただいて、応用させてみたいと思います。

【62742】Re:データベースに蓄積していきたい
回答  ema  - 09/8/26(水) 22:07 -

引用なし
パスワード
   ▼kanabun さん:
どうもありがとうございます。
マクロに詳しくないので全く分からないんですが、勉強と思って一度試してみます。

【62743】Re:データベースに蓄積していきたい
発言  kanabun  - 09/8/26(水) 22:36 -

引用なし
パスワード
   ▼ema さん:

>マクロに詳しくないので全く分からないんですが、勉強と思って一度試してみます。

Sheetのイベントを利用した処理が書けるようになると、
何かと便利ですよ♪

↓こちらの説明が分かりやすいかと思います。

Excel(エクセル)VBA入門:ワークシートのイベント
 ht tp://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_event.html

【62744】Re:データベースに蓄積していきたい
発言  kanabun  - 09/8/26(水) 23:44 -

引用なし
パスワード
   ▼ema さん:

Sheet4(入力シート)ですが、ぼくはテストのために
入力補助を設定しておきました。

[B1]セルには[データ]-[入力規則]で 1,2,3,4,...10,11,12
  のリストをセットしておきました。
[B2]セルにも、Sheet2のA列(CD)リストから選べるよう、範囲に
  「顧客コード」と名前定義をしておいて、この範囲を入力規則の
  リスト範囲にセットしておきました。

また、
上のコードですが、何をしているか、以下に、少しコメント付けてみました。

> '--------------------------------------------------------- Sheet4
 「このModule内で使用する変数は必ず宣言してから使う」ことを宣言
> Option Explicit
>
 このワークシートのセル範囲に変更があったとき
> Private Sub Worksheet_Change(ByVal Target As Range)
   使用する変数を宣言します
   まず注目するセル範囲[B1,B2,B4]の3つのセル範囲用変数
>   Dim myRange As Range
   もうひとつRangeオブジェクト変数(使用法はすぐあと)
>   Dim c As Range

   注目するセル範囲[B1,B2,B4]の3つのセル範囲をmyRangeに
   セットします。
>   Set myRange = [B1,B2,B4]
   つぎに、変化のあったセルが[B1,B2,B4]のセルかを調べます
   Intersectメソッドは重なるセル範囲を返します
>   Set c = Intersect(Target, myRange)
   変化のあったセルTargetが[B1,B2,B4]のどれかのセルと
   重なっていれば 変数c にはセルが歳入されます。
   ↓逆に myRange[B1,B2,B4] のどのセルとも一致しないときは
   c は Nothing(セルなし)となりますので、すぐ処理を抜けます
>   If c Is Nothing Then Exit Sub

   また、myRange(すなわち[B1,B2,B4])の3つとも値が入力
   されていなければ、以下の処理は無駄なので しません。
>   If WorksheetFunction.CountA(myRange) < 3 Then Exit Sub
 
 ここまで来たときは、変化のあったセルは[B1,B2,B4]のどれかであり、
  かつ[B1,B2,B4]のどのセルにも値が入力されています。

>   Dim mon As Long
>   Dim m
   [Sheet2]のA列(顧客コード)データ範囲を参照するための変数
>   Dim RefRange As Range

   [B1]セルを見て、何月分の値かを取得します
>   mon = Val([B1].Value)
   [B1]セルの数値が 1〜12であれば有効な月数ですので、
    このときだけ処理を続けます。
>   Select Case mon
>    Case 1 To 12

      [Sheet2]のA列(顧客コード)データ範囲を変数にセット
>      With Worksheets("Sheet2")
>        Set RefRange = .Range("A2", _
>             .Cells(.Rows.Count, 1).End(xlUp))
>      End With
      [B2]セルの値が RefRangeの何番目(何行目)にあるか
      調べます
      m = Application.Match([B2].Value2, RefRange, 0)
      MATCHワークシート関数は検索して見つかった時
      検索範囲の上から何番目かを数値で返します
      (見つからなかったときはエラー値が返ります)
       数値が返ってきたときだけ [B4]セルの値を
>      If IsNumeric(m) Then
        Sheet2のRefRange(A列)のm行目の (2+mon)列目の
        セルに転記します
>        RefRange(m, 2 + mon).Value = [B4].Value
>      End If
>   End Select
> End Sub

【62746】Re:データベースに蓄積していきたい
お礼  ema  - 09/8/27(木) 5:38 -

引用なし
パスワード
   ▼kanabun さん:
細かい説明本当にありがとうございます。
一つひとつに説明をつけていただいていると、非常に参考になります。
自分で作っていくことができるようになるための貴重な勉強材料になります。

ありがとうございます。

3357 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free