Excel VBA質問箱 IV

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

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


3393 / 13645 ツリー ←次へ | 前へ→

【62524】inputbox false で 終了しない チューリップ 09/7/28(火) 19:42 質問[未読]
【62525】Re:inputbox false で 終了しない kanabun 09/7/28(火) 20:09 発言[未読]
【62528】Re:inputbox false で 終了しない チューリップ 09/7/28(火) 22:04 お礼[未読]
【62527】Re:inputbox false で 終了しない kanabun 09/7/28(火) 21:57 発言[未読]
【62529】Re:inputbox false で 終了しない チューリップ 09/7/28(火) 22:06 お礼[未読]
【62531】Re:inputbox false で 終了しない チューリップ 09/7/28(火) 23:51 質問[未読]
【62532】Re:inputbox false で 終了しない kanabun 09/7/29(水) 9:07 発言[未読]
【62553】Re:inputbox false で 終了しない チューリップ 09/7/29(水) 18:14 お礼[未読]
【62534】Re:inputbox false で 終了しない yoshi 09/7/29(水) 10:51 回答[未読]
【62537】Re:inputbox false で 終了しない kanabun 09/7/29(水) 12:07 発言[未読]
【62539】Re:inputbox false で 終了しない yoshi 09/7/29(水) 12:48 回答[未読]
【62545】Re:inputbox false で 終了しない kanabun 09/7/29(水) 14:44 発言[未読]
【62554】Re:inputbox false で 終了しない チューリップ 09/7/29(水) 18:16 お礼[未読]

【62524】inputbox false で 終了しない
質問  チューリップ  - 09/7/28(火) 19:42 -

引用なし
パスワード
   こんばんは、何度も質問してすみません。
今回もよろしくお願いします。

元の表:

       果物名
10001    キウィ
10002    りんご
10003    いちご
10004    ぶどう
10005    バナナ
10003    いちご
10003    いちご
10008    すいか
10002    りんご
10002    りんご
10008    すいか

で、果物名を他のものに書き変えたい時、一気にできるように下記の
コードを作りました。

Sub 品書き換え()
Dim top As Integer
Dim botm As Long
Dim data As Integer
Dim msg1 As String
Dim msg2 As String

Application.ScreenUpdating = False
Sheets("sheet1").Activate
  If ActiveSheet.AutoFilterMode = True Then
    Range("A2").Select
    Selection.AutoFilter
  End If
  
    msg1 = InputBox("修正したい果物を入力してください" _
    & Chr(13) & "先頭の文字のみ入力でかまいません", "果物の検索", , 100, 100)
    If msg1 = "False" Then Exit Sub
    
    With Worksheets("sheet1")
    
      Range("A2").Select
      Selection.AutoFilter Field:=2, _
      Criteria1:=msg1 & "*"
  
    With .AutoFilter.Range
      top = .Offset(1).SpecialCells(xlCellTypeVisible).Row
      
      If Worksheets("sheet1").Range("B" & top).Value = "" Then
        MsgBox "該当するものがありません" _
        & Chr(13) & "再度検索してください", vbOK, "データ"
        Selection.AutoFilter
        Worksheets("sheet1").Select
      
        Exit Sub
    
      Else
        msg2 = InputBox("修正後の果物を入力してください" _
        & Chr(13) & "例)いちご", "果物の修正", , 100, 100)
        If msg2 = "False" Then Exit Sub
       
        botm = Cells(Rows.Count, 1).End(xlUp).Row
        For data = top To botm
           If Worksheets("sheet1").Rows(data).Hidden = False Then
           Range("B" & data).Select
           Selection.Value = msg2
           End If
        Next data
      End If
      
    End With
    End With

End Sub


上記のように書くと、果物名をいれたときは間違いなく動くのですが、
inputboxのキャンセルボタンを押した場合、ムシされて
そのまますべての値を空欄に書き換えてしまいます。

Inputboxは二つ出てきていますが、どちらもキャンセルがムシされます。

どこがおかしいのかご指摘いただけませんでしょうか?

