エクセルＶＢＡについて教えてください。

質問

DSUMを使ってVBAで自動計算をさせたいのですがうまくいきません。
　・Sheetsデータにデータを置いていて、A1からU1610までデータが入ってます。
　・Sheets集計用は計算させるための（条件を入れる）シートで、A1からE列まで（選択する項目によって何行目になるかわかりません。）
　・mycountでE列のデータが入ってる行を出してます。
　・部屋タイプで1K～1LDKを選ぶとDSUMの式のタイプに1を入れたいのです。（1K～1LDKの場合はCells(1,3)
下記のように書いてみましたが上手くいきません。
どなたかご教授いただけると助かります。

mycount = "=COUNT(集計用!E2:E300)"
Sheets("集計用").Cells(5, 7).Value = Range("g10") = " =DSUM(cells(データ!,1),1610,21),cells(データ!1,タイプ),cells(集計用!),cells(mycount,5))"

'部屋タイプの選択
If Sheets("フォーム").Range("c30") = "1K～1LDK" then　
タイプ = 3
ElseIf Sheets("フォーム").Range("c30") = "2K～2LDK" Then
タイプ = 6
ElseIf Sheets("フォーム").Range("c30") = "3K～3LDK" Then　
タイプ = 9
ElseIf Sheets("フォーム").Range("c30") = "4K～4LDK" Then
タイプ = 12
Else
Sheets("フォーム").Range("c30") = "その他" Then
タイプ = 15
End If

回答No.5

e10go · ベストアンサー

こんばんわ。
回答に対する補足ありがとうございます。

>・If ... Thenですが、ここの質問内容を800字以内にしないとエラーになるので削りました。
・
・
>End If

この内容でしたら、これで良いと思います。
ただ一つ、

Else
MsgBox "部屋タイプを選択してください"
End If

上のコードは、

Else
MsgBox "部屋タイプを選択してください"
Exit Sub
End If

とした方が良いです。
「Exit Sub」を追加していますが、これは、マクロの実行をここで中止します。
理由は、「Else」処理の場合、「タイプ」変数にデータを設定していないので、このままDsum関数のコードを実行するとエラーになります。

>DSUM関数の部分のソースを↓のとおり実行したらエラーになります。

惜しいですね。もう少し複雑な例をあげれば、emi445さんの書き換えるコードが正解になっていたような気がします。
ワークシート関数で複数シートのセルを指定する場合は、シート(名)とCells()をセットで指定する必要があります。

>Sheets("集計用").Range(Cells(1, 1), Cells(mycount, 5)))
上の部分を、
Range(Sheets("集計用").Cells(1, 1), Sheets("集計用").Cells(mycount, 5)))
としなければなりません。
なお、下の指定でも良いです。
Sheets("集計用").Range("A1:E" & mycount))
これは、「mycount」が「100」の場合、
Sheets("集計用").Range("A1:E100"))
と同じです。

この他に注意事項として、
Dsum関数のコードで、2つの変数「タイプ」・「mycount」を使用していますが、Dsum関数のコードを実行する前に、この変数の値を確定させる必要があります。
(そうしないと、Dsum関数のコード実行時にエラーになる)
以上をまとめて、下に修正したマクロを載せます。

'－－－－－－－－マクロコード－－－－－－－－始まり
Sub test()
　　Dim タイプ As Long, mycount As Long
　　If Sheets("フォーム").Range("C30") = "1K～1LDK" Then
　　　　タイプ = 3
　　ElseIf Sheets("フォーム").Range("C30") = "2K～2LDK" Then
　　　　タイプ = 6
　　ElseIf Sheets("フォーム").Range("C30") = "3K～3LDK" Then
　　　　タイプ = 9
　　ElseIf Sheets("フォーム").Range("C30") = "4K～4LDK" Then
　　　　タイプ = 12
　　ElseIf Sheets("フォーム").Range("C30") = "その他" Then
　　　　Sheets("集計用").Range("B1") = "タイプ5"
　　　　Sheets("集計用").Range("B2") = 5
　　　　タイプ = 15
　　Else
　　　　MsgBox "部屋タイプを選択してください"
　　　　Exit Sub
　　End If
　　mycount = Application.WorksheetFunction.Count(Worksheets("集計用").Range("E2:E300"))
　　Sheets("集計用").Cells(5, 7) = Application.WorksheetFunction.DSum( _
　　　　Sheets("データ").Range("A1:U1610"), Sheets("データ").Cells(1, タイプ), _
　　　　Sheets("集計用").Range("A1:E" & mycount))
　　Range("G10") = Sheets("集計用").Cells(5, 7)
End Sub
'－－－－－－－－マクロコード－－－－－－－－終わり

