Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
9806 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【25299】空白ｾﾙの検索 kouka 05/5/27(金) 12:01 質問[未読]
【25301】Re:空白ｾﾙの検索 kazu 05/5/27(金) 13:24 発言[未読]
【25303】Re:空白ｾﾙの検索 ichinose 05/5/27(金) 13:47 発言[未読]
【25307】Re:空白ｾﾙの検索 kouka 05/5/27(金) 14:30 お礼[未読]
【25311】Re:空白ｾﾙの検索 ichinose 05/5/27(金) 20:05 発言[未読]
【25371】Re:空白ｾﾙの検索 kouka 05/5/30(月) 10:00 お礼[未読]
【25328】Re:空白ｾﾙの検索 akiuma 05/5/28(土) 18:37 発言[未読]
【25317】Re:空白ｾﾙの検索 [名前なし] 05/5/27(金) 22:42 回答[未読]
【25329】Re:空白ｾﾙの検索 akiuma 05/5/28(土) 18:40 発言[未読]
【25372】Re:空白ｾﾙの検索 kouka 05/5/30(月) 10:11 お礼[未読]
【25299】空白ｾﾙの検索
質問  kouka  - 05/5/27(金) 12:01 -
引用なし
パスワード
   こんにちは、koukaです。
もし、『DO』や『FOR』を使わなくても簡単に出来る方法がありましたら教えてください。

A1からA10まで入力の為のｾﾙがあります。
ここには数字だけを打つのですが、
もし、数字以外が入力されていたらｴﾗｰﾒｯｾｰｼﾞを出します。
ただし、入力される行数は必ずしも10行とは限りません。
日によって、3行だけだったり、8行あったり、10行まるまるある日もあります。
なので、A1からA10までの範囲で空白以外で、
さらに数字以外が入力されていた場合のみ、ｴﾗｰﾒｯｾｰｼﾞを出したいのです。
数字以外ならIsNumeric(Range("A1:A10").Value) = Falseで出来るのですが、
空白の検索がうまくいきません。

こんな都合のいい事が簡単に出来ますでしょうか？
教えてください、よろしくお願いします。
【25301】Re:空白ｾﾙの検索
発言  kazu  - 05/5/27(金) 13:24 -
引用なし
パスワード
   ▼kouka さん：

1.アドレスとか無しで空白セルが有るかどうかだけ検索する場合
エクセル関数　COUNTIFで空白の個数を検索したらいいのではないでしょうか。

Sub H()
　 A = Application.WorksheetFunction.CountIf(Range("A1:A10"), "")
　 MsgBox A = 0
End Sub

2.アドレスとか無しで空白セルが有るかどうかだけ検索する場合
これはFor使ってしまいますが・・・。
エクセル関数　COUNTIFで空白の個数を検索した結果が0でない場合、
SpecialCells(xlCellTypeBlanks)　←　空のセルを取得
見つかった空のセルのアドレスを取得

Sub B()
　 A = Application.WorksheetFunction.CountIf(Range("A1:A10"), "")
　　If A <> 0 Then
　　　　For Each C In ActiveSheet.Range("A1:A10").SpecialCells(xlCellTypeBlanks)
　　　　　　MsgBox C.Address
　　　　Next
　　End If
End Sub
【25303】Re:空白ｾﾙの検索
発言  ichinose  - 05/5/27(金) 13:47 -
引用なし
パスワード
   kazu さん、kouka さん、こんにちは。
セルＡ１〜Ａ１０の範囲の例で

Sub test()
　 With Range("a1:a10")
　　 If .Count > Evaluate("sumPRODUCT(ISNONTEXT(" & .Address & ")*1)") Then
　　　　MsgBox "エラー"
　　 Else
　　　　MsgBox "エラーなし"
　　 End If
　　 End With
End Sub

確認して下さい
【25307】Re:空白ｾﾙの検索
お礼  kouka  - 05/5/27(金) 14:30 -
引用なし
パスワード
   こんにちは、kazuさん、ichinose さん。

無事解決できました！
ありがとうございました！

もし、お手数でなければ下の、
>　 With Range("a1:a10")
>　　 If .Count > Evaluate("sumPRODUCT(ISNONTEXT(" & .Address & ")*1)") Then
の命令の意味をおばかな私にもわかるように教えてもらえますか？

お手すきでなければ、仕方ないですけど・・・。
よろしくお願いします。
【25311】Re:空白ｾﾙの検索
発言  ichinose  - 05/5/27(金) 20:05 -
引用なし
パスワード
   こんばんは。

