マクロでシートからシートへの数式をいれる

ｺｰﾄﾞがわかりません。

条件

①ｼｰﾄA種目　と　ｼｰﾄA科目　があり
②A種目　に　数式をいれるのが目的
③三行で一式になる表
④上から順番にA科目のB列に計がある時
⑤A種目に数式　=A科目!E○○　を代入
⑥○○は計の一つ下の行のE列のセルになる
⑦数式を入れるセル　A種目のC列に数値1がある行のE列

何と何をくみあわせればいいのかわかりません。

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

• 

  https://gyazo.com/ba552b516ec77045669875940e65da8d
  https://gyazo.com/fdbc3fb1557c7b6ac0eed7ee038b97bd
  画像添付しました。
  
  ＞A科目のB列に計がある時
  ＞A種目のC列に数値1がある行
  
  対になる同じ数量だけあります。
  表で種目別のｾﾙE6に数式=A科目!E48がはいります。
  
  種目・科目　共に間に小計などもでてきますが、【計】の数と【１式】は同じ数だけ必ずあります。
  そして、上から順番に対になっています。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/04/01 10:43

• 補足です。
  1.
  A種目の
  第三区画
  棚X
  第三区画
  棚Y
  第三区画
  棚Z
  第七区画
  棚Z
  の順に
  科目の方も上から同じ順番で計が並んでいます。
  この順番はかわりません。
  2.
  基本的には半角スペースが二つ入っています。
  しかし、これはあくまでもこのケースなので、別の違うカテゴリーになると、第○○区画だけになったり在庫棚とかだけの表記になります。第〇区画 棚□のグループ分けだけでは無いのですよ・・・
  3.
  予備区画などの一単語にはスペースははいりません。
  4.
  B3はセル結合していません。線がないだけで、単一セルになっています。
  
  ※絶対に揺るがな法則は種目の順番に科目の計が並んでいることですね。
  
  ややこしくてすみません。

  No.5の回答に寄せられた補足コメントです。 補足日時：2021/04/02 10:07

• 補足です
  あらたに発見したのですが
  科目に第三区画 棚X　とありますが
  このパターンの他に、一単語（予備区画）などのほかに
  販売物第五区画 棚J
  郵送物第一区画 棚８
  などの場合もでてきます。
  そして種目には
  これがわけて表記されています。
  二行で
  販売物□□□
  第五区画□□棚J
  □は半角スペースです。
  実際、半角スペースがどこに何個入っているのかはランダム過ぎて困っています。

  No.6の回答に寄せられた補足コメントです。 補足日時：2021/04/02 10:21

• 

  一点だけ補足ぬけがありました
  種目表のカテゴリーがかわる時に計で一旦しめるのですが
  行数が多いと同じ形態の表がつぎのページにあり。そこに小計や計がきます
  その後、新たな同形態のページから項目がはじまります
  その時にうまく判断してくれなくて小計三行の上段に＝科目＃＃が入ってしまいます
  そしてその下のページまで読み取ってくれません

  No.8の回答に寄せられた補足コメントです。 補足日時：2021/04/02 11:36

