[[20200922004300]]　『列全体に含まれている値とセルに入力されている値』（眠神）　ページの最後に飛ぶ

『列全体に含まれている値とセルに入力されている値の完全一致』（眠神）

失礼致します。　
分からない所があったので、お聞き致しました。　
例えば、セルA2に入力されている値がB列全体の何処かに入力されていた場合、
完全に等しかった場合の時、If文で比較したいのですが、例えばB列が4行目から50行目まで、値が入力されている場合、(空白あり)
If worksheets("Sheet1"). Range("B4","B50") . Range＝ worksheets("Sheet1").Cells(2,1).Value Then
msgbox " 既に入力されています"
といった内容を書きたいのですが、
B列という事で、複数のセルを検索しなければならないのですが、これって出来るんでしょうか？　やはりRangeはIf文の比較に使えるのでしょうか？

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

 こういったことでしょうか。

 Sub Test()
    With Worksheets("Sheet1")
        If WorksheetFunction.CountIf(.Range("B4:B50"), .Range("A2").Value) > 0 Then
            MsgBox " 既に入力されています"
        End If
    End With
 End Sub
（QS） 2020/09/22(火) 01:45

なるほど。この場合はworksheet関数になるんですね。　
有りがとうございます。🙇♂
（眠神） 2020/09/22(火) 02:27

