エクセル マクロ Matchの使い方

質問

前回の質問で、大変親切に回答して頂き、大変感謝しております。
でも、私自身がこの質問サイトのルールを把握していなっかたので、まだ問題が解決していないのに、親切に回答してくださった方を、ベストアンサーに選んでしまいました。
でも、まだ問題が解決していないので、再度質問をさせて頂きます。

私のやりたい事は下記のような事です。

現金出納帳シートのA列（大科目）B列（小科目）の文字列の中の　大科目（たとえば会費・入会金）小科目（たとえば会費）
この二つの文字が、決算シートのA列（大科目）の範囲のB列（小科目）の同じ文字と一致したときに
現金出納帳シートのB列（小科目）の一致した文字の行の4列目の数値を決算シートのB列（小科目）の同じ文字の行の右隣のセルに、出納帳に入力した数値を入れたいのです。

前回の回答を参考に、下記のようなマクロを試してみたのですがどうしてもうまくいくません。

Dim s
Dim h As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
h = Sheets("決算").Range("B3:B103").Value

For Each ws In Worksheets
s = ws.Index
Next ws

For K = 5 To s Step 1

Set KaMoku = Sheets(K).Range("D3:J103").Columns(1)

Next K
If IsError(Application.Match(h, KaMoku, 0)) = False Then
この用にするとここでエラーになります。
MyRNo = Application.WorksheetFunction.Match(h, KaMoku, 0) 実行時エラー方が一致しません
MyUNo = KaMoku.Cells(MyRNo).Offset(, 4)
N = N + MyUNo
Sheets("決算").Cells(3, 5).Value = N

End If

End Sub

もう一例

Dim s
Dim h As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
h = Sheets("決算").Range("B3:B103").Value

For Each ws In Worksheets
s = ws.Index
Next ws

For K = 5 To s Step 1

Set KaMoku = Sheets(K).Range("D3:J103").Columns(1)

Next K
If IsError(Application.Match(h, KaMoku, 0)) = False Then
では、.WorksheetFunctionがいけないのかと思って、この用にすると、アプリケーション定義と出ます。
MyRNo = Application.Match(h, KaMoku, 0)
MyUNo = KaMoku.Cells(MyRNo).Offset(, 4)　
実行時エラー　アプリケーション定義またはオブジェクトの定義エラー
N = N + MyUNo
Sheets("決算").Cells(3, 5).Value = N

End If

End Sub
素人の私には皆目見当がつきません、どうすれば良いのですか。
教えてください、どうかよろしくお願いします。

回答No.7

TAKA_R

何を説明するか、悩んだのですが・・・

他の方の言われるように、まず標準モジュールにかかれてはどうでしょう。
標準モジュールのつくりの方が簡潔で、初心者にはエラー探しもしやしやすいですよ。
ちなみに標準モジュールを開いて、「sub マクロ名（）」を書けば、end subが書き込まれるので、その間にコードをコピぺすればよいです。
動かし方はシートに絵とかボタンを貼って、マクロ登録したら動きます。
やっぱりイベントモジュール（今使っているところ）がいいと思ったら、コピペしてしまえばよいのです。

ｈはあってます。

次にｓですが。意外に重要な位置を占めてる割にスルーされていますね。
前の質問、今回の質問で、何を求めたいのか図りかねたのですが・・・。
For k = 5 To s から察するに現金出納帳の欄は５行目から始まって、ｓ行目まであるということでしょうか。（step 1　は標準になので書かなくても良いです）
現金出納帳がこのシートにのみに書かれていると仮定して、最後の行数をｓに入れたいとしたら。
s=activesheets.usedrange.rows.count

小科目は別の大科目の小科目とダブっているということはないですか？
ないなら、いろんな意味で楽です。
ダブっていたら、別の関数を同時使用もしくはちょっと複雑なことをしないといけません。

面倒なので小科目にダブりがないとして。
kamokuはセルの値（.valueで表されるもの）を指すと思われるので、setの必要はないです。
（でもマクロの頭の方で、宣言してあげましょう、as string です)

それからｋですが。
ここが一番よく分からなかったのですが、現金出納帳のシートがいっぱいあるんでしょうか。
そうでないなら、無視して。
sub 転記()
dim h
dim s as long, k as long,v as long,a as long,b as long
dim kamoku as string
dim ws1 as worksheet,ws2 as worksheet

set ws1=worksheets("決算")
set ws2=worksheet("現金出納帳")　'シート名は正しく入れなおして

