[[20220303142521]]　『マクロを使用した関数の挿入を行いたい』（ぺんぎん）　ページの最後に飛ぶ

『マクロを使用した関数の挿入を行いたい』（ぺんぎん）

毎日集計したデータを作り、別のエクセルに貼付てまとめています。
張り付ける場所が奇数行・偶数行と関数が少し違います。
奇数行：=IF(ISERROR(VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,3,0)),0*1,VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,3,0))

偶数行：=IF(ISERROR(VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,4,0)),0*1,VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,4,0))

１日目：Ｅ列５行目～Ｅ列６２行目
２日目：Ｆ列５行目～Ｇ列６２行目
・
・
・
３１日目：ＡＩ列５行目～ＡＩ列６２行目
最大で３１日目まであります

私は、関数を挿入する方法が
Range("E5").Value = "=IF(ISERROR(VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,3,0)),0*1,VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,3,0))"

こういう方法しか分かりません
ただこれだと、５８行×３１列分必要になるので…
しかも、いらない部分にも張り付いてしまう可能性があり(月で曜日が違うので)、ボタンなどを３１個作る形になる…（ボタン⇒マクロ設定したボタン）

行いたいのは、
Ｅ５セルを選択した状態でマクロを実行した場合はＥ列に関数を張り付ける
Ｆ５セルを選択した状態でマクロを実行した場合はＦ列に関数を張り付ける
この様にできたらと思っています。

また、関数を沢山書かなくても「＋２」していく事ができたらと思っています。
出来な場合、
=IF(ISERROR(VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,3,0)),0*1,VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,3,0))
=IF(ISERROR(VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,4,0)),0*1,VLOOKUP($B5,[集計先.xlsx]Sheet2!$A:$D,4,0))
=IF(ISERROR(VLOOKUP($B7,[集計先.xlsx]Sheet2!$A:$D,3,0)),0*1,VLOOKUP($B7,[集計先.xlsx]Sheet2!$A:$D,3,0))
=IF(ISERROR(VLOOKUP($B7,[集計先.xlsx]Sheet2!$A:$D,4,0)),0*1,VLOOKUP($B7,[集計先.xlsx]Sheet2!$A:$D,4,0))
このように「＋２」づつ増やしている関数を全て明記しないといけないので…

以上よろしくお願いします。

＜使用 Excel：unknown、使用 OS：Windows10 ＞

よくわかりませんが、こんな感じでしょうか。

 Sub Test()
    Dim c As Long, n As Long, r As Long

    c = ActiveCell.Column
    n = 5
    For r = 5 To 61 Step 2
        Cells(r + 0, c).Formula = "=IF(ISERROR(VLOOKUP($B" & n & ",[集計先.xlsx]Sheet2!$A:$D,3,0)),0*1,VLOOKUP($B" & n & ",[集計先.xlsx]Sheet2!$A:$D,3,0))"
        Cells(r + 1, c).Formula = "=IF(ISERROR(VLOOKUP($B" & n & ",[集計先.xlsx]Sheet2!$A:$D,4,0)),0*1,VLOOKUP($B" & n & ",[集計先.xlsx]Sheet2!$A:$D,4,0))"
        n = n + 2
    Next
 End Sub

（わからん） 2022/03/03(木) 14:58

IFERROR関数やMOD,ROW()関数などを使えば、もともとの式をまとめられそうですが。
（γ） 2022/03/03(木) 15:05

（わからん）さん、完全にそういう事です！
ありがとうございます。

こんなさっぱりした式でできるんですね…

因みに、これを張り付けた後に値貼り付けする事は可能ですか？
（ぺんぎん） 2022/03/03(木) 16:35

 参考までに。

 Sub test()
    Dim c As Long
    c = ActiveCell.Column
    With Cells(5, c).Resize(58, 1)
        .Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[集計先.xlsx]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
        .Value = .Value
    End With
 End Sub

（γ） 2022/03/03(木) 17:00

Ｙさん、ありがとうございます。

.Value = .Value
これが値貼り付けですか？

というか、２つの関数が１個でまとまってるんですか？

どういう考え方ですか？
（ぺんぎん） 2022/03/03(木) 17:22

 ＞.Value = .Value
 ＞これが値貼り付けですか？
 はい。

 ＞というか、２つの関数が１個でまとまってるんですか？
 はい。

 =IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),
                  Sheet2!$A:$D,
                  IF(MOD(ROW(),2)=1,3,4),
                  FALSE),
          0
  )
 （ブック指定は回答上省略しましたが。）

 これをじっくり睨みつけてみてください。
（γ） 2022/03/03(木) 17:37

（γ）さん、もう１っ個教えてください。
５行目から６２行目までデータを張り付けたあと
６３行目・６４行目は張り付けないで
６５行目・６６行目にデータを張り付ける事は可能でしょうか？
（ぺんぎん） 2022/03/04(金) 11:52

いったん６６行目まで貼り付けたあと、
６３,６４行目の当該列をClearContentsしたらいかがでしょうか。

既にそこに入力があって消去することができない状況であれば、
２つの部分にわけて書き込みすればよいと思います。
（γ） 2022/03/04(金) 13:11

Ｙさん返信ありがとうございます。
６３,６４行目は、既にデータが入っています。
そうはいっても
63行目（Ｆ列の場合）
=F65-F5-F7-F9-F11-F13-F15-F17-F19-F21-F23-F25-F29-F31-F33-F35-F37-F41-F43-F45-F47-F49-F51-F53-F55-F57-F59-F61

