[[20080612101317]]　『数式の値だけを参照する方法ってありますか』(nori) 　ページの最後に飛ぶ

『数式の値だけを参照する方法ってありますか』(nori)

 =IF($B8="","",VLOOKUP($B8,INDIRECT($F$4),2,FALSE))
 お世話になります。今､上のような式があります。この式で得られる値だけを別のセルに
 さんしょうせっていできるのでしょうか？？？
 例えば､上の式がG3入っているとして､A3セルに=G3と書き込むと値だけでなく､式も入って
 しまいます。これを避けたいのです。
 宜しくお願い致します。

 <nori>
 判り辛い質問でした。
 お示しした、式の値を他のセルで参照したときに､その値を固定（元の式の値が変更されても
変わらない様にする。）することが出来るでしょうかと言う意味です。宜しくお願い致します。

 数式だけでは無理でしょう。
 コピー - 形式を指定して貼り付け値貼り付け
 ですかね...
 (seiya)

 seiyaさん、お世話になります。ネット上で見たものを繋ぎ合わせてつくったものですが､
 上手く行きません。特に､コピー＆ペーストの所で引っかかっております。
 コピー＆ペーストの所を教えてください。宜しくお願い致します。
 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim j As Integer

    If Target.Row = 1 Or Target.Row > 70 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    For j = 8 To 7
    Application.EnableEvents = False
        Cells(j, 14).Copy
        Cells(j, 8).xlPasteValues
    Application.EnableEvents = True
    Next
End Sub

 <nori>
 worksheet_change関数と言うのは現在入力されている値を実際に書き換える際に発生するもので､
VlookUp関数等で値が変化するだけでは発生しないものでしょうか？ヘルプは見たつもりですが、
今一理解できていないようです。そもそもこれが駄目だとすると、最初から考え直さざるを得なく
なるようです。

 あるセルに変化があった時、それに応じて変化させたい場合は、数式を使います。
 変化させたくない場合は、値を入力します。

 ある時は「数式」の様になり、ある時は「値」の様にしたいとすれば VBAになるかと思いますが、
 何かが起こった場合、どちらの「ある時」になるのか判定できないと始まりません。

 その判定基準をご説明いただいていますか？　私には読み取れなかったのですが。

 (うまく動かないコードから、本当にやりたいことを読み取るのは至難です。)

 (半平太)

 > Cells(j, 8).xlPasteValues
 ↓
 Cells(j, 8).PasteSpecial xlPasteValues

 Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 1 Or Target.Row > 70 Then Exit Sub
    If Target.Column <> 14 Then Exit Sub
    Application.EnableEvents = False
        Cells(7, 14).Resize(2).Copy
        Cells(7, 8).PasteSpecial xlPasteValues
    Application.EnableEvents = True
 End Sub
 (seiya)

 半平太さん、seiyaさん、お世話になります。
 以前､こちらの掲示板で教えていただき､一応の完成を見て､使って居たのですが､
その時ペンディングにしておいたことを実現したく、再開したものです。
　やりたいことは､検索値を入れる列（B列､リスト）があり､そこである値を選択
すると、VlookUp関数により、カタログページ、注文コード、単価､発注単位が入
るように設定したセルがあります。これを発注入力シートとします。
この発注入力シートでは発注先ごとに注文するのではなく､登録されている発注先な
らばどこでも選択出来るようにし､後に発注先ごとに仕分けするようにしてあります。
　ここで問題になるのが､登録されている業者の全ての品目を検索をかけられる様に
設定すると､発注品目を選ぶだけでも一仕事になってしまいます。
　そこで、業者毎に別々に検索値､検索範囲の配置位置を変えるようにしました。
　ところが、この様にすると、先にA業者への発注品目を選択した後､B業者の発注
品目を選択するために検索値､検索範囲を変更してしまうと､結果として､A業者の発注
品目の検索範囲を見ていなくなリますから､A業者への発注品目のところが､#N/Aエラー
になってしまいます。
　それを避けるため､VlookUp関数で抽出され、値の入る所を別のセルにし､その値
だけを発注シートにペーストすれば良いのでは？と考えました。今回の質問は､この
ことを実現するためのステップです。

 seiyaさん、試してみました。動くこと確認しました。有難う御座います。
