[[20130329104317]]　『名前定義したセルの中の数式を結果値に変換するマ』（まさ）　ページの最後に飛ぶ

『名前定義したセルの中の数式を結果値に変換するマクロ』（まさ）
Excel2010、Windows 7

1つのシートに名前定義した範囲(不規則)が複数あります。
名前はSS01、SS02、SS03・・・という感じで連番です。
名前は無い場合もあり、最大何個になるかも分かりません。(連番が2桁なので99？)
名前はブック単位ではなくシート単位なので各シートに同じ名前のSS01、SS02・・・があり、範囲もバラバラです。

この名前定義されたセルに入力されている数式を結果表示されている値に変換したいのです。
現在は一つ一つ名前で選択された範囲をコピーして同じ位置に値貼り付けしています。
しかしブック内に同様のシートが大量にある為、VBAで簡単にできないかと考えています。

とりあえず手動でやっていたものを自動記録して全部のシートで実行すればいいかと思い以下のようなコードを作りました。
しかし名前が無いシートではエラーが返り、名前があってもSS01だけしかできません。

Sub Test1()

   Dim Sht As Worksheet
      For Each Sht In Worksheets
          Sht.Select
    Application.Goto Reference:="SS01"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
      Next Sht
End Sub

定義された名前が連番なのでどうにかなりそうな気はするのですが、なんせVBA初心者なのでよく分かりません。
名前の先頭がSSとなっている箇所は全部対象なのでSS*とかワイルドカード使ったりもできるんでしょうか。

以上お助けください。

 回答じゃないんだけど、エクセルバージョンは 2003 ?
 2007以降では SS12 なんて名前は、セルの名前なので設定できない。
 いずれバージョンアップしたとすれば、問題が出てしまうかな？
 名前を変えたほうがいいんじゃない？

 (ぶらっと)

 で、そちらでは SS10 といった名前がＯＫだとして

 Sub Sample()
    Dim n As Name

    For Each n In Names
        If n.Name Like "SS*" Then
            With Range(n.RefersTo)
                .Value = .Value
            End With
        End If
    Next

 End Sub

 こんなことかな？

 (ぶらっと)

ぶらっとさん、度々ありがとうございます。
本当に助かります。

名前を定義する時に範囲をブックかシートか選べるのですが、今回はシートになっています。
シートになっていると頂いたコードでは動きませんでした。ブックになっていると動きます。

ただし、名前の指定にワイルドカードが使える事で名前の付け方に幅ができたので、名前定義の範囲をブックに変更する事にしました。

これで頂いたコードで解決する事ができます。
また「SS10」のようなセル番地みたいな名前もやめます。

前回に続いて的確なご回答を頂きましてありがとうございました。

（まさ）

一応名前をブックレベル限定にするという事で、頂いたコードにやりたい処理を追加して以下のコードで落ち着きました。名前の付け方は仮で以前のまま(SS*)にしています。

Sub Sample1()

    Dim n As Name
    For Each n In Names
        If n.Name Like "SS*" Then
            With Range(n.RefersTo)
                Application.Goto Reference:=n.Name
                Selection.Copy
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                Selection.Interior.ColorIndex = xlNone
                Range("A1").Select
                End With
        End If
    Next
 End Sub

ところがシートレベルの名前も使う事になりまして、同様なコードを作成してみました。

Sub Sample2()

    Dim s As Worksheet
    Set s = ActiveSheet
    Dim n As Name
    For Each n In s.Names
        If n.Name Like "SS*" Then
            With Range(n.RefersTo)
               Application.Goto Reference:=n.Name
               Selection.Copy
               Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
               Selection.Interior.ColorIndex = xlNone
               Range("A1").Select
            End With
        End If
    Next
 End Sub

シートレベルの名前なのでアクティブシートに限定したのですが、このコードだと名前を付けたセルにジャンプしてくれません。どこがおかしいでしょうか？

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

（まさ）

 名前定義の名前は「ブックの中でユニーク（重複がない）」という大原則があります。
シートレベルの名前であってもそれは同じで、各シートに同じ名前がある
のではなくて、Sheet1!SS20　のようにシート名＋！を付した名前になります。
　
検証はしていませんが、上記の点を踏まえてコーディングしてはいかがでしょうか。

 Sub Sample3()
     Dim n As Name
     For Each n In ThisWorkbook.Names
         Debug.Print n.Name
     Next
 End Sub
　
