Excel VBA 別シート値の引き算

仮シートにはシートの名前が入っています。そのシートが2シートあり、引き算をやりたいのですが、うまく行かずValue1のところで、エラーになってしまいます。別々のシートの計算方法でいい方法はありますか？

cnt = WorksheetFunction.Count(ActiveWorkbook.Sheets("仮シート1").Range("D1:D" & Setting1)) '

For Calc = 2 To cnt + 1
Value1 = ActiveWorkbook.Sheets("仮シート２").Cells(Calc, 11)
Value2 = ActiveWorkbook.Sheets("仮シート1").Cells(Calc, 10)
ActiveWorkbook.Sheets("仮シート1").Cells(Calc, 16) = Value1 - Value2
Next Calc

質問者からの補足コメント

• シート名は仮なのでなんでもいいです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2017/11/23 09:06

• 

  エラーはSubscript out of range error 9です。

    補足日時：2017/11/23 09:16

• 

  シート名と同じシートは存在するのですが、エラーになってしまいます。全角半角は関係ありませんでした。

    補足日時：2017/11/23 14:31

• 

  ごめんなさい。Setting1も気にされないでください。Setting1はその前にいろいろ条件がそろっていますからできます。Cntのところに関係し、MSGBOXでSetting1が機能していることを確認済みです。用はValue1とValue2のところで必ず、エラーになってしまいます。何か間違っているのですが、どこでしょうか？

    補足日時：2017/11/24 13:18

• 

  わかったことは下記のようにRangeを入力して、実行するとちゃんとCellsのところに数値が出ますがValue1-Value2のところは０になってしまいます。
  cnt = WorksheetFunction.Count(ActiveWorkbook.Sheets("仮シート1").Range("D1:D" & Setting1)) '
  For Calc = 2 To cnt + 1
  Value1 = ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11))
  Value2 = ActiveWorkbook.Sheets("仮シート1").Range(Cells(Calc, 10))
  ActiveWorkbook.Sheets("仮シート1").Range(Cells(Calc, 16)） = Value1 - Value2
  Next Calc

    補足日時：2017/11/25 13:32

No.9 回答者： WindFaller 回答日時： 2017/11/25 17:33

最初に失礼ですが、読んでいても通じていないような不安を感じています。

また、英語版のExcelをお使いになっているようですが、余計にちぐはぐしているようです。おそらくは、VBAの基礎的な部分の知識が不足しているように思われます。

1:　 Set wb = Workbooks("Temp.xlsm")　
'固有のブックでしたら、Temp.xlsmの代わりにに、ブック名を拡張子(extention)を組めて入れます。だいたい、ここでエラーなんてありえませんからね。
2:　 Set sh1 = wb.Worksheets("Sheet1")
'固有のシート名でしたら、その名前を入れます。(以下同様)
3:　 Set sh2 = wb.Worksheets("Sheet2")

>特にブック名を指定するマクロは作っておりません。
もしかしたら、大本の部分で、考え違いをしていませんでしょうか。
ActiveWorkbookを使うというのは、汎用性のあるマクロであるという意味です。

しかし、汎用性のあるマクロが作れるようになるのには、それなりに技術が向上しないと難しいのです。

ActiveWorkbook というのは、Workbooks の中のアクティブにしているブックという意味ですから、何でもよいということです。何でもよいのに、データは何でもよくないというのは矛盾しています。

ふつうは、自分のブックのシートの計算をするマクロを作るというのが初歩の初歩です。だから、ActiveWorkbookは使いません。

そのような　仮のシート名1 とか、仮のシート名2　とかでなく、変数で置けばよいのです。
2:　 Set sh1 = Worksheets("Sheet1")　'←実際の名称
3:　 Set sh2 = Worksheets("Sheet2")　 '←実際の名称

変に隠す必要もありません。デフォルトの名称Sheet1, Sheet2から、特定の名称に書けばよいのです。ブックを特定化して、下位のオブジェクトが決まります。下位基底が、上部基底を決めるようなことはありえません。

ご自身で何とか頑張ろうとするよりも、このレベルでしたら、人にまかせてしまってもよいと思います。ご自分のスタイルは変えないで、エラーが出ましたを繰り返されるのは、これ以上は無理なのです。

たぶん、こんな所だろうと思います。

'For General Module
'//
Sub TestMacro1()
Dim i As Long
Dim cnt As Long
Dim myVal1 As Double, myVal2 As Double
cnt = Worksheets("仮シート1").Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To cnt
　 myVal1 = Worksheets("仮シート2").Cells(i, 11).Value
　 myVal2 = Worksheets("仮シート1").Cells(i, 10).Value
Worksheets("仮シート1").Cells(i, 16).Value = myVal1 - myVal2
Next i
End Sub
'//

この回答へのお礼

いろいろとご説明どうもありがとうございました。上記もやっぱり、エラー9でした。もっと違う方法がないかネットで調べてみます。

お礼日時：2017/11/26 13:06

No.8 回答者： tatsu99 回答日時： 2017/11/24 13:37

>用はValue1とValue2のところで必ず、エラーになってしまいます。

Value1 = ActiveWorkbook.Sheets("仮シート２").Cells(Calc, 11)
Value2 = ActiveWorkbook.Sheets("仮シート1").Cells(Calc, 10)
上記の２行でエラーということでしょうか。

そうであれば、セルに正しい値が入ってないかもしれません。
msgbox(ActiveWorkbook.Sheets("仮シート２").Cells(Calc, 11).value)　・・・①
Value1 = ActiveWorkbook.Sheets("仮シート２").Cells(Calc, 11)
msgbox(ActiveWorkbook.Sheets("仮シート1").Cells(Calc, 10).value)　・・・②
Value2 = ActiveWorkbook.Sheets("仮シート1").Cells(Calc, 10)

のように①②を追加して、何が入っているか確認されてはどうでしょうか。（そのセルに関数式が埋め込まれてその関数でエラーになっていませんか）

この回答へのお礼

ヒントをどうもありがとうございます。
MSGBOX ActiveWorkbook.Sheets("仮シート2").Cells(Calc, 11)でもMSGBOX ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11))でも同じエラー9でした。しかし、後者はスクリプト実行中にちゃんとCellsのところにカーソルをあてると”Cells(Calc,11)=254.10"と数値を出してくれます。しかしMSGBOXはエラー9で出ませんでした。