動くのですが､実際にセルの値を変更しないと動かないようですね。VlookUｐ関数
で抽出された値が変化するだけでは､Worksheet_change関数は動作しないようです。
Taregetとして指定した列の上で､何らかの値を変更すると動作します。
この問題を解決するアイデアは無いでしょうか？宜しくお願い致します。

 数式での変化は　Calculate イベントです。

 Private Sub Worksheet_Calculate()
     Application.EnableEvents = False
        Cells(7, 14).Resize(2).Copy
        Cells(7, 8).PasteSpecial xlPasteValues
    Application.EnableEvents = True
 End Sub
 (seiya)

 今までのご説明では、何が省略されていて、何が現実なのか分かりませんが、
 「ヒントがあれば十分対応できる」との印象を受けますので、以下その積りです。

 Changeイベントのトリガーはここではなく(つまり「N2:N70」ではなく)、「B8」か「F4」の数式では無い方のセルだと思います。
   　　　　　　　　　　　　　↓
 ＞    If Target.Row = 1 Or Target.Row > 70 Then Exit Sub
 ＞    If Target.Column <> 14 Then Exit Sub

 (半平太)

 seiyaさん、半平太さん、有難う御座います。
 半平太さんからのご指摘の様に入力シートの設定を書くのを怠って居た為、お分かり頂けなかった点も
多々あったと思います。根が怠け者のせいで、申し訳ありません。概略を下に書き込みましたのでアドバイス
お願い致します。

 入力用シートの概略
　　 BC     D     　E    　F      　G       H       I   　J     K      L
 6 検索値 発注先名 番号 発注品名　サイズ色 発注番号 発注数 単位  単価   金額
  (リスト)
 B列の各行に､=IF($B8="","",LEFT($B8,FIND("　",$B8,1)-1))
 C列の各行に､=IF($B8="","",REPLACE(B8,1,FIND("　",$B8,1),""))を設定しました。
 これは､「具体的な商品名を書いてならない」と言う規則があり､例えば､トンボ鉛筆とは書けないためです。
 このため､鉛筆　HB：黒　と言うように書き､検索が的確に行われるよう検索値の値を発注品名＋色＋特徴に
 しています。
 H､J,Kの各列には､=IF($B8="","",VLOOKUP($B8,係名!$A$2:$D47,3,FALSE))とVlookUp関数を書き込み
 検索値により検索範囲から抽出した値が入ります。
 これらH,J,kの値は検索範囲が変わっても＃N/Aと表示されては困るのです。そこでN､PそしてQ列にVlookUp
 関数を配置し､H,J,kには、N,P,Qの値だけを写せばと考えました。
 (なお、現在は､入力を行う事業所毎に検索値､検索範囲を変更しておりますが､前に､ご指摘がありましたように、
将来は､発注先毎に検索値､検索範囲を変更しようと考えております。)
 seiyaさんにお示し頂きましたコード、動いております。これを自分の設定に合うよう改変して､いろいろやっています。
 また､一歩実現に近づいた、ウッシシと一人ヤニ下がっています。

 想定と載せて下さったサンプルが違うようですので
 使えないかもしれませんが、一応作ったのを載せておきます。
	[A]	[B]	[C]	[D]	[E]	[F]	[G]
[1]	範囲	のみもの			くだもの	記号	単価
[2]	検索値	記号	単価		りんご	イ	100
[3]	りんご	イ	100		みかん	ロ	30
[4]	お茶	ア	80		なし	ハ	120
[5]							
[6]					のみもの	記号	単価
[7]					お茶	ア	80
[8]					コーヒー	カ	100
[9]					ジュース	サ	90
 E2:G4 は「くだもの」
 D7:G9 は「のみもの」と
 それぞれ名前の定義がしてあります。

 B1セルは定義してある名前の中から選び
 A3:A5のセルで、B1セルで定義した名前の
 先頭列の中から選びます。

 B3:B5セル以降に記号を
 C3:C5セル以降に単価を
 それぞれ参照します。

 シートモジュールに
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A3:A5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    With Target
        .Offset(, 1).FormulaR1C1 = "=VLOOKUP(RC1,INDIRECT(R1C2),2,FALSE)"
        .Offset(, 2).FormulaR1C1 = "=VLOOKUP(RC1,INDIRECT(R1C2),3,FALSE)"
        .Offset(, 1).Resize(, 2).Value = .Offset(, 1).Resize(, 2).Value
    End With
