Excel (VBA)

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

 
(Windows 7 Professional : Excel 2016)
結合されたセルの値を取得したい
投稿日時: 19/12/06 15:50:52
投稿者: namonamo20

下記のような構文でOffsetでの移動先が2つの列が結合されたセルの場合にセルの値を取得しません。
(Empty値となる)
どのように修正したらよいでしょうか?
分かる方がいましたらお教えください。
 
WS1.Range("E" & i).Value = WS2.Range("D" & r).Offset(1, 工程NO).Value
【説明】
ワークシートWS2のセルにある値"WS2.Range("D" & r).Offset(1, 工程NO)."を
ワークシートWS1の"("E" & i)"に貼り付け
Offset(1, 工程NO)で指定されたセルは2列x1行で結合されています。
 
よろしくお願いします。
 
以上

回答
投稿日時: 19/12/06 16:30:01
投稿者: sk

引用:
下記のような構文でOffsetでの移動先が2つの列が結合されたセルの場合に
セルの値を取得しません。(Empty値となる)

結合されたセル範囲において、値が格納されるのは
その範囲の左上のセルだけです。
 
したがって、Offset プロパティによって参照されるセルが
「結合されたセル範囲に含まれ、かつ左上のセルではない」
のであれば、そういう結果となるのは当然です。
 
引用:
どのように修正したらよいでしょうか?

(どういう意図でセルを結合したのかが不明ですが)
具体的にどのような値を取得したいのか次第でしょう。

回答
投稿日時: 19/12/06 16:57:08
投稿者: mattuwan44

んと、
 
「D2:E2」「F2:G2」「H2:I2」のセルがそれぞれ結合されている場合、
 
イミディエイトウィンドウで、
 
?range("D2").Offset(,2).address[Enter]
([Enter]キーを押下の意)
 
と、入れたら
 
$G$2
 
と返ってきます。
 
結合されたセルの場合、左上以外のセルは無効になっていますので、値などを保持できません。
なので、Emptyとなります。
 
で、実際にはどのセルを参照したかったのでしょう?
結合セルが存在するときに、Offsetプロパティを使用すると妙な挙動をします。
(エクセル君的にはそれが当たり前だろうけど、人間的には、想定した動きと違うように感じる)
なので、かなり注意が必要です。
 
軽く実験したところ、
 
?range("D2").MergeArea.Offset(,2).Address
$G$2
 
?range("D2").Offset(,1).address
$F$2
 
?range("D2").Offset(,2).MergeArea.Cells(1).address
 $F$2
 
このような結果になりました。
 
なので、実験をいくつかして、エクセル君の癖を掴んでコードを微調整する必要があるかなと思います。
 
まぁ、普通はそういう紛れがある書き方は敬遠して、
別の紛れの無い書き方をするとは思いますが、
コードのごく一部だけを提示されても、
namonamo20の意図が分かりませんので、
日本語でどうしたいか説明されれば、アドバイスが貰えると思います。

回答
投稿日時: 19/12/06 17:00:18
投稿者: mattuwan44

訂正
 
>namonamo20の意図が分かりませんので、
namonamo20さんの意図が分かりませんので、
 
大変失礼しましたm(_ _)m

回答
投稿日時: 19/12/06 17:03:35
投稿者: WinArrow
投稿者のウェブサイトに移動

おそらく、結合セルの次のセルを参照したいということだと推察します。
 
例えば
セルC2〜E2まで結合したセルがあると仮定して
↓のコードを試してみてください。
 
Debug.Print Range("C2").Offset(0,1).Address
Debug.Print Range("C2").MergeArea.Offset(0,1).Adress
 

投稿日時: 19/12/06 18:32:59
投稿者: namonamo20

返信ありがとうございます。
列2x行1の結合セルであれば左側のセルを指定すればよいということでしょうか?
取得したい値は入力されている数量です。
よろしくお願いします。
 
以上
 
 