また、わからない点や、うまくいかない部分がありましたら、私のわかる範囲でお答えします。

それから、Excelマクロを勉強されるなら、下のURLを参考にされると良いと思います。
「Excelでお仕事」
http://www.asahi-net.or.jp/~ef2o-inue/top01.html
「Smile!Excel」
http://www.happy2-island.com/excelsmile/index.html
「Excel VBA　モーグ即効テクニック集」
http://www.moug.net/tech/exvba/index.htm
「K窓 Excel技Excel Tips」
http://homepage2.nifty.com/kmado/kvba.htm
なお、私がExcelVBAを使いこなせる様になったのは、下の本を読んでからです。(おすすめです)
かんたんプログラミング Excel2003 VBA 基礎編
かんたんプログラミングExcel2003 VBA 応用編
かんたんプログラミング Excel2003 VBA コントロール・関数編
かんたんプログラミング Excel2002VBA 演習編

参考URL：

http://www.amazon.co.jp/exec/obidos/ASIN/4774119660/ish-22/503-9250461-7915948

お礼

おかげで、思ったとおりに動作させることができました。
ありがとうございました。

回答No.6

e10go

No.4,No.5のe10goです。

No.5で一番下に紹介した本ですが、私が読んだ本は全てExcel2000の本でした。
紹介した本も、内容はほぼ同じです。
どうも、すみませんでした。

回答No.4

e10go

こんばんわ。

emi445さんは、Excelのシート上での関数の使い方は理解されていると思いますが、
質問の内容を拝見すると、マクロでの関数の使い方をご存じないように見受けられます。
私も、マクロで初めて関数を使う時に、質問者さんと同じようなコードを書きましたので＾＾；

DSUM関数は、下のURLの例で、
http://homepage1.nifty.com/kenzo30/ex_kisotyu/ex_ks_tyukyuxb3_1.htm#データベース関数・書式と引数

Excel表では、計算式が

=DSUM(A2:D11,D2,A14:C15)

となりますが、マクロコードでは、

Range("E15") = Application.WorksheetFunction.DSum(Range("A2:D11"),Cells(2, 4), Range(Cells(14, 1), Cells(15, 3)))

となります。
Excel表で使う関数をワークシート関数といい、マクロで関数を呼び出したり、
セルを指定する方法は、Excel表で使うやり方とは異なります。

前置きはそれぐらいにして(長い＾＾；)、具体的にコードを示そうと思いましたが、
質問の意味がよく判らないので、私なりに整理しました。

>"=DSUM(cells(データ!,1),1610,21),cells(データ!1,タイプ),cells(集計用!),cells(mycount,5))"

DSUM関数の使い方は、DSUM(データベースの範囲,フィールド,Criteria)、です。
最も、emi445さんは、DSUM関数の使い方はご存知ですよね。

「データベースの範囲」は、「=DSUM(cells(データ!,1),1610,21),...)」とありますが、
「シート"データ"のセルA1～U1610」の事ですね。

「フィールド」は、「=DSUM(...,cells(データ!1,タイプ),...)」とあるので、
シート"データ"のセルA1～U1のどれかを「タイプ」で指定したいのでしょう。

「Criteria」は、「=DSUM(...,cells(集計用!),cells(mycount,5))」とありますが、
シート"集計用"の中のE列mycount行を指定するのですか？
取り合えずそういう事にしておきます。

これらをまとめると、下の元のコードは、

mycount = "=COUNT(集計用!E2:E300)"
Sheets("集計用").Cells(5, 7).Value = Range("g10") = " =DSUM(cells(データ!,1),1610,21),cells(データ!1,タイプ),cells(集計用!),cells(mycount,5))"

下のコードになります。

mycount = Application.WorksheetFunction.Count(Worksheets("集計用").Range("E2:E300"))
Sheets("集計用").Cells(5, 7) = Application.WorksheetFunction.DSum( _
　　Sheets("データ").Range("A1:U1610"), Sheets("データ").Cells(1, タイプ), _
　　Sheets("集計用").Cells(mycount, 5))
Range("G10") = Sheets("集計用").Cells(5, 7)

なお、
Sheets("集計用").Cells(5, 7)
は、
Sheets("集計用").Range("G5")
でも良いです。

後、これは、補足要求ですが、

1.
DSUM関数の「Criteria」は、E列mycount行で良いでしょうか？
私の解釈が間違っているような気がしますが。

2.
後半のコードで、

'部屋タイプの選択
If Sheets("フォーム").Range("c30") = "1K～1LDK" then　
タイプ = 3
・
・
End If