s=ws2.usedrange.rows.count
h = ws1.Range("B3:B103")
ws1.activate　　'書き込むシートは選択されていないと書くことができないので
for k=5 to s
if ws2.range("b"&k)<>"" then 　 '現金出納の小科目が空欄だとエラーになるだろうから
kamoku=ws2.range("b"&k)
v=Application.Match(kamoku,h, 0)
a=ws1.range("c"&2+v) '&の後ろは3+v-1を計算した数、aは決算の科目の隣のセルの数字
b=ws2.range("ｆ"&k)　　 '現金出納の数字の入っている列がＦ（？）
if v<>0 then
a=a+b
range("c"&2+v).value=a
end if
end if
next
end sub

お礼

有り難うございます。
説明付きの丁寧なマクロを書いて頂き有り難うございます。
このマクロも参考にして勉強させていただきます。

補足

現金出納帳にこの用に書いてみました。
Dim h
Dim s As Long, k As Long, v As Long, a As Long, b As Long
Dim kamoku As String
Dim ws1 As Worksheet, ws2 As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
Set ws1 = Worksheets("決算")
Set ws2 = Worksheets("現金出納帳") 'シート名は正しく入れなおして

s = ws2.UsedRange.Rows.Count
h = ws1.Range("c3:c103")
'ws1.Activate '書き込むシートは選択されていないと書くことができないので
For k = 3 To s
If ws2.Range("e" & k) <> "" Then '現金出納の小科目が空欄だとエラーになるだろうから
kamoku = ws2.Range("e" & k)
v = Application.Match(kamoku, h, 0)
a = ws1.Range("e" & 2 + v) '&の後ろは3+v-1を計算した数、aは決算の科目の隣のセルの数字
b = ws2.Range("h" & k) '現金出納の数字の入っている列がＦ（？）
If v <> 0 Then
a = a + b
ws1.Range("e" & 2 + v).Value = a
End If
End If
Next
End Sub

現金出納帳
月　　日　　大科目　　　　　　小科目　　　　　摘要　　金額　　残高
　　　　　　　会費・入会金　　会費・入会金　　　　　　2000　　2000

決算
大科目　　　　　小科目　　　　　　　　合計金額
会費・入会金　　会費・入会金　　　　2000円（これはうまくいきました）

現金出納帳
月　　日　　大科目　　　　　　小科目　　　　　摘要　　金額　　残高
　　　　　　　会費・入会金　　会費・入会金　　　　　　2000　　2000
　　　　　　　会費・入会金　　会費　　　　　　　　　　　2000
この時点で型が一致しませんと出ます。

決算
大科目　　　　　小科目　　　　　　　　合計金額
会費・入会金　　会費・入会金　　　　2000円
　　　　　　　　　　会費　　
どのようにすればいいのですか、教えてください。

回答No.6

piyo_dansyaku

以前回答した者です。

以前の処理を見たときに気になってはいたのですが、Worksheet_SelectionChangeはセルを選択するたびに毎回実行されるイベントです。

それにより、毎回マクロが実行される負荷がかかります。
本当に必要なプログラムでなければ、Worksheet_Change（セルに値を入力した時に実行されるイベント）か標準モジュールでマクロを組むのがよい気がします。

この処理を活かすのであれば、Worksheet_Change。

また、Matchについても下で回答があるように、使い方のわからない処理よりもFor文で書いたほうがよい気がします。

あと確認ですが、ブレークポイントをはってデバッグ実行はされていますか？

お礼

そうですね、標準モジュールに書きます。
Worksheet_Change。のことも有り難うございます。

回答No.5

Rilakkuma_san

もう一例で提示されているものだと
MyRNoに値が入っていないように見えました。
もしほんとに値が入っていなければ
入っていない値をCellsに指定してはエラーとなります。

Matchとかそうゆうものを使用せずとも
Forでループさせるという選択肢があるなら

==============================================
Private pRow As Long
Private pCol As Long

Private Sub Worksheet_Activate()
　　pRow = ActiveCell.Row
　　pCol = ActiveCell.Column
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　Dim i As Integer
　　a_data = ActiveSheet.Cells(pRow, 1).Value
　　b_data = ActiveSheet.Cells(pRow, 2).Value
　　For i = 1 To 103
　　　　If Sheets("決済").Cells(pRow, 1).Value = a_data And _
　　　　　Sheets("決済").Cells(pRow, 2).Value = b_data Then
　　　　　　Sheets("決済").Cells(pRow, 3).Value = ActiveSheet.Cells(pRow, 4).Value
　　　　End If
　　Next
　　pRow = Target.Row
　　pCol = Target.Column
End Sub
===============================================

ちょっと不格好かもしれませんがこんな感じにすると
セルが移動した時に移動前の値を決済シートに反映することができると思います。
シート構成なんかはよくわからないので適宜変更してください。

提示したものだとどこの列だろうと反映させてしまうので
列判定は必要かもしれません。

お礼

