Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
参照元と参照先を反対にすることってできますか
投稿日時: 19/09/24 15:33:46
投稿者: 京すけ

「Sheet1のA5」のセルに、「=Sheet2!$A$2」という参照する関数
を入れています。(この他にもたくさん参照はあるのですが)
 
これを反対にして、「Sheet2のA2」のセルに、「=Sheet1!$A$5」と
いうように、参照先と参照元の方向を反対にすることはできますでしょうか?
一つひとつ変えるには、とても大変なので、ご助言いただけないでしょうか。
お願い致します。

回答
投稿日時: 19/09/24 17:47:20
投稿者: よろずや

京すけ さんの引用:
「Sheet1のA5」のセルに、「=Sheet2!$A$2」という参照する関数
を入れています。(この他にもたくさん参照はあるのですが)
 
これを反対にして、「Sheet2のA2」のセルに、「=Sheet1!$A$5」と
いうように、参照先と参照元の方向を反対にすることはできますでしょうか?

「Sheet1のA5」のセルを、「=Sheet2!$A$2」にしたまま
「Sheet2のA2」のセルに、「=Sheet1!$A$5」といれると、
循環参照エラーになります。
「Sheet1のA5」のセルには、何を入れましょうか?
「Sheet2のA2」のセルに入ってたものを入れましょうか?

回答
投稿日時: 19/09/24 18:34:55
投稿者: simple

>「Sheet1のA5」のセルに、「=Sheet2!$A$2」という参照する関数
これにさらに、
「Sheet1のA105」のセルでも、「=Sheet2!$A$2」としていたら、どうするんですか?
つまり、n : 1 の関係になる場合ですねえ。
こういうの結構ありそうですが。

回答
投稿日時: 19/09/24 20:01:30
投稿者: WinArrow
投稿者のウェブサイトに移動

循環参照させないためのサンプルコード
   
Sub sample()
  Dim SHIKI As String, STNM As String, CELL As String
  Dim NEWSHIKI As String
     
      SHIKI = Sheets("Sheet1").Range("A5").Formula
      STNM = Left$(Mid$(SHIKI, 2), InStr(Mid$(SHIKI, 2), "!") - 1)
      CELL = Mid$(SHIKI, InStr(SHIKI, "!") + 1)
      Sheets(1).Range("A5").ClearContents
      With Sheets("Sheet1")
          NEWSHIKI = Replace(SHIKI, STNM, .Name)
          With .Range("A5")
              NEWSHIKI = Replace(NEWSHIKI, CELL, .Address)
          End With
      End With
      Sheets(STNM).Range(CELL).Formula = NEWSHIKI
  End Sub

回答
投稿日時: 19/09/24 21:47:48
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
説明の範囲内でのコードですので
simpleさんが懸念している n : 1 の関係にあるならば、
貴方が組込む際に、考慮してください。

投稿日時: 19/09/24 23:25:26
投稿者: 京すけ

みなさん、ありがとうございます。今、パソコンから離れた場所にいまして
携帯でみています。このため、返信が大変、遅くなり申し訳ありませんでした。
また、説明が不十分で、ご迷惑をおかけしたと反省しております。
 
先の事については、
sheet1が、入力シートでありsheet2が出力シートでした、
ですので、sheet1のA5に入力すると、その出力シートであるsheet2の
A2に入力した値が、出力されるというものでした。
 
これを、レイアウトはそのままで、入力シートと出力シートを反対に
したいと考えたものです。
 
ですので、今まで、sheet1のA5が、sheet2のA2に出ていたとすると
sheet2のA2に入れたものが、sheet1のA5に出力されてほしいと
考えています。
 
ですので1:1対応であると考えています。
 
ご提示いただいたコードがどういう動きをしてくれるのかパソコン
がないので確かめることができませんが、明日、場所を変えて
確認させていただきます。
返信が遅くなりますことお許しください。

投稿日時: 19/09/25 11:50:48
投稿者: 京すけ

やっと、確かめることができました。ご提示いただいたコードを
以下のようにして列(A,B,C,・・・列)ごとに変換していくことに成功
いたしました。
ありがとうございました。
 
ただ、出力されるところが、元が$がついていない相対でも
出力されるのに$がつくのはなぜなんでしょうか。
ここだけが、わからない状態となりました。
 
Sub sample()
  Dim SHIKI As String, STNM As String, CELL As String
  Dim NEWSHIKI As String
  Dim i As Long
   
  For i = 2 To 81
      SHIKI = Sheets("入力").Range("B" & i).Formula
      STNM = Left$(Mid$(SHIKI, 2), InStr(Mid$(SHIKI, 2), "!") - 1)
      CELL = Mid$(SHIKI, InStr(SHIKI, "!") + 1)
      Sheets("入力").Range("B" & i).ClearContents
      With Sheets("入力")
          NEWSHIKI = Replace(SHIKI, STNM, .Name)
          With .Range("B" & i)
              NEWSHIKI = Replace(NEWSHIKI, CELL, .Address)
          End With
      End With
      Sheets(STNM).Range(CELL).Formula = NEWSHIKI
       
  Next i
   
  End Sub

回答
投稿日時: 19/09/25 14:15:35
投稿者: WinArrow
投稿者のウェブサイトに移動

京すけ さんの引用:

ただ、出力されるところが、元が$がついていない相対でも
出力されるのに$がつくのはなぜなんでしょうか。
ここだけが、わからない状態となりました。

.Address
というプロパティがあります。
  
 .Address() :絶対アドレス
.Address(0,1) : 列が絶対アドレス
.Address(1,0) : 行が絶対アドレス
.Address(0,0) : 行・列共に、相対アドレス
  
どのパターンを使うかは
  
CELL文字列のパターンを判別する必要があります。
 

投稿日時: 19/09/25 16:49:29
投稿者: 京すけ

ありがとうございます。
ご教示いただいた方法で、思い通りの動きとなりました。
本当にありがとうございました。