まずは上記のようなコードで名前がどのように管理されているか確認してください。
結果はVBEのイミディエイトウィンドウに出力されます。
　
追記
Sample1やSample2のWith　～　End Withのブロックは役に立ってないです。
（みやほりん）

 まず、（まさ）さんが動かしている環境は 2003以前？2007以降?

 基本的には 2003以前であれば、【表技】として、あるシートの定義した同じ名前を別シートで定義することはできないね。
 ただ、2003以前であっても、名前定義をしたシートをシートコピーすると、コピーでできたシートにも同じ名前の定義ができてしまう。
 いわゆる、2007以降で【表技】となった「シートレベル」の名前定義が（しらないうちに）なされていたということなんだけど。

 で、こちらには、もう 2003 の環境がなく、2010では　Range(n.RefersTo)　で、ブックレベルであれシートレベルであれ
 その名前で参照しているセルは正しくポイントできるけどね？ 2003 では、できなかった？

 (ぶらっと)

 「挿入」－「名前」－「定義」の名前欄や数式バーの名前ボックスで「シート名!名前」と設定することでシートレベルの名前定義は2003以前でも可能。
 (ねむねむ)

みやほりんさん、ぶらっとさん、ねむねむさん、ご回答ありがとうございます。
エクセルは2010ですが、ぶらっとさんのおっしゃるとおりシートをコピーして作成する事が多々ある為、
その都度ブックレベルに再定義するのが面倒なのでシートレベルの名前も拾えないかと考えました。

で、

 Sample2のコードで
 Debug.Print n.Name　すると
 Sheet1!SS01　のようにシート名がついた名前が出力されます。

 なので　Application.Goto Reference:=n.Name
 でよいと思ったのですが、ジャンプしません。

 直接n.Nameのところに名前を書き込むとジャンプします。
 Application.Goto Reference:="Sheet1!SS01"

ちなみにSample1で
Debug.Print n.Name　するとブックレベルもシートレベルも全て出力されますが、
シートレベルのセルにはジャンプし無い為結果としてブックレベルのみが対象となっている？みたいです。

 With　～　End With
は削除しました。

 （まさ）

 To ねむねむさん

 わぁ、そうだったんですか。しったかぶりで、できないなんて、汗、汗。ペコリ。

 (ぶらっと)

 >シートレベルのセルにはジャンプし無い

 いま、2010環境で、Sheet1 の F5 に ブックレベルの名前 "ABCD" を設定。
 続けて、Sheet2 の F5 に　シートレベル(Sheet2)の名前 "ABCD" を設定。

 以下のコードを動かしたけど、それぞれ、ちゃんとしたところにジャンプするけど？

 Sub Test()
    Dim nm As Name

    For Each nm In Names
        Application.Goto Range(nm.RefersTo)
        MsgBox "CFM"
    Next

 End Sub

 (ぶらっと)

 原因は「If n.Name Like "SS*" Then」で「Sheet1!SS01」という名前が
引っかからないからじゃないかなぁ。
ジャンプしないんじゃなくて、それ以前のところ。
F8でステップ実行すれば、感づく部分だと思われ。
　
「If n.Name Like "*SS*" Then」とするところ。
　
コピー、値貼り付けだけならジャンプする必要はないので、
　
Sub Sample2()
    Dim n As Name
    For Each n In ActiveSheet.Names
        If n.Name Like "*SS*" Then
            With Range(n.RefersTo)
               .Copy
               .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
               .Interior.ColorIndex = xlNone
            End With
        End If
    Next
 End Sub
　
このような感じではないでしょうか。
（みやほりん）

ぶらっとさん、みやほりんさん、ご回答ありがとうございます。
ぶらっとさんと同じようにしたらきちんと動きました。

みやほりんさんのご指摘の部分が原因だったようです。
頂いたコードで思っていた動きができました。

皆さん、本当にありがとうございました。

 To みやほりんさん

 > 原因は「If n.Name Like "SS*" Then」で「Sheet1!SS01」という名前が引っかからないからじゃないかなぁ

 ごもっとも！！！！！

 (ぶらっと)

最終的に以下のコードにしました。
ブック丸ごとブックレベルシートレベル関係無く、指定文字を含む名前全てを対象とする形です。
本当はこれがやりたかったのです。ありがとうございます。
名前の付け方に注意しないといけないですね。

 Sub Sample3()
    Dim n As Name
    For Each n In Names
        If n.Name Like "*SS*" Then
            With Range(n.RefersTo)
               .Copy
               .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
               .Interior.ColorIndex = xlNone
            End With
        End If
    Next
 End Sub

コメント返信：

[ 一覧(最新更新順) ]

[[20130329104317]] 『名前定義したセルの中の数式を結果値に変換するマ』（まさ） ページの最後に飛ぶ

[[20130329104317]]　『名前定義したセルの中の数式を結果値に変換するマ』（まさ）　ページの最後に飛ぶ