お礼日時：2017/11/25 13:41

No.7 回答者： WindFaller 回答日時： 2017/11/23 23:28

質問と回答が、どうもちゃんと噛み合っていないように思います。

こちらがこういう所だといっても、そのままでは解決しないので、マクロはマクロで解決するのが一番早いです。

>シート名と同じシートは存在するのですが、エラーになってしまいます。全角半角は関係ありませんでした。

ActiveWorkbookでつなげていれば、エラーは回避できないと思います。

それと、もともとのマクロの問題が残っています。

以下、こんなエラー処理は本来のマクロには不要ですが、試しに書いてみました。
問題点は
・ActiveWorkbook から書いて、次のシート名が固有の名称というのはコードとしてはありえません。
　マクロを搭載しているという意味の、ThisWorkbook 以外は、省略することが多いです。
・WorksheetFunction のCount は、ループのカウント行を数えるのにはふさわしくありません。
・ループカウンターの変数には、i を使う方が見やすい。(interation)
・Value2 は、プロパティ名ですから、変数にはむやみに使ってはいけません。

'//
'//
Sub Test1()
　Dim wb As Workbook
　Dim sh1 As Worksheet
　Dim sh2 As Worksheet
　Dim SetRow As Long
　Dim i As Long
　On Error GoTo ErrHandler
'--------Setting Bigginning-----------
1:　 Set wb = Workbooks("Temp.xlsm")
2:　 Set sh1 = wb.Worksheets("Sheet1")
3:　 Set sh2 = wb.Worksheets("Sheet2")
'--------Setting End ----------
　　 SetRow = sh1.Cells(Rows.Count, 4).End(xlUp).Row
　'WorksheetFunction.Count は、数値の数を数えるものです。意味が違います。
　With sh1
　　For i = 2 To SetRow
　　　If .Cells(i, 4).Value <> "" Then　'D列に空行がなければ、不要
　　　　.Cells(i, 16).Value = sh2.Cells(i, 11).Value - .Cells(i, 10).Value
　　　End If
　　Next i
　End With
　Exit Sub
ErrHandler:
　If Err.Number = 9 Then
　　If Erl = 1 Then
　　　MsgBox "ブック名が間違っています。", 16
　　ElseIf Erl = 2 Then
　　　MsgBox "1番目のシート名が違っています。", 16
　　ElseIf Erl = 3 Then
　　　MsgBox "1番目のシート名が違っています。", 16
　　Else
　　　MsgBox "他の部分で名称が違っています。", 16
　　End If
　ElseIf Err.Number > 0 Then
　　MsgBox Err.Number & ": " & Err.Description
　End If
End Sub

この回答へのお礼

どうもありがとうございます。テストしてみたら、”ブック名が間違っています”でした。いろいろとエラー9の件を調べたら、https://oshiete.goo.ne.jp/qa/6043451.htmlが出てきて、「登録されている拡張子は表示しない」のチェックが外れてるとエラーが出るとあり、つけて見ましたが、同じエラー9でした。おそらくブック名を指定しないといけないのかも知れませんが、特にブック名を指定するマクロは作っておりません。逆にブック名をあいまいにしたいのですが、どのような方法がありますでしょうか？　ご存知であれば教えていただけるとありがたいです。どうぞよろしくお願いいたします。