とありますが、
シート"フォーム"のセルC30に「1K～1LDK」とあれば、「タイプ = 3」になるのでしょうか。
それとも、「1K」または「1LDK」でも「タイプ = 3」になるのでしょうか。
それによって、マクロコードが変ります。

最後に、

'部屋タイプの選択
If ... Then
・
・
Else
Sheets("フォーム").Range("c30") = "その他" Then
タイプ = 15
End If

の中で、
Sheets("フォーム").Range("c30") = "その他" Then
の後ろの、「Then」は削除してください。
これがあると、エラーになります。
If ... Then
ElseIf ... Then
以外で「Then」は使用できません。

補足

解説ありがとうございます。
マクロで関数を使用するのは初めてで使い方がわかりませんでした。
・CriteriaはA1からE列mycount行です。
・sheetsフォームC30が1K～1DLKだったらタイプ=3になります。（フォームにリストボックスを作っていて、1K～1DLKを選択するとセルC30に1K～1DLKが入ります。）
・If ... Thenですが、ここの質問内容を800字以内にしないとエラーになるので削りました。

↓のソースが続きです。
ElseIf Sheets("フォーム").Range("c30") = "その他" Then
Sheets("集計用").Range("b1") = "タイプ5"
Sheets("集計用").Range("b2") = 5
タイプ = 15
Else
MsgBox "部屋タイプを選択してください"
End If

DSUM関数の部分のソースを↓のとおり実行したらエラーになります。
よろしくお願いします。

Sheets("集計用").Cells(5, 7) = Application.WorksheetFunction.DSum( _
Sheets("データ").Range("A1:U1610"), Sheets("データ").Cells(1, タイプ), _
Sheets("集計用").Range(Cells(1, 1), Cells(mycount, 5)))

回答No.3

papayuka

imogasiさん

> ＝が２つ左辺に出現しますが、ありえない。

ありえなくは無いですよ。
test1はTrue、test2はFalseが返ります。

Sub test1()
　Range("A2:A3").Value = "○"
　Range("A1").Value = Range("A2").Value = Range("A3").Value
End Sub

Sub test2()
　Range("A2").Value = "○"
　Range("A3").Value = "△"
　Range("A1").Value = Range("A2").Value = Range("A3").Value
End Sub

回答No.2

imogasi

質問の意味をはっきりさせてください。
>DSUMを使って
エクセル（ワークシート）関数のDSUMを使うのですか。
であれば
Application.Worksheetfunction。Dsumとしないといけないはずですが。
＞A1からE列まで（選択する項目によって何行目になるかわかりません。）の意味は？？
片一方はA1とセルを言ってながら、Eは列を言っているのは？？
E1のミスタイプ？？
＞mycountでE列のデータが入ってる行を出してます。
この意味は？？E列とはセルE1のこと。
＞DSUMの式のタイプに1を入れたいのです
Dsum関数の引数に「タイプ」という引数がないはず。Criteriaと言われる引数のこと？？
Dsumなんて関数は、VBAでは使わないでIF分で判別すればすっきりするように思うが。
＞mycount = "=COUNT(集計用!E2:E300)"
は文字列をmycountに入れて、cells(集計用!),cells(mycount,5))"
で使っているが無茶な記法では？
コードから回答者に逆推測させ、添削させるより、現状のシートの状態の実例を上げて、やりたいことをはっきり書く方がためになると思います。
全体に記法がめちゃなような印象なんですが。
Sheets("集計用").Cells(5, 7).Value = Range("g10") = ・・
＝が２つ左辺に出現しますが、ありえない。
DSUM(cells(データ!,1),1610,21),cells(データ!1,タイプ),cells(集計用!),cells(mycount,5))"
セルにVBAでエクセルの関数式を書いたら、手作業で入力したのと同じになるという、誤解してませんか。
Sub test01()
Cells(2, 3).Formula = "=SUM(A1:A3)"
End Sub
を参考に。

回答No.1

papayuka

mycount = "=COUNT(集計用!E2:E300)" は 文字列を mycount に代入しているだけです。
その次の行は Range("g10") の値が " =DSUM～" と等しいか否かをセルG5に代入しているだけです。
また式をセルに入れたいなら文字列で直接Cellsとしても意味がありません。
タイプと言う変数を使いたいなら、値をセットした後に使用しないと意味がありません。

mycount部分だけですがサンプルです。

Sub Test()
　mycount = "=COUNT(集計用!E2:E300)"
　　MsgBox mycount
　mycount = WorksheetFunction.Count(Worksheets("集計用").Range("E2:E300"))
　　MsgBox mycount
End Sub