Application.EnableEvents = True
End Sub

 例えばC3セルには
=VLOOKUP($A3,INDIRECT($B$1),2,FALSE)
 の式を入れますので、この式をC3セルに入力する行為を
 マクロの記録にとりますと
 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,INDIRECT(R1C2),2,FALSE)"
 のコードが出来ていますので、これをActiveCellではなく
 Targetセルの、一つ右のセル Offset(, 1) に入るようにしています。
 この式を入れて、計算させた後　値に変更しています。

 ご参考に。

 そうそう、お名前は（）の中に書いて下さい。
 タイトルの直ぐ後ろに　<nori>　の様に<>で書いて居られるので
 名前と見なされて居ないようですよ。

 (HANA)

 昨晩書き込んだ内容に間違いがありましたので訂正させていただきます。
 B列の各行に､=IF($B8="","",LEFT($B8,FIND("　",$B8,1)-1))がB列ではなく､F列
 C列の各行に､=IF($B8="","",REPLACE(B8,1,FIND("　",$B8,1),""))がC列ではなく､G列です。
 御免なさい。宜しくお願い致します。
 HANAさん、アドバイス有難う御座います。複写して､試しております。

 私も、説明文に間違いが有りました・・・。

 ＞=VLOOKUP($A3,INDIRECT($B$1),2,FALSE)
 の式を入れるのは、C3セルではなく、B3セルでした。
（VLOOKUP関数の列番号が違うだけですけどね。）

 また、コード実行中に途中で止まって
 そのまま終了させた場合
 以降でA3:A5セルの値を変更しても
 コードが実行されない場合が有ります。

 その様な時は
Sub 戻す()
    Application.EnableEvents = True
End Sub
 を実行して下さい。

 (HANA)

 HANAさん、有難う御座います。
 いろいろなことに手を出し、頭がコンガラカッテおります。いま少し時間を下さい。
 このスレッドの最初の書き込みに対する処理として､皆様から教えていただいたことを捻くり
 まわし､下の様に書き込みました。
 これはこれで､動いているようなのですが・・・これで問題なければ､VlooUpで抽出した値を
 狙ったセルに入れることは成功か？？？と考えておりますが､甘いですか？
 Private Sub Worksheet_Calculate()
    Dim ActRow As Long
    ActRow = ActiveCell.Row
    Application.EnableEvents = False
        Cells(ActRow, 14).Copy
        Cells(ActRow, 8).PasteSpecial xlPasteValues
        Cells(ActRow, 15).Copy
        Cells(ActRow, 10).PasteSpecial xlPasteValues
        Cells(ActRow, 16).Copy
        Cells(ActRow, 11).PasteSpecial xlPasteValues
    Application.EnableEvents = True
    Application.CutCopyMode = False
　　'ここにApplication.EnableEvents = Falseを入れる必要は？
End Sub

 > 'ここにApplication.EnableEvents = Falseを入れる必要は？
 必要か？と聞かれればご本人の判断です。
 それを記述することによって、何を期待するのでしょう？
 (seiya)

 seiyaさん、お世話になります。
 ブックの動作が何か不自然なのです。テストの為に他のシートに書き込んだり､消したり