【62525】Re:inputbox false で 終了しない
発言  kanabun  - 09/7/28(火) 20:09 -

引用なし
パスワード
   ▼チューリップ さん:

>inputboxのキャンセルボタンを押した場合、ムシされて

一つ目は、以下の部分ですね?

>Dim msg1 As String
>
>    msg1 = InputBox("修正したい果物を入力してください" _
>    & Chr(13) & "先頭の文字のみ入力でかまいません", "果物の検索", , 100, 100)
>    If msg1 = "False" Then Exit Sub

InputBox には InputBox関数と Application.InputBoxメソッドの2つあり、
このばあいは InputBox関数のことですね?

InputBox関数では戻り値はすべて文字列になります。
(そういう意味で
> Dim msg1 As String
 と変数を宣言しているのは正しいです。)
で[Cancel]ボタンが押された時は、"False"は返りません。
"" という長さ0の文字列でもありません。
戻り値msg1 を格納する「アドレスがない」状態が返るのです。
ちょっと難しいですが、
文字列変数のアドレス(これをポインタといいます)は
StrPtr()という関数で調べることができます。
結局、
>    If msg1 = "False" Then Exit Sub
ではなく、
    If StrPtr(msg1) = 0 Then Exit Sub
という風に判別します。

【62527】Re:inputbox false で 終了しない
発言  kanabun  - 09/7/28(火) 21:57 -

引用なし
パスワード
   ▼チューリップ さん:

余計なお世話かもしれませんが、
そのプロシージャ、全文はこんな風に書けると思います。

Sub 品書き換え()
 Dim msg1 As String
 Dim msg2 As String

 With Worksheets("sheet1")
  If .FilterMode Then
    .AutoFilterMode = False
  End If

  msg1 = InputBox("修正したい果物を入力してください" _
    & vbCr & "先頭の文字のみ入力でかまいません", _
    "果物の検索", , 100, 100)
  If StrPtr(msg1) = 0& Then Exit Sub
  
  ''AutoFilter.Rangeは明示的に指定しましょう
  ([A2].Select のような Excelの自動範囲検索機能に頼らない)
  With .Range("A2").CurrentRegion 
    .AutoFilter Field:=2, Criteria1:=msg1 & "*"
  
    If .Columns(1).SpecialCells(xlVisible).Count < 2 Then
      MsgBox "該当するものがありません" _
       & vbCr & "再度検索してください", , "データ"
  
    Else
     msg2 = InputBox("修正後の果物を入力してください" _
     & vbCr & "例)いちご", "果物の修正", , 100, 100)
     If StrPtr(msg2) = 0& Then Exit Sub
     Intersect(.Columns(2), .Offset(1)).Value = msg2
    End If
    .AutoFilter
  End With
 End With

End Sub

【62528】Re:inputbox false で 終了しない
お礼  チューリップ  - 09/7/28(火) 22:04 -

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

早速のご回答ありがとうございます。

>戻り値msg1 を格納する「アドレスがない」状態が返るのです。
の意味がちょっと難しいですが、StrPtrでググってみると、
でるわでるわ・・・初心者は同じとこで躓くんですね(笑)

で、cancelボタンがFalseを返すのは、Application.InputBoxメソッドを
使用した場合に限られるということなんですね。

わかりやすい御説明ありがとうございました。
教えていただいたようにするとばっちりできました。
ありがとうございました!

【62529】Re:inputbox false で 終了しない
お礼  チューリップ  - 09/7/28(火) 22:06 -

引用なし
パスワード
   あ、入れ違いで、ご提案いただいてたのですね!

お忙しいのにすみません。
早速使わせていただき、不明なところがあったら質問させて頂きます。

ありがとうございました!

【62531】Re:inputbox false で 終了しない
質問  チューリップ  - 09/7/28(火) 23:51 -

引用なし
パスワード
   ▼kanabun さん:
教えていただいたプロシージャ、勉強させていただきました。