おっしゃる通りだと思います。
全然わからない関数をつかうよりも、Forでループさせた方が良いですね
と言っても素人なので、他の事も分からないんですが。
親切にマクロまで書いて頂いて、とっても感謝しています。
書いて頂いたマクロを参考に、出納帳のマクロを書いてみます。
出納帳も標準モジュールに書く用にします。
有り難うございます。

補足

私なりに努力して、この用にして、各月の出納帳にマクロを書いてみました。
Private pRow As Long
Private pCol As Long

Private Sub Worksheet_Activate()
pRow = ActiveCell.Row
pCol = ActiveCell.Column
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
pRow = ActiveCell.Row
pCol = ActiveCell.Column
a_data = ActiveSheet.Cells(pRow, 4).Value
b_data = ActiveSheet.Cells(pRow, 5).Value
For i = 1 To 103
If Sheets("決算").Cells(pRow, 2).Value = a_data And _
Sheets("決算").Cells(pRow, 3).Value = b_data Then
Sheets("決算").Cells(pRow, 5).Value = ActiveSheet.Cells(pRow, 8).Value
End If
Next i
pRow = Target.Row
pCol = Target.Column
End Sub
最初の一回はうまく決算シートに転記できるのですけど
二回目からは転記できません。
１回目
出納帳
月　日　大科目　　　　　　小科目　　　　摘要　　　　入金　　出金　　残高
　　　　　会費・入会金　　会費・入会金　　　　　　　2000　　　　　　　2000

決算
大科目　　　　　　小科目　　　　　　金額
会費・入会金　　会費・入会金　　2000円

２回目
出納帳
月　日　大科目　　　　　　小科目　　　　摘要　　　　入金　　出金　　残高
　　　　　会費・入会金　　会費・入会金　　　　　　　2000　　　　　　　2000
　　　　　会費・入会金　　会費・入会金　　　　　　　2000　　　　　　　4000　　　

決算
大科目　　　　　　小科目　　　　　　金額
会費・入会金　　会費・入会金　　2000円（ここの金額が増えない）

又は
出納帳
月　日　大科目　　　　　　小科目　　　　摘要　　　　入金　　出金　　残高
　　　　　会費・入会金　　会費・入会金　　　　　　　2000　　　　　　　2000
　　　　　会費・入会金　　会費・入会金　　　　　　　2000　　　　　　　4000　
　　　　　会費・入会金　　会費　　　　　　　　　　　　2000　　　　　　　6000　　

決算
大科目　　　　　　小科目　　　　　　金額
会費・入会金　　会費・入会金　　2000円
会費・入会金　　会費　　　　　　　2000円（ここに数値がはいらない）
どうすれば良いのですか、教えてください。

回答No.4

Rilakkuma_san

ちなみにですが、
この処理が動作するタイミングは
出納帳に数値が入力された時ですかね？

お礼

色々細かい所まで考えて下さって、とっても感謝しています。
ありがとうございます。

補足

出納帳は、４月～３月までを最初の画面で、その月の数値を入力すれば
たとえば4と入力すれば、４月分と言うシートができるようにしてありますので
この処理が動作するタイミングは、決算シートをマウスでクリックした時です。
本当は、各月分出納帳に数値を入力した時点でこの処理が動作すれば良いと思います。

回答No.3

Rilakkuma_san

ANo1です。

offsetは「0」の省略なんですね。。。
失礼しました。。

お礼

いろいろ考えてくれてありがとうございます。

補足

本当に素人なので、そこのところがあんまりわからないのです。

回答No.2

Rilakkuma_san

ANo1です。

>MyUNo = KaMoku.Cells(MyRNo).Offset(, 4)
の「Offset(, 4)」はなんか記述ミスですか？

お礼

回答ありがとうございます。
Offset(,4)は違うんですか、VBAの本にはそういう用に書いてあったのでそうしたのですが、

回答No.1

Rilakkuma_san

こんにちわ。

VBAはあまり使わないので自信がないですが。。。

Match関数の使い方が違う気がします。

Match（検査値, 検査範囲, 仕様）
という書式なので
「h」というVariant配列では検査値との型が不一致で
エラーとなっているんじゃないでしょうか？

お礼

早速回答して頂いてありがとうございます。

範囲指定して文字列を検索するときは
Dim　h　As Variantで良いみたいなのですが。
If IsError(Application.Match(h, KaMoku, 0)) = False Then
やりたい事
ここまではうまくいったのですが、
MyRNo = Application.WorksheetFunction.Match(h, KaMoku, 0) 実行時エラー方が一致しません
MyUNo = KaMoku.Cells(MyRNo).Offset(, 4)
N = N + MyUNo
Sheets("決算").Cells(3, 5).Value = N
このへんの構文がどうすれば良いのかさっぱりわかりません。

また気づいた事があったら教えてください。
ありがとうございます。