sk さんの引用:
引用:
下記のような構文でOffsetでの移動先が2つの列が結合されたセルの場合に
セルの値を取得しません。(Empty値となる)

結合されたセル範囲において、値が格納されるのは
その範囲の左上のセルだけです。
 
したがって、Offset プロパティによって参照されるセルが
「結合されたセル範囲に含まれ、かつ左上のセルではない」
のであれば、そういう結果となるのは当然です。
 
引用:
どのように修正したらよいでしょうか?

(どういう意図でセルを結合したのかが不明ですが)
具体的にどのような値を取得したいのか次第でしょう。

投稿日時: 19/12/06 18:40:41
投稿者: namonamo20

返信ありがとうございます。
品番が入力されたセルを起点に数量が入力された列2x行1の結合セルに移動して、
別のシートのセルに転記したいです。
 
よろしくお願いします。
 
 

mattuwan44 さんの引用:
んと、
 
「D2:E2」「F2:G2」「H2:I2」のセルがそれぞれ結合されている場合、
 
イミディエイトウィンドウで、
 
?range("D2").Offset(,2).address[Enter]
([Enter]キーを押下の意)
 
と、入れたら
 
$G$2
 
と返ってきます。
 
結合されたセルの場合、左上以外のセルは無効になっていますので、値などを保持できません。
なので、Emptyとなります。
 
で、実際にはどのセルを参照したかったのでしょう?
結合セルが存在するときに、Offsetプロパティを使用すると妙な挙動をします。
(エクセル君的にはそれが当たり前だろうけど、人間的には、想定した動きと違うように感じる)
なので、かなり注意が必要です。
 
軽く実験したところ、
 
?range("D2").MergeArea.Offset(,2).Address
$G$2
 
?range("D2").Offset(,1).address
$F$2
 
?range("D2").Offset(,2).MergeArea.Cells(1).address
 $F$2
 
このような結果になりました。
 
なので、実験をいくつかして、エクセル君の癖を掴んでコードを微調整する必要があるかなと思います。
 
まぁ、普通はそういう紛れがある書き方は敬遠して、
別の紛れの無い書き方をするとは思いますが、
コードのごく一部だけを提示されても、
namonamo20の意図が分かりませんので、
日本語でどうしたいか説明されれば、アドバイスが貰えると思います。

投稿日時: 19/12/06 18:44:23
投稿者: namonamo20

返信ありがとうございます。
質問の意図は結合セルに入力されている数量を取り出したいです。
そしてそれを別シートのセルに転記するということです。
 
よろしくお願いします。
 
 

WinArrow さんの引用:
おそらく、結合セルの次のセルを参照したいということだと推察します。
 
例えば
セルC2〜E2まで結合したセルがあると仮定して
↓のコードを試してみてください。
 
Debug.Print Range("C2").Offset(0,1).Address
Debug.Print Range("C2").MergeArea.Offset(0,1).Adress
 

回答
投稿日時: 19/12/06 20:24:54
投稿者: simple

<<Sheet2>>の「D2:E2」「F2:G2」「H2:I2」のセルがそれぞれ結合されているとします。
             以下同様に1×2の大きさのセルが下に並んでいるとします。

<<Sheet2>>
      D    E    F    G    H    I
1行   
2     "a"       "b"       "c"
3     "aa"      "bb"      "cc"    
4     "X"       "Y"       "Z"  
5      
6

(質問)
この、どのセルの値を,Sheet1に転記したいのですか?
それを説明して下さい。

回答
投稿日時: 19/12/06 21:15:29
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
返信ありがとうございます。
 品番が入力されたセルを起点に数量が入力された列2x行1の結合セルに移動して、
 別のシートのセルに転記したいです。

> 品番が入力されたセルを起点に数量が入力された列2x行1の結合セル
を表のレイアウトで説明してください。

回答
投稿日時: 19/12/06 22:40:43
投稿者: 半平太