64行目（Ｆ列の場合）
=F66-F6-F8-F10-F12-F14-F16-F18-F20-F22-F24-F26-F30-F32-F34-F36-F38-F42-F44-F46-F48-F50-F52-F54-F56-F58-F60-F62

こんだけの計算式が入っているだけです。
これも、同じ様にＦ列などの指定ない状態で張り付けられたら別に良いのですが…

頂いた式にこんなんかなって居れたら

 Sub test()
    Dim c As Long
    c = ActiveCell.Column
    With Cells(5, c).Resize(58, 1)
        .Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[集計先.xlsx]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
        .Value = .Value
    End With

    With Cells(65, c).Resize(66, 1)
        .Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[集計先.xlsx]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
        .Value = .Value
    End With
 End Sub
こんなのかなと思って行ったら、更に65行目から沢山張り付いたので…
どうやるのかなって思って．．．
質問しました。
（ぺんぎん） 2022/03/04(金) 14:05

Cells(65, c).Resize(66, 1)
の意味はわかりますか？
（γ） 2022/03/04(金) 14:16

＞同じ様にＦ列などの指定ない状態で張り付けられたら別に良いのですが…
F63：63行の最終列　の Formulaプロパティの値 = F63の式
F64：64行の最終列　の Formulaプロパティの値 = F64の式
としておけばよいのではないですか？
（上記は実際のコードではなく、考え方を書いたものです）

各列ごとに集計行が違うとかであれば、前提をきちんと書いてもらわないと
回答できません。

質問が広がっていっていますが、最初の質問にはお答えしましたので、
ここまでとさせてください。

（γ） 2022/03/04(金) 14:27

ありがとうございました。
少し悩みます。
（ぺんぎん） 2022/03/04(金) 14:35

＞=F65-F5-F7-F9-F11-F13-F15-F17-F19-F21-F23-F25-F29-F31-F33-F35-F37-F41-F43-F45-F47-F49-F51-F53-F55-F57-F59-F61

F27とF39は、なくていいのですか？
（わからん） 2022/03/04(金) 14:41

ここは無くて大丈夫です！
ありがとうございます。
（ぺんぎん） 2022/03/14(月) 17:12

■１
いま詰まってる部分がよくわかりませんが、↓は理解されてるのですよね？

 「MOD(ROW(),2)」←が0になれば偶数行、1になれば奇数行

となると

 =F65-F5-F7-F9-F11-F13-F15-F17-F19-F21-F23-F25-F29-F31-F33-F35-F37-F41-F43-F45-F47-F49-F51-F53-F55-F57-F59-F61
 =F66-F6-F8-F10-F12-F14-F16-F18-F20-F22-F24-F26-F30-F32-F34-F36-F38-F42-F44-F46-F48-F50-F52-F54-F56-F58-F60-F62
          ↓

 =F65～F66のうち奇数行　-　（F5～F26の奇数行　+　F29～F38の奇数行　+　F41～F62の奇数行）
 =F65～F66のうち偶数行　-　（F5～F26の偶数行　+　F29～F38の偶数行　+　F41～F62の偶数行）

という表現でもよいような気がします。

■２
さらに、行の関係が決め打ちだというならVBAっぽくループ処理を使って↓のようにしてもよさそうにおもいます。

    Sub ループ処理()
        Dim 行 As Long
        Dim myRNG As Range

        Set myRNG = Range("A5") '←どの列でもよい
        For 行 = 7 To 61 Step 2
            Select Case 行
                Case 27, 39
                    '何もしない
                Case Else
                    Set myRNG = Union(myRNG, Cells(行, "A")) '←どの列でもよい
            End Select
        Next 行

        With Range("F63") '←ここを好きな列に変える
            .Formula = "=" & .Offset(2).Address(0, 0) & "-SUM(" & Intersect(myRNG.EntireRow, .EntireColumn).Address(0, 0) & ")"
            .Copy .Offset(1)
        End With
    End Sub

（もこな２） 2022/03/14(月) 19:30

＞ここは無くて大丈夫です！
あるほうが、楽なのですが...

まあ、こんな感じでしょうか。

 Sub test3()
    c = ActiveCell.Column
    s = "=R[2]C-R[-58]C-R[-56]C-R[-54]C-R[-52]C-R[-50]C-R[-48]C-R[-46]C-R[-44]C-R[-42]C-R[-40]C-R[-38]C-R[-34]C-R[-32]C-R[-30]C-R[-28]C-R[-26]C-R[-22]C-R[-20]C-R[-18]C-R[-16]C-R[-14]C-R[-12]C-R[-10]C-R[-8]C-R[-6]C-R[-4]C-R[-2]C"
    Cells(63, c).Resize(2, 1).FormulaR1C1 = s
 End Sub

ご参考。
https://www.google.com/search?q=VBA+R1C1%E5%BD%A2%E5%BC%8F

（わからん） 2022/03/15(火) 09:05

コメント返信：

[ 一覧(最新更新順) ]

[[20220303142521]] 『マクロを使用した関数の挿入を行いたい』（ぺんぎん） ページの最後に飛ぶ

[[20220303142521]]　『マクロを使用した関数の挿入を行いたい』（ぺんぎん）　ページの最後に飛ぶ