しているのですが､その度に､データシートを見に行くのか、プルプルと震えるような動き
をするので､関係しているのかと考え､後で試すための備忘のため書き込んでおきました。
自分用です。御免なさい。

 Calculate イベントはシートが再計算する度に実行されます。

 Application.EnableEvents = False
 Application.ScreenUpdating = False　　'<- 追加

 Application.EnableEvents = True
 Application.Screenupdating = True     '<- 追加

 してみてください
 (seiya)

 実際になさりたいことが分かっていません。
 以前からのご質問の続きとして、私が載せたレイアウトで言うと
 「数式を直接入れていた場合、B1セルの値が変わると
 　B4:C4がエラーになるから　値化して計算されない様にしたい」
 と言う事で有れば　どこかに有る数式の計算結果を貼り付けるにしても
 Worksheet_Changeイベントにしておくのが良いと思います。

 現在のコードで
 ＞これはこれで､動いているようなのですが・・・
 と言う事で有れば、現在のご希望は叶えられているのでは無いでしょうか？
 今回の条件でのみ使用するので有れば使用出来ると思います。

 ただ、私が想像する　前回の話の続きとして使用する事を
 考えて居られるなら、かなり不安なコードだと思います。

 たとえば、数量を入れますよね？
 数値を打ち込み、Enterを押します。
 数式が入っているので、再計算されます。
 再計算されるので、コードが実行されます。

 １．このタイミングで実行されても良いですか？
 　　　たとえば、入力が有る程度終わってから「やっぱり個数を増やそう」
 　　　と思って変更した場合、B1セルの値が何に成っているかは分かりません
 ２．Enterを押すと、通常はアクティブセルは下方向へ移動します。
 　　　この移動した後の行に、値貼り付けをするので良いのですか？
 　　　・・・まぁ、アクティブセルの移動方向を変更しておく
 　　　と言う手も有るかもしれませんが。

 勿論、「前回までの話とは切り離して考える」と言う事で有れば
 私の考えは不要な事かもしれません。
 その際は、その様に仰って下さいね。
 いろいろな書き込みが有ると、混乱の元になるでしょうから。

 (HANA)

 seiyaさん、HANAさん、お世話になります。この2日､東京を離れておりました。御返事送れて
失礼致しました。
 HANAさんに教えていただいたこと､現在の自分の作っているモノに当てはめるべく、色々､やっ
ておりますが､VBAを理解していない悲しさ、上手くいっておりません。もう少し時間を下さい。
 seiyaさんに教えていただいたコードを書き込んでみました。おかしな動作は無くなったので
すが､業者毎に振り分けるところでコピーできずエラーが出てしまい､何故なのか調べておりま
す。
 本当に難しいものですね！　チョッと離れていただけで、どこから手を付ければ良いのかさえ、
判らなくなってしまいます。解決したときの快感を考え､頑張ります！

 HANAさん、seiyaさん、おはよう御座います。
 昨晩は､疲れて寝てしまったのですが､夜中に目が覚めてしまい､起き出して､
いろいろやっている内に、エラーも無く、動くようになりました。
 以下に現在までのコードを書き込みました、ダラダラとウザッタイのは我慢し
てください。
 これをベースに一つ一つ直してゆけば・・・と考えております。
 ここはこうやるべき、ここは拙い直すべきと言うようなこと､お気づきになられ
