商品の品番の確認作業にエクセルを使っています。
作業の効率化にエクセルVBAを使いたいのですが、うまくいきません。
具体的な使用目的は以下の通りです。
納品された商品には製造メーカーのバーコードがついています。それをエクセルに読み込んで予定通りの商品であるか確認作業を行います。
まず、エクセルシートのA列にメーカーコードを入力しておきます、B列のセルをアクティブにした状態でメーカーのバーコードを読み取ります。するとセルにメーカーコードが自動入力されます。この文字列がA列の文字列と一致した場合にはC列のセルにOKの文字が出るように設定しています。コードが一致しない場合はNGが出ます。
現在はOKかNGかを目で確認しているのですが、商品数が多いので、NGの場合にBeep音が出るようになっていれば作業が早くなります。
過去の質問例を調べて、VBAのAMI関数を使うとBeep音を鳴らせることはわかりました。たとえば、セルC1にNGと表示された場合にBeep音を鳴らす、というところまではできたのですが、列CのいずれかのセルにNGと表示された場合にBeep音を鳴らす、という設定ができません。
わかる方アドバイスお願いします。
ちなみに、セルC1にNGと表示された場合にBeep音を鳴らすには以下のように設定しました。
標準モジュール
Public Declare Function Beep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
シートのモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C1") = "NG" Then
Call Beep(2000, 500)
End If
End Sub
No.4ベストアンサー
- 回答日時:
こんにちは。
>VBAのAMI関数を使うとBeep音を鳴らせる
Win32API関数です。
別に、Win32API関数を使わなくても、単に、組み込み関数のBeep だけでも良いと思います。
本来は、NGと出てくるということは、データがインポートされるのですから、それを監視したほうが早いのです。Excelには、専用コマンドもあります。しかし、それは、古いメソッドなので、監視できるかどうかは分かりません。私の知っている範囲では、半分ぐらいは成功していません。
> Worksheet_Change(ByVal Target As Range)
今のところ、バーコードの出力で、Change イベントが、発生するか分かりません。Microsoft の古いサポートでは、この方法が書いてありましたが、DDEやOLEでは、反応しなかったような気がします。Change イベントで反応するのでしたら、以下のように、の入力列の Target.Value を判定に使えばよいです。
私のコンセプトは、NGは一つとは限らない、という前提で作られています。NGという表示を置いたまま、次に移って検査しても、可能なようにできています。
もし、ひとつだけを探すなら、Match関数や、CountIf 関数自体を、マクロの中に使用しても良いと思います。
注意:以下の二つのマクロは、二者択一ですから、必ず、片方だけにしてください。
'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If StrConv(Target.Value, vbUpperCase) Like "NG*" Then
Call Beep(2000, 500)
End If
End Sub
'=========================================
上記のイベント・ドリブン型のマクロでは、反応がない場合には、このようにします。
'-------------------------------------------
'標準モジュール
'Win32API関数の下に、
Public gCnt As Long 'と入れて、
'-------------------------------------------
#1さんの回答のように、該当するシートの E1 のセルに、=COUNTIF(C:C,"NG") と入れます。
そしてシートモジュールに以下のように貼りつけます。
'-------------------------------------------
Private Sub Worksheet_Calculate()
'セル"E1" に =COUNTIF(C:C,"NG") という数式が入っていること
Dim iCnt As Long
iCnt = Range("E1").Value
If iCnt > gCnt Then
Call Beep(2000, 500)
End If
gCnt = iCnt
End Sub
'----------------------------------------
なお、二番目の場合は、ブックを開いて、機器を使用した時に、最初の一回目に抜けが発生するかもしれません。
>バーコードの出力で、Change イベントが、発生するか分かりません
手元に今バーコードリーダーがないので、試験できませんが、今度確認してみます
イベント・ドリブン型のマクロをコピペして試してみたのですが、うまく動作しません。原因不明です。
二つ目のマクロはうまくいきました。
Wendy02さんが言うように、#1さんと#2さんの方法だと、NGを保留して次に進んだ場合、OKの場合でもBeep音がなってしまいますが、このマクロだと鳴らない点がすごく優れていると思います。
ここまでくると私には複雑すぎてネットで調べても文章の意味がが理解できないのですが、#2さんのやり方のようにカウントセルをシート上におかないように修正することはできるのでしょうか。
No.6
- 回答日時:
#5で、うっかり書き忘れましたが、
Microsoft 側が提示している (Excel 97時代)
>Sub Auto_Open()
> Worksheets("Sheet1").OnData = "SoundMessage"
の方法は、実際のデータが入ってこないと調べることが出来ません。DDEとOLEのデータが入る時に、イベントが発生すると言っていますが、私がAccess からDDEでエクスポートするマクロでは、イベントが発生していませんでした。
No.5
- 回答日時:
こんばんは。
>手元に今バーコードリーダーがないので、試験できませんが、今度確認してみます
できたら、お願いします。英語版掲示板でも、間違った内容があったりして正確なものがないのです。それに、機種によって違うような気がします。昔は、種類がなくて、DDEのみだったので、問題はなかったのです。
以下のコードは、バーコードリーダーで、"NG"が出て反応するかどうかの問題があります。
Change イベントというのは、リターンコードなどで反応するのではないかと思うからです。そうすると、それを出力しないものは、Change イベントでは反応しません。
'-------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
>イベント・ドリブン型のマクロをコピペして試してみたのですが、うまく動作しません。原因不明です。
イベントが二重になって登録されているとか、特に、問題は発生しないように思います。
これらは、除外条件です。
If Target.Column <> 3 Then Exit Sub 'C列以外
If Target.Count > 1 Then Exit Sub '選択したセルが複数の場合
If Target.Value = "" Then Exit Sub 'セルが空の場合
チェックポイントとしては、
Private Sub Worksheet_Change(ByVal Target As Range)
この行の所にカーソルを置いて、F9 を押しますと、ブレークポイントと言って、行に色が付き、色が反転します。そこで、マクロが通ればマクロは止まり、その後に、F8を押して、ステップインで、その先に進んで行きます。そうして、マクロの反応や If分岐の状態が確認できます。
もし、まったく動かない場合は、Application.EnableEvents = False とかで抜け出たりしているは働きませんので、
Application.EnableEvents = True で、イベント可動状態にすると元に戻り動くようになります。
'-------------------------------------------
こちらのものは、バーコードリーダーで、仮に、リーターンコードが発生していなくても、反応します。DDE インポートでは、確認しています。
Private Sub Worksheet_Calculate()
>#2さんのやり方のようにカウントセルをシート上におかないように修正することはできるのでしょうか。
できるのですが、100分の1秒とかの範囲で、ワークシートの変化を監視する特殊なマクロが必要になります。人為的に無限ループを発生されるものです。今まで、このイベント・スタイルでは作ったことがありません。言い換えると、新しいイベントを作ることになるのですが、単なるワザだけで、Excelのアプリケーションとして負担が大きすぎるような気がしているのです。
'-------------------------------------------
これは、Microsoft 側が提示している方法です。しかし、これが上手く言ったという話は、今までで1件しかありません。
'標準モジュール
Sub Auto_Open()
'シートを設定する(起動時には自動的に設定)
Worksheets("Sheet1").OnData = "SoundMessage"
End Sub
'
Sub SoundMessage()
If ActiveCell.Column = 3 Then 'C列に限定
If StrConv(ActiveCell.Value, vbUpperCase) Like "NG*" Then
Call Beep(2000, 500)
End If
End If
End Sub
返信が遅くなって申し訳ありませんでした。
自宅で手入力による作動を確認し、職場に持ち込んで試そうとしたのですが、引数の数が不正でとまってしまいました。オフィスのバージョンが違うからでしょうか。理由はわかりません。
今回、色々な方にご回答いただいたのですが、私の力不足で仕事に生かすことはできませんでした。
VBAはなかなか難しいですね。
いつか基本から学んでみたいと思います。
皆様ありがとうございました。
No.3
- 回答日時:
VBAを今後も使うのであればと思い、下記の方法も紹介します。
Private Sub Worksheet_Change(ByVal Target As Range)
For i = 1 To Range("C1").End(xlDown).Row
If Cells(i, 3).Value = "NG" Then
Call Beep(2000, 500)
Exit For
End If
Next i
End Sub
ようは、C列の1行目から最終行までを一つずつチェックして、
「NG」の行が最初に見つかった時点で音を鳴らして、チェック終了…ということです。
ただこれは、「NG」が無いときはムダに最終行までチェックします。
つまりExcel2003までなら65536行、
Excel2007なら104万8576行を1行ずつ確認する…という時間がかかってしまいます。
「NG」があればループしている途中でチェックを切り上げてくれるので時間がかからないのですが。
なので、他のご回答のように列単位でNGがあるのかどうかを確認するのが今回は良いと思います。
No.2
- 回答日時:
If Range("C1") = "NG" Then
を次のようにしてはどうでしょう。
If Target.Column = 3 And WorksheetFunction.CountIf(Range("C:C"), "NG") > 0 Then
ありがとうございます。
ワークシートにカウント用のセルをつくる必要がないので他の人が誤ってシートを変更してしまう危険がない点が良いです。
初めに、手入力でテストしたところ音が鳴りませんでした。
バーコードが読み込まれるのはB列で(自宅でテストしているときはB列に手入力します)C列には、=IF(A1=B1,"OK","NG")という関数が入っているので、
Target. Column=3をTarget. Column=2に変更したところうまくいくようになりました。
No.1
- 回答日時:
"どこかのセル"でC列のNGの有無をカウントし
=COUNTIF(C1:C200,"NG")
ご提示の部分を少し変えてみたらいかがでしょう。
If Range("どこかのセル").value => 1 Then
Call Beep(2000, 500)
大変わかりやすい回答をありがとうございます。
自宅にバーコードリーダーがないので最終確認はできませんが、手入力だと目的の動作が起きることを確認できました。
#4さんの回答を参考にC列のNGの有無は=COUNTIF(C:C,"NG")でカウントすることにしました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
ロボットの住む世界で流行ってる罰ゲームとは?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセル、条件を満たしたら音を鳴らす方法教えて下さい
Excel(エクセル)
-
エクセルで条件を満たした場合に音を鳴らしたいです。
Visual Basic(VBA)
-
エクセルで音を鳴らしたい
Excel(エクセル)
-
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル 上下で列幅を変えるには
-
特定の文字がある行以外を削除...
-
エクセルで特定の文字列が入っ...
-
[EXCEL]ボタン押す→時刻が表に...
-
Excel グラフのプロットからデ...
-
excelのデータで色つき行の抽出...
-
Excel ウインドウ枠の固定をす...
-
エクセルのセルに指定画像(.jpg...
-
エクセル マクロ オートフィ...
-
【Excel関数】UNIQUE関数で"0"...
-
エクセルVBA 最終行を選んで並...
-
エクセル2016で時間を入力して...
-
VBAで色の付いているセルの行削除
-
電話番号の入力方式が違うデー...
-
A1に入力された文字列と同じ文...
-
サイズの違うセル 並べ変え
-
Excel VBA 空白行があるセル範...
-
excel セルの結合を条件付で自...
-
エクセルで昨日までの日付デー...
-
エクセルVBA:リストに登録した...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで特定の文字列が入っ...
-
【Excel関数】UNIQUE関数で"0"...
-
特定の文字がある行以外を削除...
-
エクセル マクロ オートフィ...
-
[EXCEL]ボタン押す→時刻が表に...
-
AのセルとB行を比較して、一致...
-
VBAで色の付いているセルの行削除
-
Excel グラフのプロットからデ...
-
エクセル マクロで数値が変っ...
-
excel 小さすぎて見えないセル...
-
エクセル 上下で列幅を変えるには
-
罫線の斜線を自動で引くマクロ
-
結合されたセルをプルダウンの...
-
セルの色によって条件文をつけ...
-
エクセルのマクロで意図しない...
-
エクセルVBA 最終行を選んで並...
-
excelのデータで色つき行の抽出...
-
Excelのフィルター後の一番上の...
-
エクセル 時間の表示形式AM/PM...
-
Excelでカタカナ・ひらがな・英...
おすすめ情報