答えたところでいつものように放置されそうだけど一応・・

   Sub 別案()
      Dim MyRNG As Range

      With Worksheets("Sheet1")
         Set MyRNG = .Range("B4", "B50").Find(What:=.Cells(2, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)

         If MyRNG Is Nothing Then
            MsgBox "ないよ"
         Else
            MsgBox "あるよ"
         End If
   End Sub

（もこな２） 2020/09/22(火) 10:23

有りがとうございます。　
すいません💦
ちなみに Rangeで宣言したMyRNGをmsgboxで使いたい場合、　set MyRNG ＝ ActiveWorkBook.WorkSheets("Sheet1"). Range("B4","B50")
を、　

MsgBox ( "既に" & MyRNG ＆ "が入力されてました)
のような感じにしたいんですけど、必ずMsgBoxの所でエラーになってしまうのですが、これはどうしてでしょうか？
（眠神） 2020/09/22(火) 13:34

＞これはどうしてでしょうか？
とりあえず、エラーメッセージをよく読んで考えてみては？

また、見つけたセル番地を表示したいなら、ValueプロパティでなくAddressプロパティでしょうし、検索値なら、A2セルの値でよいでしょう。

さらに、返り値を使わないなら↓のように括弧は要りません。

 MsgBox "既に" & Cells(2, 1).Value ＆ "が入力されてました"

 ※最後の「"」はこちらで補完しました。

（もこな２） 2020/09/22(火) 14:12

有りがとうございます。　
msgboxの中身を変えてみます。　
いつも有りがとうございます
（眠神） 2020/09/22(火) 14:40

＞いつも有りがとうございます
とってつけたような言葉はいらないので、回答者からのアドバイスについてどのように考えて（理解して）、最終的にどのような結論にしたのかまで返答されるとよいとおもいます。

 ※質問者さんに対する印象は「放置の常連」といったところなので、ちょっと厳しめに書いてます。

（もこな２） 2020/09/22(火) 16:22

　
msgboxの中身を、 Range("B4,B50") から、Cells(2,1).Valueに変えたらエラーはなくなりました。　
ちなみにこの内容は前からのfindメソッドの問題だったのでやっと解決致しました。　
有りがとうございます。
（眠神） 2020/09/22(火) 16:27

＞Range("B4,B50") から、Cells(2,1).Valueに変えたら
＞この内容は前からのfindメソッドの問題

違いますね。タイプミスで無いとすれば

 Range("B4,B50")
     ↓
 Range("B4,B50").Value
 B4とB50という【２つのセル】の【値】となっています。

おそらく、ここがエクセル君で解釈できずに結果として、１つ目のB4セルの値が出力されたのでしょう。findメソッドは全く関係ありません。
また、タイプミスであれば

 Range("B4","B50")
     ↓
 Range("B4:B50").Value
 B4～B50という【セル範囲】の【値】となり、2次元配列が取得されます。

そして↓のように、MsgBoxで2次元配列を表示しようとしたから、型が合わないというエラーになったのでしょう。やはり、findメソッドは全く関係ありません。

 MsgBox Range("B4", "B50").Value

このほか、反応を見る限り、 2020/09/22(火) 10:23に提示したコードは試していないとおもいますが、間違いがありましたので訂正しておきます。

    Sub 別案()
        Dim MyRNG As Range
        With Worksheets("Sheet1")
            Set MyRNG = .Range("B4", "B50").Find(What:=.Cells(2, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
            If MyRNG Is Nothing Then
                MsgBox "ないよ"
            Else
                MsgBox "あるよ"
            End If
        End With '←これを追加
   End Sub

（もこな２） 2020/09/22(火) 17:21

　　〉そして↓のように、MsgBoxで2次元配列を表示しようとしたから、型が合わないというエラーになったのでしょう。やはり、findメソッドは全く関係ありません。

　　ではmsgboxで、範囲指定したもの　"B4"から"B50" までの範囲.、2次元配列を格納させたものを　
　　上手くmsgboxとして、表示させるのには、型を何に変換したらいいのでしょうか？
（眠神） 2020/09/22(火) 19:48

＞上手くmsgboxとして、表示させるのには、型を何に変換したらいいのでしょうか？
逆に、どのように表示されるのが希望なのですか？

 (1)Findメソッドで見つかったセル番地を列挙したい
 (2)FindメソッドのWhatに使ったものを提示したい　（←提示済）
 (3)Findメソッドの対象になった範囲のセル番地を表示したい
 (4)B4～B50のセル範囲の値を列挙したい
 (5)その他（　　　　　　）

ちなみに、(1)の場合であって、合致するセルが【複数】ある場合は、検索する部分にもう一工夫必要です。

（もこな２） 2020/09/22(火) 20:23

(1) ですね。　今まで自分は、　

 Dim 検索 As  Range 
検索　＝　ActiveWorkbook.Worksheets(" Sheet1"). Range("B4 : B50")

 If  ThisWorkbook.Worksheets("Sheet1").Cells(2,1).Value ＝ActiveWorkbook.Worksheets(" Sheet1"). Range("B4 : B50")  Then

       msgbox 検索　＆　"がありました"

   という内容を実行させたいです。
（眠神） 2020/09/22(火) 21:30

＞今まで自分は、～
意味が分かりません。
ちなみに、(1)であって、該当するセルが１つしかない（あるいは存在しない）のであれば↓でよいですね。

    Sub 別案_改()
        Dim MyRNG As Range
        With Worksheets("Sheet1")
            Set MyRNG = .Range("B4", "B50").Find(What:=.Cells(2, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
            If MyRNG Is Nothing Then
                MsgBox "ないよ"
            Else
                MsgBox MyRNG.Address(False, False) & "にあるよ"
            End If
        End With
   End Sub

findメソッドの問題ではなく、Findメソッドが1つの【Rangeオブジェクト】を返す（見つからない場合はNothingを返す）ということを理解していなかっただけでしょう。

繰り返しになりますが、該当するセルが【複数】ある場合はもう一工夫必要です。
【参考】
http://officetanaka.net/excel/vba/tips/tips123.htm

（もこな２） 2020/09/22(火) 21:51

意味わかんなくてすいません。
（眠神） 2020/09/22(火) 22:11

＞意味わかんなくてすいません。
繰り返しになりますが、そういった言葉は要らないので回答者からのアドバイスについてどのように考えて（理解して）、どうなった（どうした）のかを返答されるとよいとおもいます。

確認したのは【どうなるのが希望なのか】つまり、メッセージボックスでなんと表示したいのかを聞いているのですから、上手くいかないコード（何故、型が合わないというエラーになるのかは説明済み）を示されても、こちらには伝わりません。

改めて確認しますが、例えば↓のような表（データの検索対象セルがB4～B10）だとして、"どのように表示"されるのが希望なのですか？

   __A______B_________
  1
  2  あ
  3
  4         か
  5         き
  6         く
  7         け
  8         こ
  9         あ
 10         い

【返答例】

 (1)「あ」は既にB8セルに入力されています。
 (2)「あ」が入力されたセルが存在します。　　　　　　　★当初の質問であればこれでいいはず。
 (3) B4:B10の中に「あ」が入力されたセルが存在します。
 (4) 「か、き、く、け、こ、あ、い」のなかに「あ」が存在します。

（もこな２） 2020/09/23(水) 16:35

提示頂いた表だと一番近いのは(2)です。　

（眠神） 2020/09/23(水) 22:28

ほかのトピックのように放置しないだけマシなのかもしれませんが、埒が明かないのでこれで最後にします。（数日待ってみて理解できるレスがなければ、私は撤退します。）

どうなるのが希望なのか（メッセージボックスでなんと表示したいのか）提示してみてください。
近いのは～●●ですとか　エラーで動かないコードの提示は不要です。

おまけで、【例示】の(2)と全く同じでよければ↓や、

    Sub 別案()
        Dim MyRNG As Range
        With Worksheets("Sheet1")
            Set MyRNG = .Range("B4", "B50").Find(What:=.Cells(2, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
            If MyRNG Is Nothing Then
                MsgBox "「" & .Cells(2, 1).Value & "」が入力されたセルはありません。"
            Else
                MsgBox "「" & .Cells(2, 1).Value & "」が入力されたセルが存在します。"
            End If
        End With
    End Sub
   '----------------------------------------------------------------
    Sub Test()’QSさんのコードをMsgboxの内容だけ変更。
       With Worksheets("Sheet1")
           If WorksheetFunction.CountIf(.Range("B4:B10"), .Range("A2").Value) > 0 Then
               MsgBox "「" & .Cells(2, 1).Value & "」が入力されたセルが存在します。"
           Else
               MsgBox "「" & .Cells(2, 1).Value & "」が入力されたセルはありません。"
           End If
       End With
    End Sub

↑でよいでしょう。

また、上記をステップ実行するなどして研究すればわかることでしょうが、MsgboxのところでFindメソッドは何も関係してませんよね？（強いて言えば別案のほうは返り値がNothingであるかを条件分岐に使ってはいますが・・・）
したがって「前からのfindメソッドの問題だったのでやっと解決致しました。」とのことですが、Findメソッドの何が問題だとおもったのか私には理解できません。
無理にとは言いませんが、findメソッドの問題とやらについて説明してもらえませんか？
（今後Findメソッドを説明するときの参考にしたいので）

（もこな２） 2020/09/23(水) 23:41

6/23に質問した内容です。　　
　
『Findメソッド』（眠神）
　大量の住所データから検索したい名前の人を入力して住所や郵便番号を抽出したく、コードを書いてるのですが、どうしても"該当部分がありませんでした"と表示されてしまいます。どなたか御教授頂けると本当に嬉しいです　
ちなみにこんな感じで書いてます　
　sub データの検索()
　Dim fndRng As Range , srcRng As Range

 '検索結果を書き出す"住所Findメソッド"シートのセル範囲"B5:N5"の値をクリア　
　worksheets("住所Findメソッド").Range("B5:N5").clearcontests 
'変数srcRngにワークシート"住所支払"のB3列目(「西暦」)を含む
　Set srcRng = worksheets("住所支払").Range("B3").CurrentRegion.Columns(3)  
　
'検索キーワードはセルF2の値で完全一致検索を行う　
'変数srcRngの範囲を検索し、最初に見つかったセルを変数fndRngに代入　　
　Set fndRng ＝　srcRng.Find(what:=Range("F2").value , lookat:=xlwhole) 
　
'検索値が見つかった場合(fndRngがNothingでない場合) 
 If Not fndRng Is Nothing Then 
'見つかったセルとワークシート"住所支払"のB3:N3の値をワークシート"住所Findメソッド" シートのB5:N5に代入

　cells(5,"B").value = fndRng.value

   cells(5,"C").value = fndRng.Offset(0,1).value 
　cells(5,"D").value = fndRng.Offset(0,2).value  
　cells(5,"E") .value = fndRng.Offset(0,3).value 
　cells(5,"F").value = fndRng.Offset(0,4).value
　cells(5,"G").value = fndRng.Offset(0,5)value 
　cells(5,"H").value = fndRng.Offset(0,6)value    
    cells(5,"I").value = fndRng.Offset(0,7).value 
　cells(5,"J").value = fndRng.Offset(0,8).value  
　cells(5,"K").value = fndRng.Offset(0,9).value  
　cells(5,"L").value = fndRng.Offset(0,10).value  
    cells(5,"M").value = fndRng.Offset(0,11).value  
　cells(5,"N").value =  fndRng.Offset(0,12).value  
　
　'検索値が見つからなかった場合　
Else 
           Msgbox "該当部分がありませんでした" 
End If 
  'セルの参照を削除　
　　Set fndRng = Nothing 
       Set srcRng = Nothing 
 End sub  
　
今回の聞きたい内容とはほぼ違うかも知れませんが要は、過去の内容と今回の内容で共通しているのは、Findメソッドの使い方に　
ついて聞きたいと言う事でした。
　
（眠神） 2020/09/23(水) 23:48

＞6/23に質問した内容です。
あぁ放置されたトピックの一つですね。
長々と転記せずとも、↓のように記事番号を角括弧で括るとリンクにできますよ。
[[20200623174954]]　『Findメソッド』（眠神）

そして
＞今回の聞きたい内容とはほぼ違うかも知れませんが
このトピックの質問の段階では、Findメソッドなんて書いてませんし全く違うでしょう・・・・

＞ちなみにこんな感じで書いてます
ちなみついでに、[[20200623174954]]を隅から隅まで読んでみてはどうでしょうか？
2020/06/23(火) 21:15 に記述ミスの指摘をしてますよ。

＞要は、過去の内容と今回の内容で共通しているのは、Findメソッドの使い方について聞きたいと言う事でした。
う～ん。Findメソッドの使い方がよくわかってなかった（検索値の有無判定にも使えることを知らなかった）ということを言いたかったってことですかね？
にしては、[[20200623174954]]で↓の判定を使っているわけですが・・・
　If Not fndRng Is Nothing Then

さて、【どうなるのが希望なのか（メッセージボックスでなんと表示したいのか）】について理解できる提示はないようなので私は撤退します。

（もこな２） 2020/09/24(木) 05:52

僕が聞きたかったのはA2の値がB列の4行目から50行目の中にあるのかどうか、
一つのセルの比較と列全体のRangeの値の比較ができるのかどうか、　
　Ex) If worksheets.Cells(2,1).Value ＝　worksheets.Range("B4","B50") Then
　　　　　msgbox "B列に" & Cells(2,1).Value & "があります"

という事です。　If文の判定で、Rangeが使えるのかどうかという事に、疑問を持っていました。　　
左辺の方はCellの値が、1個という事もあり、比較がしやすく、右辺の方は複数の値を判定　
する訳なので、1個　＝　複数　の判定をしようとすれば、どうしてもmsgboxの方でエラー　
が起きてしまうという事でした。　　
これについて再度御教授願います。
　

（眠神） 2020/09/24(木) 11:35

二次元配列で格納させようとしたから、msgboxで、エラーに、なる事は分かりました。　
もう一工夫必要になるとおっしゃいましたが、office TANAKAさんのサイトから参考とすると、どのような工夫をしなければ　
ならないのでしょうか？
（眠神） 2020/09/24(木) 11:39

 ＞ 一つのセルの比較と列全体のRangeの値の比較ができるのかどうか、
それが、Findメソッドでは？

これだけ多くの実例コードを動かしているのに、何で範囲と1つのセルをイコールで比較しようとするのやら…。
（???） 2020/09/24(木) 13:47

どうぞ。

    Sub めっせーじぼっくす()
        Dim MSG As String
        Dim WF As Object, obj As Object
        Set WF = WorksheetFunction
        Set obj = CreateObject("ScriptControl"): obj.Language = "JScript"
        Dim 静的配列(1) As String
        Dim i As Long

        With Worksheets.Add
            .Cells(10, "A").Value = "%E2%91%A0%E3%81%A9%E3%81%86%E3%81%AA%E3%82%8C%E3%81%B0%E8%89%AF%E3%81" & _
                                                 "%84%E3%81%AE%E3%81%8B%E8%81%9E%E3%81%84%E3%81%A6%E3%81%84%E3%82%8B%E3" & _
                                                 "%81%8C%E7%AD%94%E3%81%88%E3%81%A6%E3%81%8F%E3%82%8C%E3%81%AA%E3%81%84"
            .Cells(12, "A").Value = "%E2%91%A1%E2%86%91%E3%82%92%E7%AD%94%E3%81%88%E3%81%A6%E3%81%8F%E3%82" & _
                                                 "%8C%E3%81%AA%E3%81%84%E3%81%8B%E3%82%89%E3%80%81%E3%81%A8%E3%81%AB%E3" & _
                                                 "%81%8B%E3%81%8F%E6%9C%89%E7%84%A1%E3%81%8C%E3%82%8F%E3%81%8B%E3%82%8C" & _
                                                 "%E3%81%B0%E3%82%88%E3%81%84%E3%81%AE%E3%81%8B%E3%80%81"
            .Cells(13, "A").Value = "%E3%80%80%E3%81%A9%E3%81%AE%E3%82%BB%E3%83%AB%E3%81%AB%E3%81%82%E3%82" & _
                                                 "%8B%E3%81%AE%E3%81%8B%E3%82%92%E7%89%B9%E5%AE%9A%E3%81%99%E3%82%8B%E5" & _
                                                 "%BF%85%E8%A6%81%E3%81%8C%E3%81%82%E3%82%8B%E3%81%AE%E3%81%8B%E5%88%A4" & _
                                                 "%E3%82%89%E3%81%AA%E3%81%84"
            .Cells(15, "A").Value = "%E2%91%A2%E2%86%91%E3%81%8C%E3%81%A9%E3%81%AE%E3%82%BB%E3%83%AB%E3%81" & _
                                                 "%AB%E3%81%82%E3%82%8B%E3%81%AE%E3%81%8B%E3%82%92%E7%89%B9%E5%AE%9A%E3" & _
                                                 "%81%99%E3%82%8B%E5%BF%85%E8%A6%81%E3%81%8C%E3%81%82%E3%82%8B%E5%A0%B4" & _
                                                 "%E5%90%88%E3%81%A7%E3%82%82%E3%80%81"
            .Cells(16, "A").Value = "%E3%80%80%E8%A4%87%E6%95%B0%E3%81%82%E3%82%8B%E5%8F%AF%E8%83%BD%E6%80" & _
                                                 "%A7%E3%81%8C%E3%81%82%E3%82%8B%E3%81%AE%E3%81%8B%E7%AD%94%E3%81%88%E3" & _
                                                 "%81%A6%E3%81%8F%E3%82%8C%E3%81%AA%E3%81%84%E3%81%8B%E3%82%89%E3%82%8F" & _
                                                 "%E3%81%8B%E3%82%89%E3%81%AA%E3%81%84"
            .Cells(18, "A").Value = "%E2%91%A3%E2%86%91%E3%81%8C%E3%80%8C%E8%A4%87%E6%95%B0%E3%81%AE%E5%8F" & _
                                                 "%AF%E8%83%BD%E6%80%A7%E3%81%8C%E3%81%82%E3%82%8B%E3%80%8D%E5%A0%B4%E5" & _
                                                 "%90%88%E3%81%AE%E3%81%BF%E3%80%8C%E3%81%99%E3%81%B9%E3%81%A6%E6%A4%9C" & _
                                                 "%E7%B4%A2%E3%80%8D%E3%81%99%E3%82%8B"
            .Cells(19, "A").Value = "%E3%80%80%E5%BF%85%E8%A6%81%E3%81%8C%E5%87%BA%E3%81%A6%E3%81%8F%E3%82" & _
                                                 "%8B%E3%82%8F%E3%81%91%E3%81%A0%E3%81%8C%E3%80%81%E3%83%AA%E3%83%B3%E3" & _
                                                 "%82%AF%E5%85%88%E3%82%92%E8%AA%AD%E3%82%93%E3%81%A7%E4%BD%95%E3%81%8C" & _
                                                 "%E5%88%86%E3%81%8B%E3%82%89%E3%81%AA%E3%81%84"
            .Cells(20, "A").Value = "%E3%80%80%E3%81%AE%E3%81%8B%E8%AA%AC%E6%98%8E%E3%81%8C%E3%81%AA%E3%81" & _
                                                 "%84%E3%81%8B%E3%82%89%E5%88%A4%E3%82%89%E3%81%AA%E3%81%84"
            .Cells(22, "A").Value = "%E2%91%A4%E3%81%BE%E3%81%9F%E3%80%81%EF%BC%93%E3%81%8B%E6%9C%88%E5%89" & _
                                                 "%8D%E3%81%AB%E6%98%8E%E3%82%89%E3%81%8B%E3%81%AA%E3%83%9F%E3%82%B9%E3" & _
                                                 "%82%92%E6%8C%87%E6%91%98%E3%81%97%E3%81%9F%E3%82%B3%E3%83%BC%E3%83%89" & _
                                                 "%E3%82%92%E3%81%9D%E3%81%AE%E3%81%BE%E3%81%BE"
            .Cells(23, "A").Value = "%E3%80%80%E6%8F%90%E7%A4%BA%E3%81%97%E3%81%9F%E3%81%A8%E3%81%93%E3%82" & _
                                                 "%8D%E3%82%92%E3%81%BF%E3%82%8B%E3%81%A8%E3%82%A2%E3%83%89%E3%83%90%E3" & _
                                                 "%82%A4%E3%82%B9%E3%82%92%E3%81%97%E3%81%9F%E3%81%A8%E3%81%93%E3%82%8D" & _
                                                 "%E3%81%A7%E3%82%8D%E3%81%8F%E3%81%AB%E8%AA%AD%E3%82%93%E3%81%A7"
            .Cells(24, "A").Value = "%E3%80%80%E3%81%AA%E3%81%84%E5%8F%AF%E8%83%BD%E6%80%A7%E3%81%8C%E9%AB" & _
                                                 "%98%E3%81%84"
            .Cells(26, "A").Value = "%E2%91%A5%E4%BB%AE%E3%81%AB%E2%86%91%E3%81%8C%E3%81%9F%E3%81%BE%E3%81" & _
                                                 "%9F%E3%81%BE%E3%81%A7%E3%81%82%E3%82%8B%E3%81%A8%E3%81%97%E3%81%A6%E3" & _
                                                 "%82%82%E3%80%81%E4%BB%B6%E3%81%AE%E3%83%88%E3%83%94%E3%83%83%E3%82%AF" & _
                                                 "%E3%81%A7%E3%80%8C%E3%82%82%E3%81%86%E4%B8%80%E6%97%A5"
            .Cells(27, "A").Value = "%E3%80%80%E5%BE%85%E3%81%A3%E3%81%A6%E3%80%8D%E3%81%A8%E8%A8%80%E3%81" & _
                                                 "%84%E3%81%AA%E3%81%8C%E3%82%89%E3%80%81%E7%B5%90%E5%B1%80%E8%BF%94%E7" & _
                                                 "%AD%94%E3%81%8C%E3%81%AA%E3%81%8B%E3%81%A3%E3%81%9F"
            .Cells(29, "A").Value = "%E2%91%A6%E3%81%93%E3%82%8C%E3%82%89%E3%81%AE%E3%81%93%E3%81%A8%E3%82" & _
                                                 "%92%E9%91%91%E3%81%BF%E3%82%8B%E3%81%A8%E3%80%81%E3%81%93%E3%81%AE%E3" & _
                                                 "%83%88%E3%83%94%E3%83%83%E3%82%AF%E3%81%A7%E4%BC%9A%E8%A9%B1%E3%81%AE" & _
                                                 "%E3%82%AD%E3%83%A3%E3%83%83%E3%83%81%E3%83%9C%E3%83%BC%E3%83%AB%E3%81" & _
                                                 "%8C"
            .Cells(30, "A").Value = "%E3%80%80%E6%88%90%E3%82%8A%E7%AB%8B%E3%81%A4%E3%82%88%E3%81%86%E3%81" & _
                                                 "%AB%E6%94%B9%E5%96%84%E3%81%95%E3%82%8C%E3%82%8B%E3%81%AE%E3%81%AF%E9" & _
                                                 "%9B%A3%E3%81%97%E3%81%9D%E3%81%86%E3%81%A0%E3%81%97%E3%80%81%E9%A0%91" & _
                                                 "%E5%BC%B5%E3%81%A3%E3%81%A6%22%E8%B3%AA%E5%95%8F%22%E3%82%92"
            .Cells(31, "A").Value = "%E3%80%80%E8%A7%A3%E9%87%88%E3%81%97%E3%81%9F%E3%81%A8%E3%81%97%E3%81" & _
                                                 "%A6%E3%80%81%E3%81%BB%E3%81%8B%E3%81%AE%E3%83%88%E3%83%94%E3%83%83%E3" & _
                                                 "%82%AF%E3%81%AE%E3%82%88%E3%81%86%E3%81%AB%E6%94%BE%E7%BD%AE%E3%81%95" & _
                                                 "%E3%82%8C%E3%82%8B%E5%8F%AF%E8%83%BD%E6%80%A7%E3%81%8C%E9%AB%98%E3%81%84"
            .Cells(32, "A").Value = "%E3%80%80%E3%81%A8%E6%80%9D%E3%82%8F%E3%82%8C%E5%9B%9E%E7%AD%94%E3%81" & _
                                                 "%99%E3%82%8B%E3%81%AE%E3%81%8C%E9%A6%AC%E9%B9%BF%E3%82%89%E3%81%97%E3" & _
                                                 "%81%8F%E3%81%AA%E3%81%A3%E3%81%9F%E3%80%82"

            静的配列(0) = (Join(Split(Join(WF.Transpose(.Range("A10:A32").Value))), vbLf))
            静的配列(1) = "%E4%BB%A5%E4%B8%8B%E3%81%AE%E7%90%86%E7%94%B1%E3%81%AB%E3%82%88%E3%82%8A%E6" & _
                                  "%92%A4%E9%80%80%E3%81%97%E3%81%BE%E3%81%97%E3%81%9F"

            Application.DisplayAlerts = False
            .Delete
            Application.DisplayAlerts = True
        End With

        For i = 0 To 1
            静的配列(i) = obj.CodeObject.decodeURI(静的配列(i))
        Next i

        MsgBox prompt:=静的配列(0), Buttons:=vbCritical, Title:=静的配列(1)
    End Sub

（もこな２） 2020/09/27(日) 12:55

すごいコードですね。　
頑張って解読してみて、1行1行意味を考えていきます。
（眠神） 2020/09/30(水) 05:49

＞頑張って解読してみて、1行1行意味を考えていきます。
いえ、そのまま実行してみてください。別にシステムクラッシュとかは無いはずです。

 （やっぱり試さないで適当にコメントしてるんだなぁ・・・・）

（もこな２） 2020/09/30(水) 06:09

 実行時エラー'13':
 型が一致しません
 静的配列(0) = (Join(Split(Join(WF.Transpose(.Range("A10:A32").Value))), vbLf))

（ppt） 2020/09/30(水) 09:39

もこな２さんの撤退理由が書いてありますが、pptさんご指摘の箇所がエラーだから、これを解決できないと読めませんね。作戦失敗かと。
（???） 2020/09/30(水) 09:45

 ちなみに、1要素255文字を超える場合、Transposeが使っすね。
 エラーすらリアクションないのは堪えますね。
 同情します・・・
（稲葉） 2020/09/30(水) 10:51

撤退理由マクロのエラー原因は1セル255文字を超えたせいですが、解決方法としては、「.Range("A10:A32").Value」から「.Value」を消すのが手っ取り早いでしょう。

Transposeの機能自体は255文字を超えても対応しているのに、Valueを付けると255文字までの制限でエラー判定してしまうのでしょうかね。 Excelのバグっぽいと思います。

私は2013で確認しましたが、2016以降だとエラーにならないなら、修正されてますね。いかがでしょう？
（???） 2020/09/30(水) 11:37

2016以降を使っていますが、撤退理由読めましたね。
???さんのおっしゃるようにExcelのバグな気がします。
正しいコードでも使う側の環境によってエラー扱いになっちゃうのですね…。
（さすらい） 2020/09/30(水) 12:02

 Oh・・・それは知らなかった
 2013ですが、.Valueなし確認できました。
 静的配列(0) = Join(Split(Join(.[transpose(A10:A32)])), vbLf)
 Evaluateでもイケますね

 内部的な処理順番が違うんですかね？
 Valueプロパティだと、要素を引っこ抜いてから入れ替える
 Rangeオブジェクトだと、オブジェクトをひっくり返してから要素を取り込むみたいな？

 どちらにしろ、読んでいらっしゃる（ようには見えない）

（稲葉） 2020/09/30(水) 12:13

書いているあいだに話しがすすんでましたが、そのまま。

Office365だとエラーにならないのですが、稲葉さんが指摘されているようにTranspose関数がよくないんですかね・・・
（１要素が255文字を超えると"使えない"のタイプミスですよね？）

とりあえず撤退したものに時間かけてもしょうがないので、Transpose関数を使わない↓に修正します。

    Sub めっせーじ()
        Dim MSG As String
        Dim obj As Object, MyRNG As Range
        Set obj = CreateObject("ScriptControl"): obj.Language = "JScript"
        Dim 静的配列(1) As String
        Dim i As Long
        With Worksheets.Add
            .Cells(10, "A").Value = "%E2%91%A0%E3%81%A9%E3%81%86%E3%81%AA%E3%82%8C%E3%81%B0%E8%89%AF%E3%81" & _
                                    "%84%E3%81%AE%E3%81%8B%E8%81%9E%E3%81%84%E3%81%A6%E3%81%84%E3%82%8B%E3" & _
                                    "%81%8C%E7%AD%94%E3%81%88%E3%81%A6%E3%81%8F%E3%82%8C%E3%81%AA%E3%81%84"
            .Cells(12, "A").Value = "%E2%91%A1%E2%86%91%E3%82%92%E7%AD%94%E3%81%88%E3%81%A6%E3%81%8F%E3%82" & _
                                    "%8C%E3%81%AA%E3%81%84%E3%81%8B%E3%82%89%E3%80%81%E3%81%A8%E3%81%AB%E3" & _
                                    "%81%8B%E3%81%8F%E6%9C%89%E7%84%A1%E3%81%8C%E3%82%8F%E3%81%8B%E3%82%8C" & _
                                    "%E3%81%B0%E3%82%88%E3%81%84%E3%81%AE%E3%81%8B%E3%80%81"
            .Cells(13, "A").Value = "%E3%80%80%E3%81%A9%E3%81%AE%E3%82%BB%E3%83%AB%E3%81%AB%E3%81%82%E3%82" & _
                                    "%8B%E3%81%AE%E3%81%8B%E3%82%92%E7%89%B9%E5%AE%9A%E3%81%99%E3%82%8B%E5" & _
                                    "%BF%85%E8%A6%81%E3%81%8C%E3%81%82%E3%82%8B%E3%81%AE%E3%81%8B%E5%88%A4" & _
                                    "%E3%82%89%E3%81%AA%E3%81%84"
            .Cells(15, "A").Value = "%E2%91%A2%E2%86%91%E3%81%8C%E3%81%A9%E3%81%AE%E3%82%BB%E3%83%AB%E3%81" & _
                                    "%AB%E3%81%82%E3%82%8B%E3%81%AE%E3%81%8B%E3%82%92%E7%89%B9%E5%AE%9A%E3" & _
                                    "%81%99%E3%82%8B%E5%BF%85%E8%A6%81%E3%81%8C%E3%81%82%E3%82%8B%E5%A0%B4" & _
                                    "%E5%90%88%E3%81%A7%E3%82%82%E3%80%81"
            .Cells(16, "A").Value = "%E3%80%80%E8%A4%87%E6%95%B0%E3%81%82%E3%82%8B%E5%8F%AF%E8%83%BD%E6%80" & _
                                    "%A7%E3%81%8C%E3%81%82%E3%82%8B%E3%81%AE%E3%81%8B%E7%AD%94%E3%81%88%E3" & _
                                    "%81%A6%E3%81%8F%E3%82%8C%E3%81%AA%E3%81%84%E3%81%8B%E3%82%89%E3%82%8F" & _
                                    "%E3%81%8B%E3%82%89%E3%81%AA%E3%81%84"
            .Cells(18, "A").Value = "%E2%91%A3%E2%86%91%E3%81%8C%E3%80%8C%E8%A4%87%E6%95%B0%E3%81%AE%E5%8F" & _
                                    "%AF%E8%83%BD%E6%80%A7%E3%81%8C%E3%81%82%E3%82%8B%E3%80%8D%E5%A0%B4%E5" & _
                                    "%90%88%E3%81%AE%E3%81%BF%E3%80%8C%E3%81%99%E3%81%B9%E3%81%A6%E6%A4%9C" & _
                                    "%E7%B4%A2%E3%80%8D%E3%81%99%E3%82%8B"
            .Cells(19, "A").Value = "%E3%80%80%E5%BF%85%E8%A6%81%E3%81%8C%E5%87%BA%E3%81%A6%E3%81%8F%E3%82" & _
                                    "%8B%E3%82%8F%E3%81%91%E3%81%A0%E3%81%8C%E3%80%81%E3%83%AA%E3%83%B3%E3" & _
                                    "%82%AF%E5%85%88%E3%82%92%E8%AA%AD%E3%82%93%E3%81%A7%E4%BD%95%E3%81%8C" & _
                                    "%E5%88%86%E3%81%8B%E3%82%89%E3%81%AA%E3%81%84"
            .Cells(20, "A").Value = "%E3%80%80%E3%81%AE%E3%81%8B%E8%AA%AC%E6%98%8E%E3%81%8C%E3%81%AA%E3%81" & _
                                    "%84%E3%81%8B%E3%82%89%E5%88%A4%E3%82%89%E3%81%AA%E3%81%84"
            .Cells(22, "A").Value = "%E2%91%A4%E3%81%BE%E3%81%9F%E3%80%81%EF%BC%93%E3%81%8B%E6%9C%88%E5%89" & _
                                    "%8D%E3%81%AB%E6%98%8E%E3%82%89%E3%81%8B%E3%81%AA%E3%83%9F%E3%82%B9%E3" & _
                                    "%82%92%E6%8C%87%E6%91%98%E3%81%97%E3%81%9F%E3%82%B3%E3%83%BC%E3%83%89" & _
                                    "%E3%82%92%E3%81%9D%E3%81%AE%E3%81%BE%E3%81%BE"
            .Cells(23, "A").Value = "%E3%80%80%E6%8F%90%E7%A4%BA%E3%81%97%E3%81%9F%E3%81%A8%E3%81%93%E3%82" & _
                                    "%8D%E3%82%92%E3%81%BF%E3%82%8B%E3%81%A8%E3%82%A2%E3%83%89%E3%83%90%E3" & _
                                    "%82%A4%E3%82%B9%E3%82%92%E3%81%97%E3%81%9F%E3%81%A8%E3%81%93%E3%82%8D" & _
                                    "%E3%81%A7%E3%82%8D%E3%81%8F%E3%81%AB%E8%AA%AD%E3%82%93%E3%81%A7"
            .Cells(24, "A").Value = "%E3%80%80%E3%81%AA%E3%81%84%E5%8F%AF%E8%83%BD%E6%80%A7%E3%81%8C%E9%AB" & _
                                    "%98%E3%81%84"
            .Cells(26, "A").Value = "%E2%91%A5%E4%BB%AE%E3%81%AB%E2%86%91%E3%81%8C%E3%81%9F%E3%81%BE%E3%81" & _
                                    "%9F%E3%81%BE%E3%81%A7%E3%81%82%E3%82%8B%E3%81%A8%E3%81%97%E3%81%A6%E3" & _
                                    "%82%82%E3%80%81%E4%BB%B6%E3%81%AE%E3%83%88%E3%83%94%E3%83%83%E3%82%AF" & _
                                    "%E3%81%A7%E3%80%8C%E3%82%82%E3%81%86%E4%B8%80%E6%97%A5"
            .Cells(27, "A").Value = "%E3%80%80%E5%BE%85%E3%81%A3%E3%81%A6%E3%80%8D%E3%81%A8%E8%A8%80%E3%81" & _
                                    "%84%E3%81%AA%E3%81%8C%E3%82%89%E3%80%81%E7%B5%90%E5%B1%80%E8%BF%94%E7" & _
                                    "%AD%94%E3%81%8C%E3%81%AA%E3%81%8B%E3%81%A3%E3%81%9F"
            .Cells(29, "A").Value = "%E2%91%A6%E3%81%93%E3%82%8C%E3%82%89%E3%81%AE%E3%81%93%E3%81%A8%E3%82" & _
                                    "%92%E9%91%91%E3%81%BF%E3%82%8B%E3%81%A8%E3%80%81%E3%81%93%E3%81%AE%E3" & _
                                    "%83%88%E3%83%94%E3%83%83%E3%82%AF%E3%81%A7%E4%BC%9A%E8%A9%B1%E3%81%AE" & _
                                    "%E3%82%AD%E3%83%A3%E3%83%83%E3%83%81%E3%83%9C%E3%83%BC%E3%83%AB%E3%81" & _
                                    "%8C"
            .Cells(30, "A").Value = "%E3%80%80%E6%88%90%E3%82%8A%E7%AB%8B%E3%81%A4%E3%82%88%E3%81%86%E3%81" & _
                                    "%AB%E6%94%B9%E5%96%84%E3%81%95%E3%82%8C%E3%82%8B%E3%81%AE%E3%81%AF%E9" & _
                                    "%9B%A3%E3%81%97%E3%81%9D%E3%81%86%E3%81%A0%E3%81%97%E3%80%81%E9%A0%91" & _
                                    "%E5%BC%B5%E3%81%A3%E3%81%A6%22%E8%B3%AA%E5%95%8F%22%E3%82%92"
            .Cells(31, "A").Value = "%E3%80%80%E8%A7%A3%E9%87%88%E3%81%97%E3%81%9F%E3%81%A8%E3%81%97%E3%81" & _
                                    "%A6%E3%80%81%E3%81%BB%E3%81%8B%E3%81%AE%E3%83%88%E3%83%94%E3%83%83%E3" & _
                                    "%82%AF%E3%81%AE%E3%82%88%E3%81%86%E3%81%AB%E6%94%BE%E7%BD%AE%E3%81%95" & _
                                    "%E3%82%8C%E3%82%8B%E5%8F%AF%E8%83%BD%E6%80%A7%E3%81%8C%E9%AB%98%E3%81%84"
            .Cells(32, "A").Value = "%E3%80%80%E3%81%A8%E6%80%9D%E3%82%8F%E3%82%8C%E5%9B%9E%E7%AD%94%E3%81" & _
                                    "%99%E3%82%8B%E3%81%AE%E3%81%8C%E9%A6%AC%E9%B9%BF%E3%82%89%E3%81%97%E3" & _
                                    "%81%8F%E3%81%AA%E3%81%A3%E3%81%9F%E3%80%82"

            For Each MyRNG In .Range("A10:A32")
                静的配列(0) = 静的配列(0) & MyRNG.Value & vbLf
            Next MyRNG

            静的配列(1) = "%E4%BB%A5%E4%B8%8B%E3%81%AE%E7%90%86%E7%94%B1%E3%81%AB%E3%82%88%E3%82%8A%E6" & _
                                  "%92%A4%E9%80%80%E3%81%97%E3%81%BE%E3%81%97%E3%81%9F"
            Application.DisplayAlerts = False
            .Delete
            Application.DisplayAlerts = True
        End With

        MsgBox _
            prompt:=obj.CodeObject.decodeURI(静的配列(0)), _
            Buttons:=vbCritical, _
            Title:=obj.CodeObject.decodeURI(静的配列(1))
    End Sub

（もこな２） 2020/09/30(水) 12:37

脱線ついでに。

2016以降OKという事なら、2007で1セルの文字数が増えた際に直し忘れたExcelバグか、2013でSDI化した際に作り込んだバグでしょうね。いずれにせよ、いまはこっそり直された、と。

以下のコードで再現できます。 2010以前の実行結果も判ると、ちょっとしたノウハウになりそう。

 Sub test()
    Dim r As Range
    Dim vw As Variant

    Range("A1").Value = String(256, "a")
    Set r = Range("A1")
    vw = WorksheetFunction.Transpose(r.Value)
    MsgBox vw
 End Sub
（???） 2020/09/30(水) 13:25

 実行エラー'1004':
 WorksheetFunctionクラスのTransposeプロパティを取得できません。
 vw = WorksheetFunction.Transpose(r.Value)

 Excel2013です。
（ppt） 2020/09/30(水) 16:35

ピンポイントでTransposeのエラーを特定するコードなので、エラーメッセージは元と違います。 Excel2013の場合、文字長を255にすれば、正常に通ります。

Range指定を縦方向複数セル範囲に変えれば、元と同じ、型のエラーになりますが、文字長の問題だと判る単セル例にしました。（複数セルにするならvwが配列になるので、MsgBoxもJoinを使うように変える必要あり）
（???） 2020/09/30(水) 17:00

 2016でもエラーになり、文字長を255にするとエラーは出なかった。
（ねむねむ） 2020/09/30(水) 17:02

質問した人は見ていないかもですが…

失礼しました
365と2016の環境を同時に使っていて、365で確認していました。
2016だとねむねむさんのおっしゃる通りエラーでした。
（さすらい） 2020/10/09(金) 13:06

興味深い話が出ていたので、検証してみました。

まず、こんな記事が見つかりました。

 https://docs.microsoft.com/ja-jp/previous-versions/office/troubleshoot/office-developer/transfer-excel-data-from-ado-recordset
 > 次のような転置メソッドの制限事項に注意する必要があります。
 > 配列には、255文字を超える要素を含めることはできません。
 > 配列に Null 値を含めることはできません。
 > 配列内の要素の数は、5461を超えることはできません。

いくつかの検証記事を見ると、この制限のうち文字数制限と要素数制限はExcel2007で撤廃されたと予想されています。
（上限はあるのかもしれませんが、資料が確認できていません）

次の環境にて上記コード(256文字)を試した結果
2003と2016は「WorksheetFunction クラスの Transpose プロパティを取得できません。」
O365はTransposeではエラーが出ませんでした。
しかし、戻り値 vwが Variant(1 to 1)となっており、MsgBoxで「型が一致しません。」となりました。

文字数を255文字に減らした場合、全てのバージョンでvwはVariant/Stringとなり正常にメッセージが表示されました。

したがって2段階の仕様変更が起きていそうです。

 * 2003で上記制限があった
 * 2007で文字数制限と要素数制限が撤廃された ★ただし非配列は考慮しなかった
 * O365で特定条件下で配列を返すように変更された

この動きを見る限り、O365の中でもスピルのアップデートが原因ではないかと推察します。（いまさら検証できませんが）
スピル更新では戻り値は配列が基本となりましたので、Variant(1 to 1)が返るのでしょう。
しかし255文字以下の場合は配列で戻っていないので、もう少し込み入った特例措置が入っていそうです。

（鳥の人） 2020/10/09(金) 14:14

コメント返信：

[ 一覧(最新更新順) ]

[[20200922004300]] 『列全体に含まれている値とセルに入力されている値』（眠神） ページの最後に飛ぶ

[[20200922004300]]　『列全体に含まれている値とセルに入力されている値』（眠神）　ページの最後に飛ぶ