ましたらご指摘ください､宜しくお願い致します。

 Private Sub CommandButton1_Click()

 Dim LastRow1 As Long  '元データの最終行
 Dim LastRow2 As Long  '作業用シート「Temp」のデータの最終行
 Dim LastRow3 As Long
 Dim SheetCheck As Integer
 Dim i As Integer
 Dim Rng As Range
 Dim Sh As Worksheet
 Dim WS1 As Worksheet
 Dim WS2 As Worksheet

 Set WS1 = Sheets("定番用入力シート")

 Application.ScreenUpdating = False '画面の更新を停止

 '作業用シートの挿入
 Worksheets.Add(after:=WS1).Name = "Temp"
 Set WS2 = Sheets("Temp")

 'データの最終行を取得
 LastRow1 = WS1.Range("D65536").End(xlUp).Row

 '重複する業者名を除いて作業用シート「Temp」に抽出
 WS1.Range("D8:D" & LastRow1).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=WS2.Range("A1"), _
    Unique:=True

 '作業用シート「Temp」のデータの最終行を取得
 LastRow2 = WS2.Range("A65536").End(xlUp).Row

 '抽出した各業者毎に処理を繰り返す
 For Each Rng In WS2.Range("A2:A" & LastRow2)

    '業者名のシートの有無をチェック
    SheetCheck = 0
    For Each Sh In Worksheets
        If Sh.Name = Rng.Value Then
            SheetCheck = 1
            Exit For
        End If
    Next Sh
    If SheetCheck = 1 Then  '業者名のシートがあった場合
        '業者名シートの入力されているデータの最後を求める
        LastRow3 = Sheets(Rng.Value).Range("B65536").End(xlUp).Row
        '業者名毎にデータを抽出
        WS1.Range("D8").AutoFilter Field:=3, Criteria1:=Rng.Value
        '抽出したデータに対応する業者名のシートにデータをコピー
        WS1.Range("F8:L" & LastRow1).Copy
        Sheets(Rng.Value).Range("B" & LastRow3 + 1).PasteSpecial Paste:=xlPasteValues

    Else  '業者名のシートがなかった場合
        '業者名のシートを挿入
        Worksheets.Add(after:=Sheets(Sheets.Count)).Name = Rng.Value
        '業者名毎のデータの抽出
        WS1.Range("D8").AutoFilter Field:=3, Criteria1:=Rng.Value
        '抽出したデータに対応する業者名のシートにデータをコピー
        WS1.Range("F8:L" & LastRow1).Copy
        Sheets(Rng.Value).Range("B8").PasteSpecial Paste:=xlPasteValues

    End If

        WS1.Range("D8").AutoFilter  'オートフィルタの解除

 Next Rng
 Application.DisplayAlerts = False  '警告メッセージの表示を無効
 WS2.Delete  '作業用シートの削除
 Application.DisplayAlerts = True  '警告メッセージの表示を有効
 Application.ScreenUpdating = False '画面の更新を有効

 End Sub

 Private Sub CommandButton2_Click()
    Dim LastRow As Long
    Dim WS1 As Worksheet
    Set WS1 = Sheets("定番用入力シート")

    LastRow = WS1.Range("B65536").End(xlUp).Row
    Range("B8:D" & LastRow).ClearContents
    Range("H8:L" & LastRow).ClearContents

 End Sub

 Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ActRow As Long
    ActRow = ActiveCell.Row
    Application.EnableEvents = False
    Application.ScreenUpdating = False
        Cells(ActRow, 14).Copy
        Cells(ActRow, 8).PasteSpecial xlPasteValues
        Cells(ActRow, 15).Copy
        Cells(ActRow, 10).PasteSpecial xlPasteValues
        Cells(ActRow, 16).Copy
        Cells(ActRow, 11).PasteSpecial xlPasteValues
    Application.EnableEvents = True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
 End Sub

 HANAさん､仰っていること､ぼんやりですが､分かって来ました。
 現在､示唆していただいた方向に作り変えております。

 ＞現在､示唆していただいた方向に作り変えております。
 と言うことですが、↑で既に　ワークシートチェンジイベントを
 お使いのようですが・・・。

 まぁ、書き方は問題が有りそうですが・・・。
 例えば「何処のセルの値が変わった時に実行したいのか」や
 「どの場所のセルに対して実行を行いたいのか」等です。

 具体的には、最初にnoriさんが載せられたコードですと
 ＞   If Target.Row = 1 Or Target.Row > 70 Then Exit Sub
 ＞   If Target.Column <> 14 Then Exit Sub
 とか言った辺りです。

 その他の部分に関しては、どの様な事をなさりたいのか
 分かりませんので、見てません。

 (HANA)

 結局何をしたいのでしょう？
 思ったように機能しないコードを提示されても理解できませんよ？
 何をどうしたときに何をどうしたいのか、わかるようにを説明してください。
 (seiya)

 HANAさん、seiyaさん、半平太さん、またまた､分からなくなりました。教えてください。
 下のコードは､教えていただいたコードを自分のプログラムに合うように変更したものです。
 何がしたいのか？　今までも何度か説明させていただきましたが､ここで､改めて説明させていただきます。
 A列には番号
 B列には注文したい品名が表示されるリスト（入力規則）
 C列には注文先の業者名が表示されるリスト（入力規則）
 D列にはB列で選択された品名（文字列）を分割し、その前半分を表示する為の式
 E列にはB列で選択された品名（文字列）を分割し、その後半分を表示する為の式
 F（色､サイズ等の情報）､G（カタログ名及びページ数等）､H（注文数）､I（単位）､J（単価）､K（合計）列は
 起動時は空白、M､N､O列には､=IF($B8="","",VLOOKUP($B8,INDIRECT($F$2),2,FALSE))の様な式が
 配置されており､此処で抽出された値だけをG､I､Jにペーストする事を考えております。
 何故この様にするかと言うと、ある条件を満たすためVlookUpの第2引数（検索範囲）を変更せざるを得ないか
 らです。検索範囲を変更してしまうと､当然のことながら､VLOOKUPで先に抽出して表示されていたセルの表示
 が＃N/Aになってしまいます。これを避けるために､B列で注文したい品名が選択されたならば､VLOOKUPで抽出
 された値だけをG，I，Jの各列にコピーしてしまえば良いと考えた次第です。
 さて、質問であり､教えていただきたいことなのですが､上のことを実現するために、target,range("B8:B77")
 を設定するとコピーペーストがなされなくなり､考えていることが実現されません。
 ところが､If myData 以下 exit sub end ifをコメントアウトすると､思った通りの動作をしてくれます。
 何処が悪く､何処をどの様に直せば良いかを教えてください。宜しくお願い致します。

 Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ActRow As Long
    Dim myData As Range

    Set myData = Application.Intersect(Target, Range("B8:B77"))
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If myData Is Nothing Then
    ~~~~~~~~~~~~~~~~~~~~~~~~~
    Exit Sub
    ~~~~~~~~
    ActRow = ActiveCell.Row
    Application.EnableEvents = False
    Application.ScreenUpdating = False

        Cells(ActRow, 13).Copy
        Cells(ActRow, 7).PasteSpecial xlPasteValues
        Cells(ActRow, 14).Copy
        Cells(ActRow, 9).PasteSpecial xlPasteValues
        Cells(ActRow, 15).Copy
        Cells(ActRow, 10).PasteSpecial xlPasteValues

    End If
    ~~~~~~
    Application.EnableEvents = True