>  ([A2].Select のような Excelの自動範囲検索機能に頼らない)
自動範囲検索機能をあてにするのはよくないんですね。
これは知りませんでした。とても勉強になりました。

ものすごいバカで申し訳ないのですが、
二つお聞きしたいことがありまして。

>If StrPtr(msg1) = 0& Then Exit Sub
この0の後についている&は何ですか?

>Intersect(.Columns(2), .Offset(1)).Value = msg2

Intersectは、共有しているセルを返すメソッドですよね。
columns(2)はシートの2列目、すなわちB列だと思うんですが、
offset(1)というのがよくわかりません。
どの行に対してオフセットしているのでしょうか?

よろしくお願いします。

【62532】Re:inputbox false で 終了しない
発言  kanabun  - 09/7/29(水) 9:07 -

引用なし
パスワード
   ▼チューリップ さん:

> >If StrPtr(msg1) = 0& Then Exit Sub
> この0の後についている&は何ですか?

& は「型宣言文字」と呼ばれるもので、このばあい
数値0のデータ型は 整数(Integer)% でなく、長整数型
であることを明示しています。
単に
  If StrPtr(msg1) = 0 Then Exit Sub
で何ら問題はないのですが、細かいこというと、
VBE は 0 をみて 数値0を格納できる最小の型すなわち
Integer型の0% と解釈し、それとStrPtr(msg1)の戻り値とを
比較するために、0を長整数型&に 一度変換(型キャスト)して
から判断をしています。なので、最初から 0&と0の型を明示して
比較するコードを書いておけば、その分無駄な型変換をしなくて
済み、効率的というわけです。


>>Intersect(.Columns(2), .Offset(1)).Value = msg2

>Intersectは、共有しているセルを返すメソッドですよね。
 はい。
>columns(2)はシートの2列目、すなわちB列だと思うんですが、
 いいえ。.Columns(2) とColumns(2)のまえにドットが付いて
いますから、Worksheets("Sheet1")の
  .Range("A2").CurrentRegion という範囲の 2列目範囲の
ことです。シートの2列目はWorksheets("Sheet1").Columns(2)
です。
>offset(1)というのがよくわかりません。
>どの行に対してオフセットしているのでしょうか?

全体で何をしようとしているのかというと、
まず、AutoFilter.Range全体から、その2列目だけを取り出したい
という要求があり、AutoFilter範囲.Columns(2) で実現しています。
もうひとつ、範囲の1行目は列見出しなので、この先頭行を除外
したいという要求があり、そのため Offsetプロパティを使って
範囲を「1行下へシフト」しています。
[A1:F1000]がAutoFilter.Rangeだとすると、
[A1:F1000].Offset(1) が参照する範囲は [A2:F1001]です。
先ほど.Columns(2)プロパティで 範囲の2列目だけ取り出しました。
 [A1:F1000].Columns(2) が参照する範囲は [B1:B1000] です。

Intersect([A1:F1000].Columns(2), [A1:F1000].Offset(1))
は、
Intersect([B1:B1000], [A2:F1001])
という範囲のことで、最終的に
 [B2:B1000] という
2列目の 先頭見出しをカットしたセル範囲がえられています。
この範囲[B2:B1000] に 文字列msg2 を代入すると、Excelの
仕様で、[B2:B1000]の「可視セル」だけに値が代入されます。
ループで1行づつ可視行かどうかを調べていく必要はありません。

IntersectとOffsetプロパティの合わせ技は便利なので、
たとえば 表から 行見出し、列見出しを除いたデータ範囲を
取り出すのに使ったりします。
たとえば、下表のばあい、
   A   B   C   D   E
1     1月  2月  3月  4月
2 ああ  10  35  55   20
3 ええ  20  30  45   25
4 おお  35  45  55   35

そのデータ範囲は
 With [A1:E4]
   Set 範囲 = Intersect(.Cells, .Offset(1,1))
 End With
すなわち、[A1:E4]範囲と,それを右下方向に1セルづつShiftした
範囲とのIntersectする範囲となります。

