Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


4612 / 13646 ツリー ←次へ | 前へ→

【55571】実行範囲の指定方法について marumaru 08/5/11(日) 20:07 質問[未読]
【55572】Re:実行範囲の指定方法について りん 08/5/11(日) 20:18 回答[未読]
【55575】Re:実行範囲の指定方法について kanabun 08/5/11(日) 21:22 発言[未読]
【55579】Re:実行範囲の指定方法について marumaru 08/5/12(月) 1:39 お礼[未読]
【55581】Re:実行範囲の指定方法について VBWASURETA 08/5/12(月) 10:01 発言[未読]
【55582】Re:実行範囲の指定方法について kanabun 08/5/12(月) 11:00 発言[未読]
【55583】Re:実行範囲の指定方法について VBWASURETA 08/5/12(月) 11:32 発言[未読]
【55584】Re:実行範囲の指定方法について kanabun 08/5/12(月) 11:57 発言[未読]
【55585】Re:実行範囲の指定方法について VBWASURETA 08/5/12(月) 12:22 発言[未読]
【55586】Re:実行範囲の指定方法について kanabun 08/5/12(月) 12:36 発言[未読]
【55588】Re:実行範囲の指定方法について VBWASURETA 08/5/12(月) 13:54 発言[未読]
【55589】Re:実行範囲の指定方法について VBWASURETA 08/5/12(月) 14:08 発言[未読]
【55590】Re:実行範囲の指定方法について kanabun 08/5/12(月) 14:17 質問[未読]
【55612】Re:実行範囲の指定方法について ichinose 08/5/13(火) 22:20 発言[未読]
【55613】Re:実行範囲の指定方法について kanabun 08/5/13(火) 22:42 発言[未読]
【55614】Re:実行範囲の指定方法について n 08/5/14(水) 1:19 発言[未読]
【55615】Re:実行範囲の指定方法について ひげくま 08/5/14(水) 8:12 発言[未読]
【55617】Re:実行範囲の指定方法について kanabun 08/5/14(水) 9:05 発言[未読]
【55632】Re:ご迷惑をかけました。 VBWASURETA 08/5/14(水) 12:15 発言[未読]

【55571】実行範囲の指定方法について
質問  marumaru  - 08/5/11(日) 20:07 -

引用なし
パスワード
   下記の内容を指定範囲内で実行したいのですが、どなたかご教授願います。

範囲=("A1:O10")

Cells.Find(What:="*担当者*", After:=ActiveCell, LookIn _
 :=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
 SearchDirection:=xlNext, MatchCase:=True).Activate
 Cells.FindNext(After:=ActiveCell).Activate

宜しくお願いします。

【55572】Re:実行範囲の指定方法について
回答  りん E-MAIL  - 08/5/11(日) 20:18 -

引用なし
パスワード
   marumaru さん、こんばんわ。
>下記の内容を指定範囲内で実行したいのですが、どなたかご教授願います。
>
>範囲=("A1:O10")
Findメソッドのヘルプの使用例をちょっと改造。