'    Application.CutCopyMode = False
    Application.ScreenUpdating = True

 End Sub

 ブレークポイントの設定を行ったことが有りますか？

 コードを書いている白い部分の左側に、灰色の部分が有りますよね。
 その部分へポインターを持っていくと、ポインターが矢印に変わります。
 そこをクリックすると、茶色い●がつき　同じ行に茶色の色がつきます。

 これを設定しておくと、コードが実行されたとき　この行で一旦止まります。

 今回の場合は先頭行
 ＞Set myData = Application.Intersect(Target, Range("B8:B77"))
 に設定して於いて良いと思います。

 また、ローカルウィンドウも表示しておくのが良いと思います。
 これは、VBEのメニュー・表示(V)→ローカルウィンドウ(S) から表示出来ます。

 B8:B77のセルの値が変わったとしましょう。
 先ほど●をつけた部分でコードが止まります。
 [ F8 ]キーを押して、その行を実行しましょう。

 ローカルウィンドウの myData に情報が入ります。
 コード内の myDataの上にポインタを移動させてください。
 しばらく待つと、myDataの中に入った値が表示されます。

 もう一度[ F8 ]キーを押します。
 ＞If myData Is Nothing Then
 の判定部分ですが
 myData は Nothing ではないので、
 更に[ F8 ]キーを押すと
 End If に移動します。

 ・・・ここまで移動しては駄目ですよね？

 この「End IF」は、Nothing だった時の処理を
 終わらせる位置に無いといけませんよね。

 (HANA)

 HANAさん、お世話になります。
 教えていただいた方法で、色々試した結果､下の様になりました。
 何故、この様にしなければならないのかについては､未だにウ～ン
 状態です。
 動いているのですが､これで間違いは無いでしょうか？

 Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ActRow As Long
    Dim myData As Range
    Set myData = Application.Intersect(Target, Range("B8:B77"))

    ActRow = ActiveCell.Row
    Application.EnableEvents = False
    Application.ScreenUpdating = False

        Cells(ActRow, 13).Copy
        Cells(ActRow, 7).PasteSpecial xlPasteValues
        Cells(ActRow, 14).Copy
        Cells(ActRow, 9).PasteSpecial xlPasteValues
        Cells(ActRow, 15).Copy
        Cells(ActRow, 10).PasteSpecial xlPasteValues

    If myData Is Nothing Then Exit Sub
    End If
    Application.EnableEvents = True
