[[20230127093701]]　『セルに入っている関数の数字をマクロで変更』（イチゴパフェ）　ページの最後に飛ぶ

『セルに入っている関数の数字をマクロで変更』（イチゴパフェ）

Excelのマクロを使用して

B1に「=A1」という関数が入っていて
マクロを実行したら「=A3」にする
次に実行したら「=A5」次に実行したら「=A7」という風に関数を2行ずつ繰り上げる事はできないでしょうか？

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

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

 Sub test()
     If Range("D1") = "" Then Range("D1") = 3
     Range("B1").Formula = "=A" & Range("D1")
     Range("D1") = Range("D1") + 2
 End Sub
（フォーキー） 2023/01/27(金) 10:24:29

 With ActiveCell
     .Value = "=" & .Precedents(3).Address(0, 0)
 End With

（こんな感じかな） 2023/01/27(金) 10:29:53

既にレスが付いてますが、そのままアップします。

いろんな書き方があると思いますが、一例です。

Sub test()

 Dim rng As Range
 Dim rwnum As Integer
 Const colstr As String = "A"
 Dim myformula As String
  Set rng = ActiveSheet.Range("B1")
  myformula = Replace(rng.Formula, "=", "")
  rwnum = Replace(myformula, colstr, "") * 1
  rwnum = rwnum + 2
  rng.Formula = "=" & colstr & rwnum
  Set rng = Nothing
End Sub

（ＭＫ） 2023/01/27(金) 10:30:38

フォーキーさんありがとうございます。
これは自分でも理解できる感じで助かります！

こんな感じかなさん、ありがとうございます。
これはどうやって使うんですか？

ＭＫさん、ありがとうございます。
他のセルを使わないでできてるので凄いですね…
ただ、どういう仕組みか理解でできない…
よろしければ解説お願いできないでしょうか？
（イチゴパフェ） 2023/01/27(金) 10:58:57

 私のコードは非常に回りくどい方法をとってます。

Sub test()

 Dim rng As Range
 Dim rwnum As Integer

 'B1の数式の列を定数で指定(今回はＡ)
 Const colstr As String = "A"

 Dim myformula As String

  '変数rngにアクティブシートのB1セルをセット
  Set rng = ActiveSheet.Range("B1")

  '変数rngに格納したセルの数式の＝を除いた部分を取得しmyformulaに代入
  myformula = Replace(rng.Formula, "=", "")

  'myformulaに代入した文字列から定数colstrに格納した文字列(今回はA)を取り除いた部分
  '(行番号)をrwnumに格納(*1は念のため/1をかけることによって数字から数値に変換)
  rwnum = Replace(myformula, colstr, "") * 1

  '取得した行番号に2を加算
  rwnum = rwnum + 2

  'rngに数式を入力/”＝”と列番号(A)と行番号を結合した文字列
  rng.Formula = "=" & colstr & rwnum

  '変数rngの解放
  Set rng = Nothing
End Sub
（ＭＫ） 2023/01/27(金) 11:35:35

 こんな感じかなさんのコードは変えたい数式が
 入っているセル(今回はB1)をアクティブにして
 おいて実行します。
（ＭＫ） 2023/01/27(金) 11:39:24

同一シートのみ、すべて相対参照ならこれも

 Sub Sample()
    Dim dic As Object
    Dim rng As Range
    Dim str As String
    Dim key
    Set dic = CreateObject("Scripting.Dictionary")

    With ActiveCell
        If .Precedents Is Nothing Then Exit Sub
        For Each rng In .Precedents.Areas
            dic(rng.Address(0, 0)) = rng.Offset(2).Address(0, 0)
        Next
        str = .Formula
        For Each key In dic
            str = Replace(str, key, dic(key))
        Next
        .Value = str
    End With
 End Sub

（こんな感じかな） 2023/01/27(金) 12:06:32

既に話がすすんでますが、ちょっと確認。

本当に↓なんですか？
B1に「=A1」という関数が入っていて

もしもそういうことであれば、

 (1) 数式から"="を除いて【セル番地】を取り出す
 (2) セル番地から２行さがった【セル番地】を調べる
 (3) B1セルに(2)のセル番地のセルを参照する数式を書き込む

ということだけ考えればよくなります。
なので、例示のとおりであれば↓のようなことでもＯＫだとおもいます。

    Sub まくろ()
        Dim セル番地 As String

        セル番地 = Replace(Range("B1").Formula, "=", "") '(数式から=を取り除いて)【セル番地】を取り出す
        Range("B1").Formula = "=" & Range(セル番地).Offset(2, 0).Address(False, False) 'B1セルに【セル番地】が示すセルから2行下がったセルを参照する数式を書き込む
    End Sub

（もこな２） 2023/01/27(金) 12:20:10

ＭＫさん解説ありがとうございます。
ただ、見慣れない言葉が多すぎて理解する事がまだできなさそうです。
せっかく解説していただいたのにすみません。
今回頂いたのは、メモしておいて理解できるようになったら試してみます。
結構シンプルな事をしたかったんですがマクロで行うと結構大変ですね…
でも出来て良かったです！

こんな感じかなさんありがとうございます。
まだ教科書でたどり着いてないエリアのものなので
メモして後に生かす様にします。
結構シンプルな事をしたかったんですがマクロで行うと結構大変なんですね…
出来て良かったです！

もこな２さん、ありがとうございます。
凄いシンプルですね…
このAddress(False, False)これは何なのでしょうか？
セルの住所(番地)を調べるコマンドですか？

FormulaってのはFormulaやFormulaR1C1などありますが
どういう違いなのでしょうか？
以上よろしくお願いします。
（イチゴパフェ） 2023/01/27(金) 13:54:45

■１
＞このAddress(False, False)これは何なのでしょうか？

 Rangeオブジェクト.Address(False, False)

↑を詳しく書くと↓のようになります。

 Rangeオブジェクト.Address(RowAbsolute:=False, ColumnAbsolute:=False)

[[20230113144923]]でも述べましたが、わからない命令などがあればまずはネット検索してみてください。
たとえば、「VBA　Address」というキーワードで検索すると↓のようなサイトがヒットすると思います。

 【参考】
https://www.moug.net/tech/exvba/0050094.html
https://excel-ubara.com/excelvba1/EXCELVBA384.html

まずは、そういったサイトを読んでみてそれでもわからなければ再度聞いてください。
（「セルの住所(番地)を調べるコマンドですか？」といっているので概ねアタリはついていると思いますが）

■２
＞FormulaってのはFormulaやFormulaR1C1などありますがどういう違いなのでしょうか？
こちらも、まずはネット検索してみましょう。

 【参考】
https://www.moug.net/tech/exvba/0050098.html
https://excel-ubara.com/excelvba1/EXCELVBA338.html

（もこな２） 2023/01/27(金) 16:16:30

もこな２さんすみません。
ありがとうございます。
頂いたurl確認致します。
（イチゴパフェ） 2023/01/27(金) 16:27:04

コメント返信：

[ 一覧(最新更新順) ]

[[20230127093701]] 『セルに入っている関数の数字をマクロで変更』（イチゴパフェ） ページの最後に飛ぶ

[[20230127093701]]　『セルに入っている関数の数字をマクロで変更』（イチゴパフェ）　ページの最後に飛ぶ