• 

  続きです
  種目のページがかわる例
  ---------------------------------------1ペー
  種目別内訳
  名称
  ○○　1式　＝科目＃＃　
  小計　　　 □□円
  ○○　1式　＝科目＃＃
  ----------------------------------------2ペー
  種目別内訳
  名称
  　　上段　　＝科目＃＃　←これがくる
  小計　　　 □□円　　　
  計　 　□□□□円
  [
  [
  [
  [
  
  [
  ----------------------------------------3ペー
  種目別内訳
  [
  名称
  ○○　1式　　　←ここまでこない
  ○○　1式　
  計　　　　□□円

  No.9の回答に寄せられた補足コメントです。 補足日時：2021/04/02 11:37

• 

  https://gyazo.com/41b5db6ea707502cf106c9c5cca615a4
  
  雑ですがページはこのような感じです。

  No.11の回答に寄せられた補足コメントです。 補足日時：2021/04/02 13:10

No.5 回答者： tatsumaru77 回答日時： 2021/04/01 19:44

補足要求です。

１．A科目の最初の計は、B3に「第三区画　棚X」があり、この「計」の次の行のE48のセルが対象となります。
これに対応するA種目は、B5、B6の第三区画,棚Xになります。
あなたが提示されたサンプルでは１例だけなので、以下の判断が付きません。
A種目は、
第三区画,棚X
第三区画,棚Y
第三区画,棚Z
第七区画,棚Z
第七区画,棚J
・・・のように並んでますが、
A科目も
上記のならびに完全に一致して
第三区画　棚X
第三区画　棚Y
第三区画　棚Z
第七区画　棚Z
第七区画　棚J
・・・とならんでいるのでしょうか。
それとも、以下のように並びは、ばらばらの場合もあるのでしょうか。
第七区画　棚Z
第三区画　棚Z
第七区画　棚J
第三区画　棚Y
第三区画　棚X

２．A科目は、B3に「第三区画　棚X」がありますが、
この第三区画と棚Xとの間の空白は、どうなっていますか。
①半角スペースが１つ
②半角スペースが１つ又は２つ以上
③全角スペースが１つ
④全角スペースが１つ又は２つ以上
⑤半角スペースと全角スペースが混在
上記の①～⑤のどれになりますか。

３．A種目には、「予備区画」がありますが、
これに対応するA科目では、「予備区画」となっていますか。（前後に空白が付くことはないですか）

４．A科目は、B3に「第三区画　棚X」とありますが、これがA種目と対応させるときの、キーとなります。
「第三区画　棚X」と書かれているセルは結合セルとなっていますが、他のキーのセルも結合セルとなっていますか。
「第三区画　棚Y」、「第三区画　棚Z」のセルも結合セルですか。
要は結合セルにはキーが格納されているという判断をして良いかどうかの質問です。

A科目のB列には
・第三区画　棚X
・第一書類
・第二書類
等の文字があるが、マクロではどれがキーなのかが判断つかない為の質問です。
（人間は総合的に判断するので「第三区画　棚X」になりますが、マクロでは判断できません）

No.4 回答者： めぐみん_ 回答日時： 2021/04/01 18:28

そこのエラーで思い当たるとしたら、

Const ws1_f As String = "=Ａ科目!En" '集計先への参照元名＋列文字＋行番号の仮文字(n)

数式にするこの部分でシート名（全角半角含め）の違いかセルのＥや最初のが全角になっている、間の ! が抜けているとかかな。

この回答へのお礼

できました。
コードを間違えていました。
お力添えありがとうございました。

お礼日時：2021/04/02 11:56

No.3 回答者： めぐみん_ 回答日時： 2021/04/01 16:11

＞重複しない条件と言うことになると　計値が同じ数値になる場合に反応しないと言う事になるのでしょうか？

の意味が良くわかりません。

＞そして、上から順番に対になっています。

基本取得した順に該当する場合は代入していくだけです。
他に条件があるとかは考慮してません。
あとエラーの場合はそのエラーの内容を提示して下さい。
まずは私が作成したダミーデータのようなもので結果がキチンと出ているのか確認し、それで問題ないのなら本番のコピーブックで検証が宜しいかも。

No.2 回答者： めぐみん_ 回答日時： 2021/04/01 14:12

データは提示して頂いたものよりかなりいい加減ではありますが目的の、

・Ａ科目にある計の１つ下のＥ列のアドレスを使い数式とする。
・Ａ種目の上記数式はこのシートのＣ列に1があった場合は、同行のＥ列にＡ科目で該当した数式を順に当てはめる。

は叶えているはずです。
ご検証願います。

Sub megu_2()
Const ws1 As String = "Ａ科目" '集計元のシート名
Const ws2 As String = "Ａ種目" '集計先のシート名
Const ws1_f As String = "=Ａ科目!En" '集計先への参照元名＋列文字＋秒番号の仮文字(n)
Dim myDic As Object
Dim r As Range, rr As Range, n As Long

Set myDic = CreateObject("Scripting.Dictionary")
n = 1

With Worksheets(ws1)
.Range("B4:F" & .Cells(Rows.Count, "D").End(xlUp).Row).AutoFilter 1, "計"
For Each r In .Range("B5", .Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeVisible)
myDic.Add n, r.Offset(1).Row
n = n + 1
Next
.Cells.AutoFilter
End With

n = 1

With Worksheets(ws2)
.Range("B4:F" & .Cells(Rows.Count, "D").End(xlUp).Row).AutoFilter 2, 1
For Each r In .Range("C5", .Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlCellTypeVisible)
If n > myDic.Count Then Exit For
r.Offset(, 2).Formula = Replace(ws1_f, "n", myDic(n))
n = n + 1
Next
.Cells.AutoFilter
End With
Set myDic = Nothing

End Sub

コード最初のシート名と数式については適宜修正をお願いします。

この回答へのお礼

お返事ありがとうございます。

検証結果ですが。

With Worksheets(ws1)
.Range("B4:F" & .Cells(Rows.Count, "D").End(xlUp).Row).AutoFilter 1, "計"
For Each r In .Range("B5", .Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeVisible)
myDic.Add n, r.Offset(1).Row
n = n + 1
Next
.Cells.AutoFilter
End With

n = 1

With Worksheets(ws2)
.Range("B4:F" & .Cells(Rows.Count, "D").End(xlUp).Row).AutoFilter 2, 1
For Each r In .Range("C5", .Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlCellTypeVisible)
If n > myDic.Count Then Exit For
----------------------------------------------------------
r.Offset(, 2).Formula = Replace(ws1_f, "n", myDic(n))
----------------------------------------------------------
↑この位置でデバッグ
n = n + 1
Next
.Cells.AutoFilter
End With
Set myDic = Nothing
End Sub
----------------------------------------------------------
r.Offset(, 2).Formula = Replace(ws1_f, "n", myDic(n))
----------------------------------------------------------
重複しない条件と言うことになると　計値が同じ数値になる場合に反応しないと言う事になるのでしょうか？

お礼日時：2021/04/01 15:44

No.1 回答者： めぐみん_ 回答日時： 2021/03/31 15:36

どんな表とどんな表？とで処理をしなきゃいけないのか。

今回のは画像がないのでしょうか？
あと数式を入れていくってのも何を基準に入れるかはわかりますが、そこに入れる数式がどれなのか？と言うのはどうやって決めるのでしょう？

＞A科目のB列に計がある時
＞A種目のC列に数値1がある行

この２つって対になる同じ数量だけあるのですか？
そして上から順に相互が組になっていて入れていけばいい？