マクロで数式を書き換えたい。

質問

【処理概要】
担当者一覧ファイルがあり、そのファイルには各担当者の目標と売上がデータが入っています。
各担当者に目標を入力してもらうため、担当者別にファイルを作成し、かつ目標列は各担当者ファイルの対応する列を参照する数式に変更します。
　例）担当者一覧.xlsxの「C2」はA.xlsxの「C2」を参照する。（数式：=[佐藤.xlsx]Sheet1!C2)

【悩み】
担当者別にファイルを作成するマクロは出来たのですが、目標列に元から入っている数式をうまく変更することが出来ません。

例えば、添付画像の場合、担当者一覧ファイルの目標列の数式は上から
(1)
=[佐藤.xlsx]Sheet1!C2
=[佐藤.xlsx]Sheet1!C3
=[佐藤.xlsx]Sheet1!C4
=[鈴木.xlsx]Sheet1!C2
=[鈴木.xlsx]Sheet1!C3
=[山田.xlsx]Sheet1!C2
=[山田.xlsx]Sheet1!C3

となることが正しいのですが、forメソッドを使用して実行したところ、以下のようになりました。
(2)
=[佐藤.xlsx]Sheet1!C2
=[佐藤.xlsx]Sheet1!C3
=[佐藤.xlsx]Sheet1!C4
=[鈴木.xlsx]Sheet1!C5
=[鈴木.xlsx]Sheet1!C6
=[山田.xlsx]Sheet1!C7
=[山田.xlsx]Sheet1!C8

数式の書き換えは、Replaceメソッドを使用していますが、一括で置換されてしまうため、
以下のような記述をしています。

Dim a, b, Row, RowEnd As Long
Dim Emp As String

Row = 2
For Row To RowEnd
　Emp = Range("A" & Row ).Value
　a = "=SUM(E:" & Row & "G:" & Row & ")"　←例です。
　b = "=[" & Emp & ".xlsx]Sheet1!C" & Row

　Range("C" & Row ).Select
　Selection.Replace what:= a, Replacement:= b
Next Row

この記述では(2)になるように作ってしまっているのですが、ここから(1)のようにするにはどうすればよいかわかりません。。。

不躾で、大変申し訳ございませんが、どなたかヒントをくださいますでしょうか。
ご不明な点がございましたら回答致しますので、よろしくお願いします。

回答No.3

mt2008 · ベストアンサー

要は、A列の氏名が変わったら、リンク先の行を2からカウントし直したいと言う事ですよね？

そうであれば、前回の氏名を覚えておく変数と、リンク先の行数用の変数を用意して↓の様にします。

Dim b, Row, RowEnd As Long
Dim Emp As String

Dim Emp2 As String '前回氏名
Dim Row2 As Long 'リンク先Row

RowEnd = 8
Emp2 = "" '前回氏名の初期値
For Row = 2 To RowEnd
　　Emp = Range("A" & Row).Value
　　If Emp <> Emp2 Then Row2 = 2 '今回の氏名と、前回氏名が異なったらリンク先Rowは2にリセット
　　b = "=[" & Emp & ".xlsx]Sheet1!C" & Row2
　　Range("C" & Row).Formula = b　'Replaceは使いません
　　Emp2 = Emp '今回の氏名を、前回氏名変数に代入
　　Row2 = Row2 + 1 'リンク先Rowをカウントアップ
Next Row

また「Row」の様にプロパティ等で良く使用される単語を変数名にすると混乱の元ですのでお勧めしません。

回答No.2

imogasi

マクロでやる場合は、普通は質問者のやっているようなこと（ロジックでは）でやらない。
質問者がマクロやプログラムの経験が少なく、質問のように思いついたのだろうが、珍奇であると思う。
またエクセルの数式に思い入れがありすぎて（あるいは他を知らないから）そういうやり方をやろうとしたのだろうが
ーー
変える気は無いと思うが参考までに。
今後もマクロを使おうと思うなら、もっと勉強・経験しないとだめとおもいます。
ーー
データを寄せ集めればよいと思うので
集約シートをＳｈｅｅｔ１
各営業マンのデータＳｈｅｅｔ２、Ｓｈｅｅｔ３、Ｓｈｅｅｔ４の例でテスト
Sub test01()
k = 2 '集約シートは第２行から
Set sh1 = Worksheets("Sheet1") '集約シート
For Each sh In Worksheets '各シートについて
If sh.Name <> "Sheet1" Then '集約シート以外は
d = sh.Range("A65536").End(xlUp).Row '最下行取得
For i = 2 To d '各列データを集約シートに
sh1.Cells(k, "A") = sh.Cells(i, "A")
sh1.Cells(k, "B") = sh.Cells(i, "B")
sh1.Cells(k, "C") = sh.Cells(i, "C")
sh1.Cells(k, "D") = sh.Cells(i, "D")
k = k + 1 '集約シートの１行下を選択するため
Next i
End If
Next
End Sub
ただし　　集約シートにはデータしか移ってない。関数式は別途手動で。
ーー
結果
佐藤 食品 10 8
佐藤 雑貨 20 26
佐藤 衣類 30 20
佐藤 サービス 40 38
山田 食品 15 8
山田 雑貨 24 24
山田 衣類 32 25
山田 サービス 16 7
田中 食品 15 9
田中 雑貨 21 20
田中 衣類 34 26
田中 サービス 35 34
田中 飲料 15 12
元データは省略するが、どんなものかわかりますね。

補足

数式を入力するよりも、マクロで各ファイルからデータをとってくるイメージで捕らえてもよろしいでしょうか？

ご指摘の通り、マクロやプログラムの経験はなく、初めてのコーディングです。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

Sub マクロ自信ないけど()
Dim A As String
Dim n As Long
Dim i As Long
　 A = "" '初期値入力
　 'for～next構文
　 For n = 2 To Range("A2").End(xlDown).Row
　 　 '数式の行番号を判定する
　 　 If A = Range("A" & n) Then
　 　 　 i = i + 1
　 　 Else
　 　 　 i = 2
　 　 End If
　 '下記は数式を書き換えてください
　 Range("B" & n).Formula = "=Sheet1!C" & i
　 '判定の値を代入
　 A = Range("A" & n).Value
　 Next
End Sub