[[20150820155357]]　『値コピー』（1901）　ページの最後に飛ぶ

『値コピー』（1901）

初歩的なことで教えてください。
セルＡ１に関数式が入っており、その結果の「値」のみをＣ１にコピーする
簡単な方法（※）として、下記で説明されているのをみかけました。
※マクロ記録で出てくる「PasteSpecial Paste:=xlPasteValues」の代わりで

Sub Test()
Range("A1").Value = Range("C1").Value
End Sub

ところが実際に試してみると、元のＡ１セルの関数式が「消去」されて
Ｃ１には何も表示されません。

これはどういうことでしょうか？

複数のサイトで同じように記述されていたので、おそらくごく基本的なことが
わかっていないせいだとは思うのですがよくわかりません．．

＜使用 Excel：Excel2007、使用 OS：WindowsXP ＞

こんにちは

AとCが逆です。

Sub Test()
　　Range("C1").Value = Range("A1").Value
End Sub
（ウッシ） 2015/08/20(木) 15:59

 ＝(イコール)の使い方ですよね。

 この世界では、右辺にあるものを左辺に代入することになります。

 数式でも、＝の左側にセル(値の容器)がある、と思って頂くとイメージが涌くと思います。

（半平太） 2015/08/20(木) 16:04

うわっ、これは恥ずかしい．．

やはり“抜けて”ましたか、基本が。

ウッシさん、半平太さんありがとうございました。
（はやく“切り貼り”からぬけでないとダメと反省です）
（1901） 2015/08/20(木) 16:09

（追記 16:36）
再度すみません。
今、“共有セル範囲の取得”で下記「AF1」（オートフィルタです）を
得て、その内容をセル「Ｘ２」に値コピーしようとしたんですが、
Ｘ２セルにしか値コピーできません。

Set AF1 = Application.Intersect(A, B)
Range("X2").Value = AF1.Value 　　　

オートフィルタには簡単な方法ではムリなんでしょうか？

 ちょっと質問の意味が分かりません。

 ＞Set AF1 = Application.Intersect(A, B) 
 このA,Bですけど、実際は何なのですか？

 それがどうして、オートフィルタになるんですか？

 ＞Range("X2").Value = AF1.Value 
 オートフィルタにValueプロパティなんて無いと思うのですけど。

（半平太） 2015/08/20(木) 17:16

 コードすべてをアップされたほうがいいと思いますが、もしかして

 Sub Test()
    Dim A As Range
    Dim B As Range
    Dim AF1 As Range

    Set A = ActiveSheet.AutoFilter.Range
    Set B = A.Offset(1)
    Set AF1 = Intersect(A, B)

    AF1.Copy Range("X2")

 End Sub

 こんなことが、やりたかったんでしょうか？

 ところで、オートフィルターでフィルタリングされている状態ですから２行目あたりも非表示になっている可能性はありますよね。

 X2 から下に結果をコピペしても、その時点では非表示行にペーストされたものは見えません。
 オートフィルターを解除したら、はじめて、それらは目に見えるということなんですけど、それでいいのですか？

（β） 2015/08/20(木) 17:50

“舌足らず”ですみません。
関係ある箇所の実際のマクロは以下となります。

Range("A1").AutoFilter Field:=12, _
Criteria1:=Array("1", "2", "3"), _
Operator:=xlFilterValues
With ActiveSheet.AutoFilter
Set A = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
Set B = Range(Range("B2"), Cells(.Range(.Range.Count).Row, "B"))
End With

 >オートフィルタにValueプロパティなんて無い．．．
→そうでしたか。　となると簡単な方法はムリですか。

（1901） 2015/08/20(木) 17:55

追記）

 >オートフィルターを解除したら、はじめて．．．（割愛）それでいいのですか？
→はい、構いません。　実際に値コピー前に解除しています。

 >>となると簡単な方法はムリですか。

 いえ、簡単ですよ。ちゃんと正しい記述をすれば。

 >>実際に値コピー前に解除しています

 なるほど。では、以下のコードで結果オーライになるはずです。試してください。

 Sub Sample()
    ActiveSheet.AutoFilterMode = False  '念のため
    Range("A1").AutoFilter Field:=12, _
                Criteria1:=Array("1", "2", "3"), _
                Operator:=xlFilterValues
    With ActiveSheet.AutoFilter
        Intersect(.Range, .Range.Offset(1)).Copy Range("X2")
    End With
    ActiveSheet.AutoFilterMode = False  'オートフィルター解除
 End Sub

