初心者オサムシがExcelのマクロと戦う戦闘ブログである
by オサムシ
カテゴリ:マクロ 応用( 1 )
Excelシート関数+マクロ/マクロ応用

VBA : For Renge If Cells フォームコントロール
シート関数 : COUNTIF LENB



今回は実践的にいきます

まずはシチュエーションを想定しましょう

通販をやっていて、入金を管理する場面

購入者には注文時に管理番号を送って、入金する時にその管理番号をコメントで送ってもらうと仮定する
(できない銀行もあるけどあくまで仮定なので。。)

Excelシートを見てみよう
d0285354_220395.jpg

G列には関数が入っていて、「入金リスト」に入金者の管理番号が入るとココに"1"のフラグが立つ仕掛け

以下が演算式
=COUNTIF(入金リスト!A:A,購入者リスト!B2)

選択しているセルが2行目なのでB2となっているが、セルが下に行くに連れてB3,B4,B5......となっていく

つまりB列の管理番号が、「入金リスト」シートのA列にいくつ存在するかカウントしている

この場合は同じ購入者が重複して振り込んでくる事はないので、カウントの結果が1より大きい事はないはず

「入金リスト」シートA列の管理番号は入金された順に入力していく。

すると、購入者リストの該当者レコードのG列に"1"が立つという訳

でも"1"が立つだけではいつ入金されたかわからない

そこでマクロの登場

G列に"1"が立っていたらそこのセルに現在日時を入力するというシステム

次回シートを開いた時は、すでに"1"ではなくて日時が入っているので、マクロを実行したときに更新されてしまう事も避けられる

そう

マクロを使わないでやろうとすると、シート関数ではブックを開く毎に日付が更新されてしまって意味がないのだ

「入金リスト」シートを見てみよう
d0285354_22124770.jpg

解説します

A列は管理番号を入力していく列。現在3件の入金があった事になってる。

B列はLENBという関数でA列のバイト数(文字数)をカウントしている
(LENは文字数、LENBはバイト数 日本語の場合は1文字で2バイト)

入力値が多かったり少なかったりした場合は入力ミス

C列は上記で使っているのと同じCOUNTIFでA列に重複が無いかカウントしている。

ここも"0"か"1"のはず。

入金の重複は考えづらいから、もし"1"より大きい数字になったら、入力ミスを疑うべし

それではマクロを書いてみよう
Sub 更新_Click()
Worksheets("管理表").Activate
//該当するシートをアクティブ化
For seru = 2 To 11
//Forでセルの2から11まで繰り返し
If Range("D" & seru) = 1 Then
//IfでD列の対象セルの値が"1"かを判断
Range("D" & seru) = Now
//IFが真ならNowで現在日時を入力
End If //Ifここまで
Next seru //繰り返しここまで
MsgBox "更新しました" //メッセージボックス表示
End Sub

d0285354_22253053.jpg

そして今回は実践。

実行するのにいちいちマクロを開くなんてカッコ悪いので、ボタンをつくってみよう

【 開発 ⇒ 挿入 ⇒ フォームコントロール 】のボタンマークをクリック
d0285354_22255164.jpg

プリフィックス/Excel マクロ:フォームのプリフィックスです参考にどうぞ


ボタンとマクロを紐づける
d0285354_22271290.jpg


ボタン1がシート上に表示されるので、名前を「更新」に変更して準備OK
d0285354_22275084.jpg


更新ボタンをクリックして実行!
d0285354_22315215.jpg

購入者リストのG列の"1"が立っていたところだけに現在日時が入力された

管理番号じゃなくて名前でも良いんじゃないかと思うが、サンプルにもあえて入れてみたけど同姓同名の人もいるので、やはり注文時に管理番号を割り付けて、入金の時に番号を入力してもらうのが確実

コチラで設定したフォーマットで管理できるのも便利だし。。

っとまぁこんな感じでどうでしょう。osamushi

<<2012/12/24 追記>>
上記マクロですが、下記の構文でもOKです

セルを指定するのに"Renge"ではなくて"Cells"を使い「(行番号,列番号)」で指定する方法です

Rengeの場合だと列指定が文字列なので"(ダブルクォーテーション)で囲まなければならないのと、&より,(カンマ)の方が打ちやすいので、この方が早いですね

Rengeとの違いは行番号を先に指定する事

Offset関数などの相対参照で指定するときも、「(行番号,列番号)」となるので注意
Sub 更新_Click()
Worksheets("購入者リスト").Activate
For seru = 2 To 11
If Cells(seru, 7) = 1 Then
Cells(seru, 7) = Now
End If
Next seru
MsgBox "更新しました"
End Sub


オサムシじゃーなる
[PR]
by 03musi | 2012-12-22 22:37 | マクロ 応用


カテゴリ
試験メモ/VBAエキスパート
マクロ 基礎
マクロ 応用
Excel 基礎
Excel 応用
Office
日記
もくじ

ファン

フォロー中のブログ
初心者のためのOffic...
七里ヶ浜だより Lett...
はじめてのMac-Sup...
初心者のためのOffic...

検索

タグ

その他のジャンル