With Range("A1:O10")
  Set c = .Find("*担当者*", LookIn:=xlFormulas, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstAddress = c.Address
    Do
      c.Interior.ColorIndex = 5 'とりあえず青く塗ってみたり
      Set c = .FindNext(c) '検索を繰り返す
    Loop While Not c Is Nothing And c.Address <> firstAddress
  End If
End With

こんな感じです。

【55575】Re:実行範囲の指定方法について
発言  kanabun  - 08/5/11(日) 21:22 -

引用なし
パスワード
   >    Do
>      c.Interior.ColorIndex = 5 'とりあえず青く塗ってみたり
>      Set c = .FindNext(c) '検索を繰り返す
>    Loop While Not c Is Nothing And c.Address <> firstAddress

ヘルプのサンプルでは、そうなってますが (Excel2007のHelpでも)
Do〜Loop のなかで、
セルc の値を書き換えることがなければ、
> c Is Nothing
c が Nothing になることはありえないです。

Loop While c.Address <> firstAddress
とか、
Loop Until c.Address = firstAddress
とか、だけで
十分ですよ (^^

【55579】Re:実行範囲の指定方法について
お礼  marumaru  - 08/5/12(月) 1:39 -

引用なし
パスワード
   早々とご回答いただきまして、誠に有り難うございました。
早速、参考にさせていただきます。また、機会がありましたら
宜しくお願いします。

【55581】Re:実行範囲の指定方法について
発言  VBWASURETA  - 08/5/12(月) 10:01 -

引用なし
パスワード
   皆さんおはようございます。

すみませんkanabun さんにケチをつけるわけではないですが。
c Is Nothingはありえます。

Excelのバージョンかにもよるのもだと思いますが、
Findメソッドで見つからなかった場合にNothingを返す場合があります。

また、以下のこれについてはAndでしないほうがよさそうです。

>>    Loop While Not c Is Nothing And c.Address <> firstAddress


       If c.Address <> firstAddress Then Exit Loop
     Loop While Not c Is Nothing

とした方がエラーとならないです。
kanabun さんのおっしゃる通りにNothingを返さない場合があるからです。

【55582】Re:実行範囲の指定方法について
発言  kanabun  - 08/5/12(月) 11:00 -

引用なし
パスワード
   ▼VBWASURETA さん:
>Excelのバージョンかにもよるのもだと思いますが、
>Findメソッドで見つからなかった場合にNothingを返す場合があります。

はて?
Find で見つからなかった場合には Nothing が返るものと
これまで思っていましたが?
Versionに関係なく。

【55583】Re:実行範囲の指定方法について
発言  VBWASURETA  - 08/5/12(月) 11:32 -

引用なし
パスワード
   ▼kanabun さん:

Nothingはありえないと先に書かれてましたからですよ?
ま、整理するとFindメソッドは見つからなかった場合にNothingを返します。
FindNextメソッドについてもNothingが返ったり、アドレスで判定するなりになります。

ただバージョンでどちらとも言えないので、両方対応したのが先に記載した
ソースコードです。

【55584】Re:実行範囲の指定方法について
発言  kanabun  - 08/5/12(月) 11:57 -

引用なし
パスワード
   >  Loop While Not c Is Nothing And c.Address <> firstAddress
この HELPの使用例がまずい 理由を 簡単な事例で説明させていただきます。


Sub 検索でヒットしたセルを書き換えないばあい()
 Dim c As Range
 Dim 範囲 As Range
 Dim firstAddress As String
 
 Set 範囲 = Range("A1:O10")
 Set c = 範囲.Find(What:="担当者", _
    LookIn:=xlFormulas, LookAt:=xlPart)
 If Not c Is Nothing Then
   firstAddress = c.Address
   Do
     c.Interior.Color = vbYellow
     Set c = 範囲.FindNext(c)
   Loop Until c.Address = firstAddress
 End If
End Sub

Sub 検索でヒットしたセルを書き換えるばあい()
 Dim c As Range
 Dim 範囲 As Range
 
 Set 範囲 = Range("A1:O10")
 Set c = 範囲.Find("担当者", , xlFormulas, xlPart)
 If Not c Is Nothing Then
   Do
     c.Value = "発注者"
     Set c = 範囲.FindNext(c)
   Loop Until c Is Nothing
 End If
End Sub
★ここでヘルプの使用例のように
FindNext Loopの終了条件を
>  Loop While Not c Is Nothing And c.Address <> firstAddress
と書くとなぜまずいかをちょっと解説しときますと、
範囲内の 「担当者」を含むセルが、すべて「発注者」に置き換えられた
とき、
>  Set c = 範囲.FindNext(c)
c は Nothing が戻ります。

このとき
>  Loop While Not c Is Nothing And c.Address <> firstAddress
という文は 前半のLoop脱出条件 While Not c Is Nothing だけで
必要かつ十分であるにもかかわらず、Basic(VB6まで)は
2番目の条件 And c.Address <> firstAddress
をかならず評価します。
その結果、Nothing(どこにもない)もののアドレスを取得しようとして
致命的エラーとなります。

cなどでは
> Loop While Not c Is Nothing
で「False」と判定されたら、AND 以降はもう実行しないから
この手のエラーにはなりませんが。

さて、ヒットしたセルを書き換えるばあいでも、
>   Loop Until c Is Nothing
でなく、
>   Loop Until c.Address = firstAddress
をつかわなければならないケースもあります。
たとえば、検索文字列に 検索文字列を含んで文字連結したり(置換)するばあい
です。

Sub 検索でヒットしたセルを書き換えるばあい2()
 Dim c As Range
 Dim 範囲 As Range
 Dim firstAddress As String
 
 Set 範囲 = Range("A1:O10")
 Set c = 範囲.Find("りんご", , xlFormulas, xlPart)
 If Not c Is Nothing Then
   firstAddress = c.Address
   Do
     c.Value = Replace(c.Value, "りんご", "りんご(apple)")
     Set c = 範囲.FindNext(c)
   Loop Until c.Address = firstAddress
 End If
End Sub

★上の例は 単にまえの2つのsampleとの比較のためのsampleで
  実際は Replaceメソッドを使用して一括置換しちゃいますが。

【55585】Re:実行範囲の指定方法について
発言  VBWASURETA  - 08/5/12(月) 12:22 -

引用なし
パスワード
   ▼kanabun さん:

なるほど。ということは

>c が Nothing になることはありえないです。

と書かれててましたが、

>★ここでヘルプの使用例のように
> FindNext Loopの終了条件を
>>  Loop While Not c Is Nothing And c.Address <> firstAddress
>と書くとなぜまずいかをちょっと解説しときますと、
>範囲内の 「担当者」を含むセルが、すべて「発注者」に置き換えられた
>とき、
>>  Set c = 範囲.FindNext(c)
>c は Nothing が戻ります。

とNothingになり得るということですね。

【55586】Re:実行範囲の指定方法について
発言  kanabun  - 08/5/12(月) 12:36 -

引用なし
パスワード
   ▼VBWASURETA さん:
>なるほど。ということは
>
>>c が Nothing になることはありえないです。
>
>と書かれててましたが、

再掲します

>> Do〜Loop のなかで、
>> セルc の値を書き換えることがなければ、
>> c が Nothing になることはありえないです。

-------------------

> >>    Loop While Not c Is Nothing And c.Address <> firstAddress
>
> を
>        If c.Address <> firstAddress Then Exit Loop
>      Loop While Not c Is Nothing
>
> とした方がエラーとならないです。

これも FindNext(c) の「次のセル検索」結果 c に Nothing が
代入される可能性のあるときは
ヘルプの構文と同じ危険性を孕んでますね。

【55588】Re:実行範囲の指定方法について
発言  VBWASURETA  - 08/5/12(月) 13:54 -

引用なし
パスワード
   ▼kanabun さん:
>再掲します
>
>>> Do〜Loop のなかで、
>>> セルc の値を書き換えることがなければ、
>>> c が Nothing になることはありえないです。

セルcと言いますか参照先のシートセルですよね?

>
>-------------------
>
>> >>    Loop While Not c Is Nothing And c.Address <> firstAddress
>>
>> を
>>        If c.Address <> firstAddress Then Exit Loop
>>      Loop While Not c Is Nothing
>>
>> とした方がエラーとならないです。
>
>これも FindNext(c) の「次のセル検索」結果 c に Nothing が
>代入される可能性のあるときは
>ヘルプの構文と同じ危険性を孕んでますね。

因みに何故指摘したかと言いますと、この条件の前にりんさんのソースで
以前検証して、Nothingを返されたからです。

で、この条件ですが1行のソースを2つに別けるという意味だけを書いています。
       If c.Address <> firstAddress Then Exit Loop

因みにシートセル書き換えのVBAソースですがデバッグに落ちていません。

後、もし回答者に対して指摘する場合、質問者にも理解し易い
解説(今回のようなデバッグに落ちる解説)がないと
難しいと思われたので指摘させていただきました。

【55589】Re:実行範囲の指定方法について
発言  VBWASURETA  - 08/5/12(月) 14:08 -

引用なし
パスワード
   追記です。

以前作ったソースは↓これです。

//www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=53615;id=excel

ついでに見て気づいたので、記載ソース一点訂正です^^;

訂正箇所

  Dim firstAddress As Integer

ではなく

  Dim firstAddress As String

です。

【55590】Re:実行範囲の指定方法について
質問  kanabun  - 08/5/12(月) 14:17 -

引用なし
パスワード
   ▼VBWASURETA さん:
>因みに何故指摘したかと言いますと、この条件の前にりんさんのソースで
>以前検証して、Nothingを返されたからです。

こちらでも検証してみたいので、
Find でヒットして Do〜Loop に入って

>    Do
>      c.Interior.ColorIndex = 5 'とりあえず青く塗ってみたり
>      Set c = .FindNext(c) '検索を繰り返す
>    Loop While Not c Is Nothing And c.Address <> firstAddress

Set c = .FindNext(c) '検索を繰り返す
で、 Nothingが返される
実例がございまいたら、
ここに提示してもらえませんか?

Excel2007 を使ってますが、2000でも 2002でも 2003 でも
検証可能です。

【55612】Re:実行範囲の指定方法について
発言  ichinose  - 08/5/13(火) 22:20 -

引用なし
パスワード
   こんばんは。

>>因みに何故指摘したかと言いますと、この条件の前にりんさんのソースで
>>以前検証して、Nothingを返されたからです。

>Set c = .FindNext(c) '検索を繰り返す
>で、 Nothingが返される
>実例がございまいたら、
>ここに提示してもらえませんか?

これは、「備えあれば憂いなし」という事を言っているのだと思いますよ!!

特に私のようにFindメソッド回りのロジックなど一度作成したら、
再利用可能なプロシジャーにしておいて、それを利用したい。

という考え方だと、

>Set c = .FindNext(c) 

で、 Nothingが返されることをチェックするコードは入れなくては
なりませんねえ!!


例えば、

Book1.xlsのThisworkbookのモジュールに

'=======================================================
Option Explicit
Public WithEvents sht As Worksheet
'=========================================================
Private Sub sht_SelectionChange(ByVal Target As Range)
  Target.Value = ""
End Sub


book2.xls(sheet1というシートが存在するブック)の標準のジュールに
'==========================================================
Option Explicit
'==========================================================
Sub main()
  Worksheets("sheet1").Activate
  mk_sample
  With CreateObject("excel.application")
    .Visible = True
    With .Workbooks.Open(ThisWorkbook.Path & "\book1.xls")
     Set .sht = ThisWorkbook.Worksheets("sheet1")
     End With
    End With
  AppActivate Application.Caption
End Sub
'==========================================================
Sub mk_sample()
  Cells.Delete
  Range("a2:a4").Value = "aaa"
  Range("a10").Value = "aaa"
  Range("a1").Select
End Sub
'==========================================================
Sub Test()
  Dim c, firstAddress
  With Range("A2:a10")
    Set c = .Find("aaa", after:=Range("a10"), LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not c Is Nothing Then
      c.Select
      firstAddress = c.Address
      Do
       c.Interior.ColorIndex = 5 'とりあえず青く塗ってみたり
       Set c = .FindNext(c) '検索を繰り返す
      Loop While c.Address <> firstAddress
     End If
  End With
End Sub

として、Book1.Xls Book2.Xlsは、同一フォルダ上に保存してください。

一度、全てを閉じた後、Book2.Xlsのみ起動してください。

まず、mainを実行してください。

次いで問題の Testを実行して見てください。

私が試した限りでは、無限ループに陥ります(Excel2002で確認)。

こんなコードにすることがよくない と思われるかもしれません。

が、Findメソッドを使って汎用的(再利用可能)なコードを作成しようと
考えた時には上記のような事象にも対応するコード、

つまり、

>Set c = .FindNext(c) '検索を繰り返す

で、 Nothingが返される
ことを想定したコードが必要かと思います。

よって、Nothingチェックが無駄だとは一概には言えないと思いますよ!!

【55613】Re:実行範囲の指定方法について
発言  kanabun  - 08/5/13(火) 22:42 -

引用なし
パスワード
   失礼します。

▼ichinose さん:
>次いで問題の Testを実行して見てください。
>
>私が試した限りでは、無限ループに陥ります(Excel2002で確認)。
>


よく読んでなくて申し訳ないのですが、

>      c.Select

この一行は何のためにあるのでしょう?

【55614】Re:実行範囲の指定方法について
発言  n  - 08/5/14(水) 1:19 -

引用なし
パスワード
   ▼kanabun さん:
こんにちは。
途中からちょっとごめんなさい。

>Set c = .FindNext(c) '検索を繰り返す
>で、 Nothingが返される
>実例がございまいたら、
>ここに提示してもらえませんか?
検索範囲内で1回のみhitする場合で、
かつそのセルが縦方向の結合セルの場合、が該当します。

その場合
>その結果、Nothing(どこにもない)もののアドレスを取得しようとして
>致命的エラーとなります。
これは発生しますね。

なので

  If c Is Nothing Then Exit Do
Loop While c.Address <> firstAddress

こう書くようにしている...
...と他の方が書かれていたのを他の掲示板で見た記憶があります。

【55615】Re:実行範囲の指定方法について
発言  ひげくま  - 08/5/14(水) 8:12 -

引用なし
パスワード
   横から失礼します。

>検索範囲内で1回のみhitする場合で、
>かつそのセルが縦方向の結合セルの場合、が該当します。

あっ!ホントだ!
知らなかったぁ・・・
今後の参考にさせていただきます。
ありがとうございました。

【55617】Re:実行範囲の指定方法について
発言  kanabun  - 08/5/14(水) 9:05 -

引用なし
パスワード
   ▼n さん:
>>Set c = .FindNext(c) '検索を繰り返す
>>で、 Nothingが返される
>>実例がございまいたら、

>検索範囲内で1回のみhitする場合で、
>かつそのセルが縦方向の結合セルの場合、が該当します。
>
確認しました。
事例の紹介ありがとうございます。

そうなると
  If c Is Nothing Then Exit Do
のチェック必要ですね。

...それにしても、またもや、結合セルですか〜

【55632】Re:ご迷惑をかけました。
発言  VBWASURETA  - 08/5/14(水) 12:15 -

引用なし
パスワード
   皆さんおはようございます。

色々皆さんからのフォローありがとうございました。

元々Nothingが発生したのは以下のURL書いた問題で発生しましたが、
りんさんのソースにプラスαシートの値を変えるロジックで発生していました。
結局は自分のソースもダメなようですね^^;
すみません・・・

//www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=55589;id=excel

で、数日間考えたのですが、指摘したこととやっぱりケチをつけていたのかなっと
反省しています。

でも、何故指摘したかというと2点あるのですが、
1つは、質問と意図した箇所の指摘ではないこと。
 質問本来の範囲という意味での動作はりんさんの回答で、できますからね。
 問題となる箇所は作り次第で質問する側が指摘するところ?かなと思いました。

2つ目はりんさんのソースとヘルプについてダメ出ししているように
 見えたからです。自分だけかもしれませんが^^;
 りんさんは、ヘルプを見れば色々わかりますよという意味で
 書いてたかと思うのですが、指摘によって、ヘルプとりんさんの
 ソースが使い物にならないようなダメ出しに見えたというところです。


そいうところから話の腰を折ってしまいました事をお詫び致します。
申し訳ございませんでした。

4612 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free