（β） 2015/08/20(木) 18:43

 ↑　抽出の有無のチェックをすべきですが、そこは手を抜いています。

（β） 2015/08/20(木) 18:47

βさん、ありがとうございます。　

ご提示はオートフィルタをかけた全範囲をＸ２以降に出力するものですね。
やりたいのは、Ｌ列でフィルタをかけた後Ｂ２以下をＸ２以下に出力したいのですが．．
（実際には任意の複数列を任意の列に出力します）

先の「Set AF1」で任意列の抽出をしている（つもり）なんですが。
抽出対象の列の中に関数式を使用している列があって、「Copy」では都合が悪く
簡単な値コピーの方法をお聞きしています。　説明不足ですみません。
（ご提示のをいろいろ弄ってますが今だうまくいかず．．．）

（1901） 2015/08/21(金) 10:15

こんにちは

任意列の抽出って、フィルタオプションを使った方がいいのでは？

（ウッシ） 2015/08/21(金) 10:28

最初はフィルタオプションを使ってましたが、その後の作業を含め
自動化をしようとしています．．

“任意”の文字がいけなかったですね。
作業ごとに抽出列を使い分けている．．とご理解ください。
（1901） 2015/08/21(金) 10:56

 >>やりたいのは、Ｌ列でフィルタをかけた後Ｂ２以下をＸ２以下に出力したいのですが．． 
 >>（実際には任意の複数列を任意の列に出力します）

 という要件をちゃんと説明してもらわなければ、回答側としては、何度も無駄足を踏むことになります。
 コードがちゃんとしていれば、そこから推測することもできるかもしれませんが、いかんせん
 コードがちゃんとしたものではないので。

 >>作業ごとに抽出列を使い分けている．．とご理解ください。

 だから、ウッシさんがいわれるように、フィルターオプションがベストでしょう。

 >>その後の作業を含め自動化をしようとしています．．

 フィルターオプションをＶＢＡで実行すればいいんですよ、
 マクロ記録をとればコードが生成されますが、AdvancedFilterメソッドを使います。

（β） 2015/08/21(金) 11:19

ありがとうございます。

「AdvancedFilterメソッド」についてはこれから学習したいと思います。
当初から話を拡散させてしまいましたので、改めて現在のマクロを提示します。

以下のマクロで終わり近くの「AF1.Copy Range("O2")」を値コピーに“簡単な方法で”
修正したい．．というもので、自分では下記ぐらいしか思い浮かびません。
AF1.Select
Selection.Copy
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

よろしくお願いします。
－現在のマクロ－
Sub test_0820()
Dim A As Range, B As Range, C As Range, D As Range
Dim AF1 As Range, AF2 As Range, AF3 As Range

Range("A1").AutoFilter Field:=12, _
Criteria1:=Array("1", "2", "3"), _
Operator:=xlFilterValues

With ActiveSheet.AutoFilter
Set A = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
Set B = Range(Range("B2"), Cells(.Range(.Range.Count).Row, "B"))
Set C = Range(Range("C2"), Cells(.Range(.Range.Count).Row, "C"))
Set D = Range(Range("K2"), Cells(.Range(.Range.Count).Row, "K"))
End With

Set AF1 = Application.Intersect(A, B) ' 共有セル範囲の取得(1)
Set AF2 = Application.Intersect(A, C) ' 共有セル範囲の取得(2)
Set AF3 = Application.Intersect(A, D) ' 共有セル範囲の取得(3)

Range("A1").AutoFilter

Range("O2:O" & ActiveSheet.UsedRange.Rows.Count).ClearContents
Range("P2:P" & ActiveSheet.UsedRange.Rows.Count).ClearContents
Range("Q2:Q" & ActiveSheet.UsedRange.Rows.Count).ClearContents