>無事解決できました！
>ありがとうございました！
>
>もし、お手数でなければ下の、
>>　 With Range("a1:a10")
>>　　 If .Count > Evaluate("sumPRODUCT(ISNONTEXT(" & .Address & ")*1)") Then
>の命令の意味をわかるように教えてもらえますか？

.Countは、A1:A10のセルの個数ですから、10ですよね？

"sumPRODUCT(ISNONTEXT(" & .Address & ")*1)"は、
ワークシート関数を含んだ数式です。
Evaluateメソッドは、この数式を評価した結果を返します。
.Addressで、$A1:$A10というセルアドレスが取得できます。
よって、数式は、

SUMPRODUCT(ISNONTEXT($A$1:$A$10)*1)

と言う事になりますよね？

ここからは、数式を分解していきましょう！！

ISNOTEXT関数は、引数が文字列でなければTrue、文字列ならばFalseを
返します。

仮にＡ１には「あああ」

Ａ２に「123」（両端の「」は除く）

Ａ３からＡ１０は、空白（未入力）に

入力されているとします。

ISNOTEXT($A$1:$A$10)で

{false;true;true;・・・・・true}
という配列を返します（空白をTrueとして返します）。
さらに

ISNOTEXT($A$1:$A$10)*1

によって、論理値を算術値に変換しています。
（そうしないとSumproductが足し算をしてくれないので）

この結果、上記の配列が論理値から

{0;1;1;・・・・1}

に変換されます。

Sumproduct関数は、上記の配列要素の合計を出します。

よって、9という結果を得ます。

10>9　ですので　「エラー」と表示されます。

Sumproduct関数は、便利ですからその使用方法を
HELPで確認して下さい。

以上です。
【25317】Re:空白ｾﾙの検索
回答  [名前なし]  - 05/5/27(金) 22:42 -
引用なし
パスワード
   こんなのでも。

Sub Macro1()
　　With WorksheetFunction
　　　　If .CountA([A1:A10]) - .Count([A1:A10]) > 0 Then
　　　　　　MsgBox "入力出来るのは数値だけです。"
　　　　End If
　　End With
End Sub

ところで、A1:A10に予め[入力規則]の[入力値の種類]で数値しか入力できないように
制限をつけておけばいい気がしますが、なにか問題があるのでしょうか？
【25328】Re:空白ｾﾙの検索
発言  akiuma  - 05/5/28(土) 18:37 -
引用なし
パスワード
   ▼kazu さん：
>>Sub B()
>　 A = Application.WorksheetFunction.CountIf(Range("A1:A10"), "")
>　　If A <> 0 Then
>　　　　For Each C In ActiveSheet.Range("A1:A10").SpecialCells(xlCellTypeBlanks)
>　　　　　　MsgBox C.Address
>　　　　Next
>　　End If
>End Sub

失礼ですが、このプログラムだとデータ丸ごと１０件入力したら、数値以外の入力エラーが発見できないと思います。
【25329】Re:空白ｾﾙの検索
発言  akiuma  - 05/5/28(土) 18:40 -
引用なし
パスワード
   >ところで、A1:A10に予め[入力規則]の[入力値の種類]で数値しか入力できないように
>制限をつけておけばいい気がしますが、なにか問題があるのでしょうか？

すでに何万件と入力してあるマスターの更新では？
【25371】Re:空白ｾﾙの検索
お礼  kouka  - 05/5/30(月) 10:00 -
引用なし
パスワード
   こんにちは、ichinoseさん

わかりやすい内容をありがとうございます！
目から鱗ものでした。
こんなことに付き合っていただき恐縮です。
大変勉強になりました。
ありがとうございました！m(_ _)m
【25372】Re:空白ｾﾙの検索
お礼  kouka  - 05/5/30(月) 10:11 -
引用なし
パスワード
   こんにちは、[名前なし]さん、akiumaさん。

ひとつの結果を出すのにもいろいろありますね。
勉強になりました。
ありがとうございます。

>>ところで、A1:A10に予め[入力規則]の[入力値の種類]で数値しか入力できないように
>>制限をつけておけばいい気がしますが、なにか問題があるのでしょうか？
>
>すでに何万件と入力してあるマスターの更新では？
入力規則を指定していても、他のｼｰﾄからｺﾋﾟｰして貼り付けしてしまうと、
そのまま入ってしまうので困っていました。
（ですよね？ もし違うならごめんなさい。。。）
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
9806 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free