'    Application.CutCopyMode = False
    Application.ScreenUpdating = True

 End Sub

 目的のことを考えると、間違っていると思います。

 いや、そもそも　このコードは動きませんよね？
 実行すると
「コンパイルエラー
　End If に対応する If ブロックがありません」
 と出ませんか？

 ＞    If myData Is Nothing Then Exit Sub
 と一行で書くなら、その下の　End If は不要です。
 もしも、
    If myData Is Nothing Then
        Exit Sub
 と二行で書くなら、その下に End If が必要です。

 処理の流れを考えてみましょう。
 noriさんだったら　どうしますか？
 私だったら
 　１．変更が有ったセルが、B8:B77の範囲に含まれているか調べる
 　２．含まれていない場合は　処理を終了する
 　３．含まれている場合は、各セルをコピーして所定のセルに貼り付ける
 この様な作業をすると思います。

 或いは
 　１．変更が有ったセルが、B8:B77の範囲に含まれているか調べる
 　２．含まれている場合は
 　　　　　各セルをコピーして所定のセルに貼り付ける
 　（含まれていない場合は、何もしません。）
 です。

 noriさんのコードの流れは
 　１．変更が有ったセルが、B8:B77の範囲に含まれているか調べる
 　２．　　各セルをコピーして所定のセルに貼り付ける
 　３．含まれていない場合は　処理を終了する
 と成っています。

 これでは、どのセルを変更しても
 コピー貼り付けの作業が行われてしまいますよね？

 (HANA)

 HANAさん、実際には、下のように書き込んでいたのですが、間違って、テスト用のシートに
書き込んだモノをコピー＆ペーストしてしまいました。

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ActRow As Integer
    Dim myData As Range
    Set myData = Application.Intersect(Target, Range("B8:B77"))
    ActRow = ActiveCell.Row

    If myData Is Nothing Then Exit Sub

    Application.EnableEvents = False
    Application.ScreenUpdating = False
        Cells(ActRow, 13).Copy
        Cells(ActRow, 7).PasteSpecial xlPasteValues
        Cells(ActRow, 14).Copy
        Cells(ActRow, 9).PasteSpecial xlPasteValues
        Cells(ActRow, 15).Copy
        Cells(ActRow, 10).PasteSpecial xlPasteValues
    Application.EnableEvents = True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

 End Sub

 ＞実際には、下のように書き込んでいたのですが、間違って、テスト用のシートに
 ＞書き込んだモノをコピー＆ペーストしてしまいました。
 勘弁して下さい。（笑）

 一つ気になるのは
 ＞ActRow = ActiveCell.Row
 って所ですかね。

 まぁ、入力規則から選ぶので　ActiveCell = Target と思いますが
 この様な場合以外でも使うことを考えると
 【変更が有ったセルの行】に貼り付けるのですから
 = Target.Row
 としておくのが自然な気がします。

 通常は、入力をして確定（Enter）しますよね？
 すると、変更したセルとは違うセルがアクティブに成ると思いますので。

 あとは、現在のコードでも問題無いのでしょうけど　コピーペーストが３回有りますよね。
 もしも、
 　　M列に　現在の式
 　　N列に　=IF(Hのセル="","",Hのセル)
 　　O列に　N列に入っていた式
 　　P列に　O列に入っていた式
 の様に設定しておけば、M:Pをコピーして、G列から貼り付けるだけ
 （コピーペーストが一回）になるので少しは処理が早くなると思います。
（現在、気になるほどの事は無いでしょうから、早くなっても分からないと思いますが。）

 (HANA)

 HANAさん、本当にお世話になりました。教えていただいたこと、早速、書き込みます。
これから、業者毎に検索値、検索範囲を設定できるようにしてみようと考えております。
ありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20080612101317]] 『数式の値だけを参照する方法ってありますか』(nori) ページの最後に飛ぶ

[[20080612101317]]　『数式の値だけを参照する方法ってありますか』(nori) 　ページの最後に飛ぶ