AF1.Copy Range("O2") ' O2 以下へコピー
AF2.Copy Range("P2") ' P2 以下へコピー
AF3.Copy Range("Q2") ' Q2 以下へコピー
End Sub
（1901） 2015/08/21(金) 13:43

 何度も　“簡単な方法で” を強調しておられますが、通常の値転記コード、

 転記先領域.Value = 転記元領域.Value で、右辺がとびとびの領域の場合は、転記先領域に、右辺の最初の領域のみが転記されます。
 そういう仕様ですので、これを、一発で、えいやっと値転記を行う記述はないでしょうね。

 やるなら、それを地道に行うサブプロシジャを書いておいて、コードではそのプロシジャに引数をあたえて
 あたかも1行で処理しているようにみせかける？

 現在のコピペで、Copyメソッドでペースト先を指定せず、PasteSpecial の xlPasteValues を、別途、わけて記述する方法は
 もちろんありますけど。

 まぁ、そういったこともひっくるめてフィルターオプションがいいんじゃないですか。

（β） 2015/08/21(金) 14:16

こんにちは

飛び飛びのセル範囲のValueは扱い辛いですし、AutoFilterを一旦解除してO～Qのセル範囲をクリア
した時点でAF1～AF3のセル参照は全てのデータになってしまうと思います。

AutoFilterを解除する前に一時的に用意したシートに貼り付けておいてから転記すればいいと思います。

Sub test1()

    Dim B   As Range
    Dim C   As Range
    Dim D   As Range
    Dim i   As Long
    Dim aSh As Worksheet
    Dim wSh As Worksheet

    Set aSh = ActiveSheet
    Set wSh = Worksheets.Add

    With aSh
        .Activate
        i = .UsedRange.Rows.Count
        .Range("A1").AutoFilter Field:=12, _
            Criteria1:=Array("10", "11", "12", "13", "14", "6", "7", "8", "9"), _
            Operator:=xlFilterValues

        With .AutoFilter
            Set B = .Range.Offset(1).Columns(2)
            Set C = .Range.Offset(1).Columns(3)
            Set D = .Range.Offset(1).Columns(11)
        End With

        B.Copy wSh.Range("A1")
        C.Copy wSh.Range("C1")
        D.Copy wSh.Range("E1")

        .AutoFilterMode = False

        .Range("O2:O" & i).ClearContents
        .Range("P2:P" & i).ClearContents
        .Range("Q2:Q" & i).ClearContents

        wSh.Range("A1").CurrentRegion.Copy .Range("O2") ' O2 以下へコピー
        wSh.Range("C1").CurrentRegion.Copy .Range("P2") ' P2 以下へコピー
        wSh.Range("E1").CurrentRegion.Copy .Range("Q2") ' Q2 以下へコピー

    End With

    Application.DisplayAlerts = False
    wSh.Delete
    Application.DisplayAlerts = True

End Sub

（ウッシ） 2015/08/21(金) 15:46

 フィルターオプションがいいとは思いますが、アップされたオートフィルターベースのコードを
 「一見」シンプルな値転記をしているように見せかけたコードです。

 Sub Test2()

    Application.ScreenUpdating = False
    ActiveSheet.AutoFilterMode = False
    Range("A1", ActiveSheet.UsedRange).Offset(1).Columns("O:Q").ClearContents
    Range("A1").AutoFilter Field:=12, Criteria1:=Array("1", "2", "3"), Operator:=xlFilterValues

    If ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
        MsgBox "抽出がありません"
    Else
        setValue Columns("B"), Range("O2")
        setValue Columns("C"), Range("P2")
        setValue Columns("K"), Range("Q2")
    End If

    ActiveSheet.AutoFilterMode = False

 End Sub

 Private Sub setValue(r As Range, t As Range)
    Dim c As Range
    Dim pos As Range
    Set pos = t
    For Each c In Intersect(ActiveSheet.AutoFilter.Range, r).Cells
        If c.Row > 1 Then
            If Not c.EntireRow.Hidden Then
                pos.Value = c.Value
                Set pos = pos.Offset(1)
            End If
        End If
    Next
 End Sub

（β） 2015/08/21(金) 15:51

Array("10", "11", "12", "13", "14", "6", "7", "8", "9"),
ここは、
Array("1", "2", "3"),でした。
（ウッシ） 2015/08/21(金) 16:01

ウッシさん、βさん、ありがとうございます。
元はといえば、「Value」記述のように値コピーを1行で記述できないか．．
というのが発端だったんですが、“飛びとび”ではどうもムリということですね。
件のAF1の出力は最終的に余計な引数などを省いて下記とするつもりです。
AF1.Copy
Range("O2").PasteSpecial Paste:=xlPasteValues