【62534】Re:inputbox false で 終了しない
回答  yoshi  - 09/7/29(水) 10:51 -

引用なし
パスワード
   こんにちは。
>上記のように書くと、果物名をいれたときは間違いなく動くのですが、
>inputboxのキャンセルボタンを押した場合、ムシされて
>そのまますべての値を空欄に書き換えてしまいます。

キャンセル及び空白でOKの時に Exit Sub と考えられるので
単に以下でいいのでは?
 msg1 = InputBox("修正したい果物を入力してください" _
  & vbNewLine & "先頭の文字のみ入力でかまいません", "果物の検索", , 100, 100)
 If msg1 = "" Then Exit Sub

【62537】Re:inputbox false で 終了しない
発言  kanabun  - 09/7/29(水) 12:07 -

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

>キャンセル及び空白でOKの時に Exit Sub と考えられるので
>単に以下でいいのでは?
> msg1 = InputBox("修正したい果物を入力してください" _
>  & vbNewLine & "先頭の文字のみ入力でかまいません", "果物の検索", , 100, 100)
> If msg1 = "" Then Exit Sub

ユーザーがInputBoxでEnter空打ちしたときと、[キャンセル]ボタンを押して
処理を中断したときと区別したいとき、こんな書き方をしたりします。

 Dim msg1 As String
 Do
   msg1 = InputBox$("修正したい果物を入力してください")
   If StrPtr(msg1) = 0& Then Exit Sub 'ユーザーが[Cancel]した
   If Len(msg1) = 0 Then Msgbox "入力がありません"

 Loop Until Len(msg1)         '入力があるまで繰り返す

【62539】Re:inputbox false で 終了しない
回答  yoshi  - 09/7/29(水) 12:48 -

引用なし
パスワード
   >ユーザーがInputBoxでEnter空打ちしたときと、[キャンセル]ボタンを押して
>処理を中断したときと区別したいとき、こんな書き方をしたりします。

いや、キャンセル と 空白でOK を区別するとはだれも言ってませんが...
If StrPtr(msg1) = 0& Then Exit Sub
では、空白でOKのとき不都合となるので、
単に、If msg1 = "" Then Exit Sub でいいのではと...

【62545】Re:inputbox false で 終了しない
発言  kanabun  - 09/7/29(水) 14:44 -

引用なし
パスワード
   ▼yoshi さん:
どうもです。

> いや、キャンセル と 空白でOK を区別するとはだれも言ってませんが...
ぼくは、成り行き上(?)どのボタンが押されたのかで分岐したかったので
こだわってみました(^^

InputBox関数で[Cancel]ボタンが押されたとき返されるのは長さ 0 の
文字列("") ではありません。

h t tp://fireball.loafer.jp/kes/vba/vba-nullstring-vs-emptystring.html

ま、
キャンセルボタンの押下によるものであろうと、ユーザーが
Enterキーの空押しによるものであろうと、InputBox関数の戻り値が
(キャストされた かもしれない)長さ 0 の文字列("") と等しいので
あれば、有無を言わさず Exit Subしてしまって構わないのであれば、
> 単に、If msg1 = "" Then Exit Sub
とか、
 If Len(msg1) = 0 Then Exit Sub
でいいのでしょうけど...

【62553】Re:inputbox false で 終了しない
お礼  チューリップ  - 09/7/29(水) 18:14 -

引用なし
パスワード
   ▼kanabun さん:
実に丁寧な御説明どうもありがとうございます。

根本的に勘違いしていました。
.Range("A2").CurrentRegionの範囲と
それのoffset(1)と考えたらよくわかりました。

Intersectの使い方もわかりました。
まだまだ勉強中ですが、またよろしくお願いします。

【62554】Re:inputbox false で 終了しない
お礼  チューリップ  - 09/7/29(水) 18:16 -

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

ありがとうございます。
実はあとから”あっそういえばなんも書かないでOKボタン押されたらどうなるんだろ?”と思って教えていただいたコードを書き加えていました。
でもそれ一つで間に合うんですね。

ありがとうございました。

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