【処理概要】
担当者一覧ファイルがあり、そのファイルには各担当者の目標と売上がデータが入っています。
各担当者に目標を入力してもらうため、担当者別にファイルを作成し、かつ目標列は各担当者ファイルの対応する列を参照する数式に変更します。
例)担当者一覧.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ベストアンサー
- 回答日時:
要は、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
- 回答日時:
マクロでやる場合は、普通は質問者のやっているようなこと(ロジックでは)でやらない。
質問者がマクロやプログラムの経験が少なく、質問のように思いついたのだろうが、珍奇であると思う。
またエクセルの数式に思い入れがありすぎて(あるいは他を知らないから)そういうやり方をやろうとしたのだろうが
ーー
変える気は無いと思うが参考までに。
今後もマクロを使おうと思うなら、もっと勉強・経験しないとだめとおもいます。
ーー
データを寄せ集めればよいと思うので
集約シートをSheet1
各営業マンのデータSheet2、Sheet3、Sheet4の例でテスト
Sub test01()
k = 2 '集約シートは第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 '集約シートの1行下を選択するため
Next i
End If
Next
End Sub
ただし 集約シートにはデータしか移ってない。関数式は別途手動で。
ーー
結果
佐藤食品108
佐藤雑貨2026
佐藤衣類3020
佐藤サービス4038
山田食品158
山田雑貨2424
山田衣類3225
山田サービス167
田中食品159
田中雑貨2120
田中衣類3426
田中サービス3534
田中飲料1512
元データは省略するが、どんなものかわかりますね。
この回答への補足
数式を入力するよりも、マクロで各ファイルからデータをとってくるイメージで捕らえてもよろしいでしょうか?
ご指摘の通り、マクロやプログラムの経験はなく、初めてのコーディングです。
No.1
- 回答日時:
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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(プログラミング・Web制作) python OpenPyXLを使って出力結果をエクセルに書き込み 2 2022/06/04 19:46
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Visual Basic(VBA) VBAの参照先のファイル名をセルに書いて代入したい 2 2022/04/04 13:42
- Visual Basic(VBA) エクセルのマクロについて教えてください。 2 2023/06/04 09:39
- Visual Basic(VBA) エクセルのマクロについて教えてください。 2 2023/03/12 10:10
- Excel(エクセル) エクセルのマクロについて教えてください。 2 2023/02/20 14:46
- Visual Basic(VBA) エクセルのマクロについて教えてください。 4 2023/03/02 08:40
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/06/01 14:45
- Visual Basic(VBA) エクセルのマクロを使ってメールを送る方法について教えてください 2 2022/03/29 01:36
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのブック分割マクロを...
-
文字の色も参照 VLOOKUP
-
VBAで繰り返しコピーしながら下...
-
Excel VBA ピボットテーブルに...
-
ExcelのVlookup関数の制限について
-
Excelで全てのシートに一気に列...
-
Excelでの並べ替えを全シートま...
-
EXCELでシート1で作ったデータ...
-
Excel 2段組み
-
Excelで条件別にシートを振り分...
-
別シートから月(MONTH)で抽出す...
-
VBAで検索して、行をコピー&追...
-
エクセル マクロ 標準モジュー...
-
エクセルの保護で、列の表示や...
-
エクセルVBA データを別シート...
-
エクセルで横並びの複数データ...
-
エクセルVBA 行追加時に自...
-
エクセルで別シートの数値が一...
-
予定表に日本の休日を一気に入...
-
【条件付き書式】countifsで複...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ExcelのVlookup関数の制限について
-
文字の色も参照 VLOOKUP
-
オートフィルタ使用時にCOUNTIF...
-
エクセルの保護で、列の表示や...
-
VBAで繰り返しコピーしながら下...
-
エクセル関数に詳しい方、教え...
-
【条件付き書式】countifsで複...
-
Excel の複数シートの列幅を同...
-
エクセル マクロ 標準モジュー...
-
エクセルで横並びの複数データ...
-
エクセルの列の限界は255列以上...
-
Excelでの並べ替えを全シートま...
-
VLOOKアップ関数の結果の...
-
SUMPRODUCTにて別シートのデー...
-
エクセルで、チェックボックス...
-
Excel VBA ピボットテーブルに...
-
【エクセル】1列のデータを交...
-
エクセルVBAで、ある文字を含ん...
-
エクセルのブック分割マクロを...
-
excel 複数のシートの同じ場所...
おすすめ情報