ご提示いただいたお二人のコードをみて、本当にいろいろな記述のしかたが
あるんだなぁ～とつくづく思います。
このようにスラスラとコードが書ける人の“頭のなか”はどうなっているのか．．
不思議でしょうがないです（笑）

コードの“読解”はこれからで時間かかりそうなので、先ずはお礼とさせていただきます。
（1901） 2015/08/21(金) 16:27

 フィルターオプション版も参考として。
 O1:Q1 にはリスト内の該当列のタイトル文字列と同じものが記入されていることと、少なくともＮ列は空白列だという前提です。
 S1:S2を作業域に使います。

 Sub Test3()
    Application.ScreenUpdating = False
    Range("S1").ClearContents
    Range("S2").Formula = "=ISNUMBER(MATCH(L2,{""1"",""2"",""3""},0))"
    Range("A1").CurrentRegion.CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("S1:S2"), CopyToRange:=Range("O1:Q1"), Unique:=False
    Range("S1:S2").ClearContents
 End Sub

（β） 2015/08/21(金) 17:19

こんにちは

数式という話があったので、O～Q列を参照する数式がB、C、K列にセットされているのかも、
なんて考えてたんですけど・・・

どうなんでしょ？

（ウッシ） 2015/08/21(金) 17:21

 βさんへ
 ＞ O1:Q1 にはリスト内の該当列のタイトル文字列と同じものが記入されていること

 Sub test_0820()のコードはかなり神経質にO1:Q1を避けていますので、
 同じでは無いような気がしているんですけど。。 真相はどうなんでしょうね。。

（半平太） 2015/08/21(金) 20:05

 To 半平太さん

 >>test_0820()のコードはかなり神経質にO1:Q1を避けていますので、 同じでは無いような気がしているんですけど

 可能性はありますね。コードの最初に、O1:Q1のFormula(念のためValueではなくFormula)をVariant型変数に保管しておき
 当該列のタイトルで書き換え。処理後に、戻してやるということで、対応は可能かと思います。

（β） 2015/08/22(土) 06:59

皆さま、　ありがとうございます。
返答遅くてすみません。（業務後、まだ酩酊中です_ _;）

 >>test_0820()のコードはかなり神経質にO1:Q1を避け．．同じでは無いような気がしているんですけど

確かに避けてます。　1行目はタイトル行なのでオートフィルタ後には不要が理由です。
もうひとつ、「Offset」は「エクセル最終行」の問題があるらしいので。
※実用上問題ないと思ってますが、よくわかってないもので．．
（初心者としてはまだまだ“守破離”の境地にはほど遠いです）

でもまぁ～、値コピーから始まってこんなに深く（自分だけ？）教えてもらえるなんて
最高ですね、ここは（しかもタダで(笑)）。
申訳ないですけど、知らない“単語？”があったりして“読解”は未だできておりません。

拡散させてしまったようですが、ここまでの疑問点は続けていっていいんですよね？
（1901） 2015/08/22(土) 15:29

 >>1行目はタイトル行なのでオートフィルタ後には不要が理由です。

 そうだろうとは推察していました。βがアップした（β） 2015/08/21(金) 17:19のコードは
 ここに、元リストの抽出必要なタイトルと同じものがセットされているという前提です。
 (違っていたとしても　（β） 2015/08/22(土) 06:59 でコメントした通り、コード２行の追加でＯＫになります）

 >>、「Offset」は「エクセル最終行」の問題があるらしいので。

 はい。確かに OffSetしようとしている領域がエクセル最終行（1048567行目)まであるような膨大な領域であればエラーになります。
 領域が 1048566行目までならＯＫなので普通、あまり気にしないでOffSetを使っています。

 かつ、アップしたフィルターオプション処理では、このOffSet の呪縛がないので安心してもらっていいですよ。

 >>拡散させてしまったようですが、ここまでの疑問点は続けていっていいんですよね？

 もちろんですよ。

（β） 2015/08/22(土) 16:00

コメント返信：

[ 一覧(最新更新順) ]

[[20150820155357]] 『値コピー』（1901） ページの最後に飛ぶ

[[20150820155357]]　『値コピー』（1901）　ページの最後に飛ぶ