変数をうまく使えずに悪戦苦闘しています。

質問

多分、まったく内容がなっていないというお叱りを受けそうですが、
今私が考えられるところはここまでです。
何が原因で動かないのか、分かりません。
よろしくお願いします。
確認用シートにデータがあります。データは個人カウントと月ごとの集計で成り立っています。
そのデータの個人名と月で検索した回答を集計用シートに表示させたいと考えています。
それで次のようなコードを書いてみましたが、思い通りの結果を出すことができませんでした。
式は直接式を入力すると正しい答えが出るのですが、コードを実行させた場合は＃NAME?に
なってしまいます。こういう場合は変数は使えないのでしょうか?

また、集計で得た結果を結果シートに値のみ貼り付けてエビデンスを残したいのですが、
これもうまくいきません。
貼り付けた後順位の項目でソートさせたいのですが、これもうまくいきませんでした。

また、ピボットテーブルに毎月のデータを入力させて、ピボットグラフを作るように
テーブルとグラフをあらかじめ作成し、前月のデータを集計させたかったのですが、
これも見事に失敗しました。

ところで、もし当月が1月の場合は12月を見るようにさせたいのですが、IF文がうまく
できません。IF文を認識してくれていないので、END IFにするとIFがありませんとエラーが
返ってきます。

まったくお手上げです。
よろしくお願いします。

Sub カウント()

'式の割当

Dim a As Long, b As Long, d As String, e As String
Dim getRange As String, getpreRange As String

a = Sheets("集計用").Range("B87").Value
b = a - 1
d = "_" & a & "月"
e = "_" & b & "月"

If a = 1 Then b = 12

getRange = "=INDEX(確認用データ,H89,d)"
getpreRange = "=INDEX(確認用データ,H89,e)"

Sheets("集計用").Range("C89").Formula = getRange
Sheets("集計用").Range("D89").Formula = getpreRange

Sheets("集計用").Range("C89:D89").Copy Sheets("集計用").Range("C90:C128")

'結果シートに値のみ貼り付け, 順位で並べ替え

Sheets("集計用").Range("B87:E128").Select
Selection.Copy
Sheets("結果").Range("A1").End(xlRight).Offset(0, 2).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Selection.Sort Key1:=Range("順位"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal

'前月のデータからピボットテーブルを作成する

Dim r As String

r = a & "月"

ActiveSheet.PivotTables("ピボットテーブル2").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル2").PivotFields(r), "合計 / r", xlSum

End Sub

回答No.1

hallo-2007 · ベストアンサー

とりあえず
getRange = "=INDEX(確認用データ,H89,d)"
は
getRange = "=INDEX(確認用データ,H89," & d & ")"

だけど
INDEX関数は
INDEX(範囲,行番号,列番号）なので
d = "_" & a & "月"　と文字を入れることがおかしい。
加えて
a = Sheets("集計用").Range("B87").Value
・・・
Sheets("集計用").Range("C89").Formula = getRange
とB87の値に応じて、集計用のシートのC89の関数を変えるくらいだった
元々から
C89に入れる関数を考えれば良いと思うのですが。
VBAで処理する必要性が？です。

お礼

hallo-2007さん

丁寧な回答、有難うございました。
結局、自分で解決しました。

ご指摘の通りINDEX関数はセルに名前を定義していたのですが、
それが使えなかったので、結局列の番号にも参照する対照表を作って、変数で検索して、対象する列番号の数値を拾ってくるように作り直しました。

>B87の値に応じて、集計用のシートのC89の関数を変えるくらい
>だった
>元々から
>C89に入れる関数を考えれば良いと思うのですが。

行番号はセルの参照だけで澄みますが、列番号が変数なので、セルに関数を入れてすませることは出来なかったのです。
つまり、当月（例えば3月）だったら前月（2月）の合計の値を拾ってくるような。
なので、変数をどうしても使いたかったのでvbaを使いました。

hallo-2007さん、お忙しい中、質問に答えてくださった事を心より感謝いたします。本当に有難うございました。