【VBA】SUMIFを別シートから参照する方法

質問

半端な知識で切り張りして書きましたが
SUMIFのコードでコンパイルエラーとなります。
ご教示宜しくお願いします。

'ws1は、アクティブシート(=SUMIFを入力するシート)
Set ws1 = Worksheets("アクティブシート")

'ws2は、参照するシート
Set ws2 = Worksheets("参照するシート")

'Hani1は、ws2のA2セルから最終行迄
Set Hani1 = ws2.Range("A2:A" & LastRow)

'Hani2は、ws2のH2セルから最終行迄
Set Hani2 = ws2.Range("H2:H" & LastRow)

'n4はアクティブシートの2行目から最終行迄
For n4 = 2 To LastRow2

'アクティブシートのCｎ4セルから順に、An4セルに合致するものについて
Hani1、Hani2を参照してSUIFする
Range("C" & n4).Formula = _
"=SUMIF(Hani1,ws1.Range("A" & n4),Hani2)"

Next n4

回答No.2

kkkkkm · ベストアンサー

$A$2のようにそれぞれ絶対参照でしたら
Range("C" & n4).Formula = _
"=SUMIF(" & ws2.Name & "!" & Hani1.Address & "," & ws1.Range("A" & n4).Address & "," & ws2.Name & "!" & Hani2.Address & ")"

A2のようにそれぞれ相対参照でしたら
"=SUMIF(" & ws2.Name & "!" & Hani1.Address & "," & ws1.Range("A" & n4).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "," & ws2.Name & "!" & Hani2.Address & ")"

でやってみてください。

お礼

意図通りになり、有難うございました。
今は時間が無く、コードを読み説く事まで出来ませんが、
近々で勉強させて頂きます。

回答No.3

kkkkkm

蛇足ですが
もしws1がSUMIFを入力するシートと違うシートになる可能性があるのでしたら

ws1.Name & "!" & Range("A" & n4).Address

にしてください。

回答No.1

kkkkkm

セルに記載する数式の文字列になるように右辺を作成する必要があります。

"=SUMIF(Hani1,ws1.Range("A" & n4),Hani2)"

だと""の中に"がある場合""A""のようにしないとエラーになります
ただ、上記の場合だと""A""にしたとしても
=SUMIF(Hani1,ws1.Range("A" & N4),Hani2)
とセルに記載されますのでVBAではエラーになりませんが数式としてエラーになります。

セルにそのまま記載できる文字列と変数の部分(文字列になったときに数式として有効な値の変数)を分けて&で繋いでください。

Range("C" & n4).Formula = _
"=SUMIF(" & ws2.Name & "!" & Hani1.Address & ",""" & ws1.Range("A" & n4).Value & """," & ws2.Name & "!" & Hani2.Address & ")"

数式によっては(数値と文字列が区別される数式だと)
",""" & ws1.Range("A" & n4).Value & ""","
の部分(セルのデータをセルで指定する場合)は文字列だと上記でいいですが
数値だと
"," & ws1.Range("A" & n4).Value & ","

補足

うまく動き、驚愕しています。「"」の理解が出来ていませんでした。

もし可能で有れば、
",""" & ws1.Range("A" & n4).Value & ""","
又は
"," & ws1.Range("A" & n4).Value & ","

を、エクセル関数のように、数式バーに「A2」等と表示される様には出来ないでしょうか。
動作上は何ら不具合は有りませんが。

宜しくお願いします。