お礼日時：2017/11/25 15:12

No.6 回答者： GooUserラック 回答日時： 2017/11/23 14:47

No.3 の補足

この前にコードが存在しないと「Setting1」には何も代入されませんよね！
エラーしたときに、「デバック」ボタンを押して「Setting1」にカーソルを持ってきたら「Setting1 = ○○」のような表示が出るはずですがどう表示されますか？

この回答へのお礼

どうもありがとうございました。今のところ仮シート1に入っているものの数が2行しかないのでSetting1 = 2と表示されます。Setting1 =　ActiveWorkbook.Sheets("仮シート1").Range("A1").Offset(Sheets("仮シート1").Rows.Count-1,0).End(xUp).Rowと入力しています。

お礼日時：2017/11/25 15:26

No.5 回答者： tatsu99 回答日時： 2017/11/23 13:30

英語版のエクセルのようにみえますが、

英語版で、仮シート１、仮シート２のような名称は使えるのでしょうか。
実際のシート名を半角の英字だけにして、その名称を使用してはいかがでしょうか。

この回答へのお礼

ありがとうございます。日本語のosでExcelを日本語から英語に切り替えているだけなので問題ありません。

お礼日時：2017/11/25 15:29

No.4 回答者： WindFaller 回答日時： 2017/11/23 11:03

>エラーはSubscript out of range error 9です。

日本語版なら、以下のようにでます。
「インデックスが有効範囲にありません。」
前の方がご指摘のように、シート名なのか、Setting1 のどちらかでしょう。その名称が違っているという意味です。「何でもよい」ということではなくて、正しく入っていないのではないかということです。

例：
全角と半角を間違えてもエラーが出ます。ご質問の最初のコードの中にも混在しています。

Sheets("仮シート２")　　全角
Sheets("仮シート1") 　　半角

Set sh1 = Worksheets("Sheet１")　'エラー
正しくは、
Set sh1 = Worksheets("Sheet1")　'OK

それと、WorksheetFunction.Count は、数値の数を数えるものです。VBAでは、数が合わない可能性があります。

For i = 2 To sh1.Cells(Rows.Count, 4).End(xlUp).Row
ふつうは、このようにEndプロパティを利用します。

この回答へのお礼

どうもありがとうございます。今のところ仮シート1に入っているものの数が2行しかないのでSetting1 = 2と表示されます。Setting1 =　ActiveWorkbook.Sheets("仮シート1").Range("A1").Offset(Sheets("仮シート1").Rows.Count-1,0).End(xUp).Rowと入力しています。ちょっと遠回りをしていたようです。簡単に、して下記のようにしてますがまだ駄目のようです。

For Calc = 2 To ActiveWorkbook.Sheets("仮シート1").Cells(Rows.Count, 1).End(xlUp).Row
MSGBOX ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11)).Value　＜＜エラー9（変更１）
MSGBOX ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11)) '＜＜エラー9 （変更２）
Value1 = ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11)).Value
MSGBOX ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11))
Value2 = ActiveWorkbook.Sheets("仮シート1").Range(Cells(Calc, 11))
ActiveWorkbook.Sheets("仮シート1").Range(Cells(Calc, 16)) = Value1 - Value2
Next Calc

お礼日時：2017/11/25 15:41

No.3 回答者： GooUserラック 回答日時： 2017/11/23 09:18

「Setting1」に何か代入されていないからでは？

No.2 回答者： GooUserラック 回答日時： 2017/11/23 09:15

エラーの内容は何ですか？

変数名はこの通りでしょうか？

この回答へのお礼

どうもありがとうございました。　下記のように変更していますが、まだ駄目のようです。
For Calc = 2 To ActiveWorkbook.Sheets("仮シート1").Cells(Rows.Count, 1).End(xlUp).Row
MSGBOX ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11)).Value　＜＜エラー9（変更１）
MSGBOX ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11)) '＜＜エラー9 （変更２）
Value1 = ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11)).Value
MSGBOX ActiveWorkbook.Sheets("仮シート2").Range(Cells(Calc, 11))
Value2 = ActiveWorkbook.Sheets("仮シート1").Range(Cells(Calc, 11))
ActiveWorkbook.Sheets("仮シート1").Range(Cells(Calc, 16)) = Value1 - Value2
Next Calc

お礼日時：2017/11/25 15:45

No.1 回答者： GooUserラック 回答日時： 2017/11/23 08:45

もしかしたらシート名の「２」が半角でなく全角なので「仮シート２」が存在しないだけでは？

この回答へのお礼

どうもありがとうございます。仮シート2は存在します。

お礼日時：2017/11/25 15:46