全て、2列結合で決まっているなら、Cellsプロパティ(Itemプロパティ)を使った方が簡便です。
※OFFSETとセル結合は相性が悪いです(やってやれないことはないですけど)
  
WS1.Range("E" & i).Value = WS2.Range("D" & r).Cells(2, 工程NO * 2 +1 ).Value
  
※Itemプロパティは、スタートのセル位置が(1,1)であることに留意
 厳密に言うと 「Range("D" & r)」と 「Range("D" & r).Cells」 は全く同じものであり、
 「.Cells」は無意味な記述なので、書かない人もいます。

回答
投稿日時: 19/12/07 10:11:35
投稿者: WinArrow
投稿者のウェブサイトに移動

振り出しに戻って申し訳ありませんが
  

引用:
WS1.Range("E" & i).Value = WS2.Range("D" & r).Offset(1, 工程NO).Value
【説明】
ワークシートWS2のセルにある値"WS2.Range("D" & r).Offset(1, 工程NO)."を
 ワークシートWS1の"("E" & i)"に貼り付け
Offset(1, 工程NO)で指定されたセルは2列x1行で結合されています。

ここの説明では、
仮に「r=2」&[工程No=[1だとした場合
WS2.Range("D2").Offset(1,1)
セルE3の値を取得することになりますが、
これであっていますか?
 
 

回答
投稿日時: 19/12/07 12:05:39
投稿者: simple

<<Sheet2>>の「D2:E2」「F2:G2」「H2:I2」のセルがそれぞれ結合されているとします。
             以下同様に1×2の大きさの結合セルが並んでいるとします。

      D    E    F    G    H    I
1行   
2     1         2         3
3     4         5         6    
4     7         8         9  
5      

既に半平太さんから指摘がありましたように、Itemプロパティを使って下さい。
 
■Offsetを使った手法は上手くいかない
Sub test1()
    Dim i As Long
    Dim j As Long
    
    For i = 0 To 2
        For j = 0 To 2
            Debug.Print i, j, Range("D2").Offset(i, j).Address
        Next
    Next
End Sub

【結果】
 0             0            $D$2
 0             1            $F$2
 0             2            $G$2
 1             0            $D$3
 1             1            $F$3
 1             2            $G$3
 2             0            $D$4
 2             1            $F$4
 2             2            $G$4

■こうして下さい
Sub test2()
    Dim i As Long
    Dim j As Long
    
    For i = 1 To 3
        For j = 1 To 3
            Debug.Print i, j, [D2].Cells(i, 1 + 2 * (j - 1)).Address
        Next
    Next
End Sub

【結果】
 1             1            $D$2
 1             2            $F$2
 1             3            $H$2
 2             1            $D$3
 2             2            $F$3
 2             3            $H$3
 3             1            $D$4
 3             2            $F$4
 3             3            $H$4 

投稿日時: 19/12/09 15:25:28
投稿者: namonamo20

皆様いろいろ回答ありがとうございます。
 
私の説明不足でイマイチ伝わっていませんので改めて記載します。
 
◆WS2
 A B C  D    E  F     G   H     I  J
1    工 程     自動盤    仕上加工1     アルマイト    
2     場 所     自動盤一課    加工二課    (株)AAA    
3    納入数        100        100        100    
4    在庫数     0    927    0    398     0     0
※2,3行目が2列で結合されている(EF,GH,IJ)
※A列に品番が入ります。
 
◆WS1
A    B    C       D     E   F  G    H     I     J
購入先    得意先名 品番        用途      当月 次月 次々月    部署      係       工程No
ABC    XX     235164-0051    シャフトローター            第一製造課 第二   2
DEF    XX     JGF28-000061    シャフトEPSモーター            本社製造課 1係       1
zzz    XX     133036-5009    5000SVφ6.0            第二製造課 3係       3
yyy    DN     133036-5009    5000SVφ2.6            第二製造課 3係       3
 
【やりたいこと】
・WS1のE列にWS2の該当の品番の納入数を入れたい
・WS1の工程NOからWS2のどの工程の納入数か判断する(WS1にはいくつめの工程かの数字が入っている)
【作成したマクロ】
WS1.Range("E" & i).Value = WS2.Range("D" & r).Offset(1, 工程NO).Value
【直したいところ】
右辺にて結合された3行目の数字を取得しないので取得してWS1のE列へ転記したい
※工程NOが1の時は取得できている
 
分かる方よろしくお願いします。

回答
投稿日時: 19/12/09 15:51:21
投稿者: WinArrow
投稿者のウェブサイトに移動

具体的な表を掲示して貰ったが、
 
説明のコードとの対応がさっぱりわかりません。
 
>WS2.Range("D" & r)
は、表ではどこを指しているのでしょうか?
 

回答
投稿日時: 19/12/09 16:09:23
投稿者: sk

引用:
列2x行1の結合セルであれば左側のセルを指定すればよいということでしょうか?

基本的にはそういうことです。
 
引用:
※2,3行目が2列で結合されている(EF,GH,IJ)

引用:
【やりたいこと】
・WS1のE列にWS2の該当の品番の納入数を入れたい
・WS1の工程NOからWS2のどの工程の納入数か判断する(WS1にはいくつめの工程かの数字が入っている)

引用:
※工程NOが1の時は取得できている

引用:
WS1.Range("E" & i).Value = WS2.Range("D" & r).Offset(1, 工程NO).Value

WS1 の[工程NO]の値が 1 である場合は、WS2 の E 列( D 列の 1 つ右側)の、
WS1 の[工程NO]の値が 2 である場合は、WS2 の G 列( D 列の 3 つ右側)の、
WS1 の[工程NO]の値が 3 である場合は、WS2 の I 列( D 列の 5 つ右側)の
3 行目のセルの値を取得して、WS1 の E 列([当月])のいずれかのセルに代入したい、
ということであれば、そもそも[工程NO]の値をそのまま列方向のオフセット値として
使用しているのが原因でしょう。
 
[工程NO]の値に 2 を掛けて、更に 1 を引いた結果を渡せばよいはず。

回答
投稿日時: 19/12/09 16:27:32
投稿者: simple

他人の発言を読まれているのでしょうか?
 
私も例示までしてますが、
半平太さんも指摘されているように、
・Offsetは使わない
・結合セルの左上端をさすセル位置をItemプロパティ(Cells(m,n)方式)で指定する
ということさえ理解してもらえば、
あとは自力で対応できないはずがないと思います。

回答
投稿日時: 19/12/10 10:12:49
投稿者: WinArrow
投稿者のウェブサイトに移動

質問者さんは
 

引用:
>WS1.Range("E" & i).Value = WS2.Range("D" & r).Offset(1, 工程NO).Value

に固執されていますが、
  
説明された表のレイアウトでは
>WS1.Range("E" & i).Value = WS2.Range("D" & r).Offset(1, 工程NO).Value
のコードの中のセルDがどこなのかわかりません。
つまり、
>WS2.Range("D" & r)
の場所が判断できないのです。
  
解決策
(1)OFFSETに固執
>WS2.Range("D" & r).Offset(1, 工程NO).Value

Ws2.Range("D2").Offset(1, 工程NO * 2 - 1).Value
に変更する
 
(2)工程NOを使わない
Ws1の「部署」でWS2の「場所」を行方向に検索して列番号を取得→ 列
WS2.Cells(WS2.Cells(r, 列).Offset(1).Value
 
(3)工程NOを列番号に変換する
  列 = 工程NO * 2 -1 + 4
WS2.Cells(r, 列).Offset(1).Value

投稿日時: 19/12/11 10:50:23
投稿者: namonamo20

皆様
いろいろご意見いただきありがとうございました。
おかげさまで解決できました。