
エクセル2007のVBAでオートフィルタのチェックを閾値以上の%のみに入れたいのです。
ユーザー設定フィルタでは視覚的に解りつらい為、フィルタの▽をクリックした時に、チェックがされている事を確認したいのです。
【シート1の内容】
セルA1から行方向に数字の1~3
セルB1から行方向に、値1、値2、%
セルA3~Bnは列方向に、整数
セルC3から列方向に、“=A3/B3”が入力されており、書式は パーセンテージ(小数点以下の桁数は“1”)
セルD1に 閾値として 10.5%・・・書式はC3に同じ
【目的】
動きとしては、閾値以上の結果を出すつもりで書きました。
【質問】
フィルタがかかった▽をクリックした時に、1行、2行及びC列の10.5%以上のチェックボックスにチェックを入れたいのです。
しかし、下記コードの
.AutoFilter Field:=3, Criteria1:=Array("%") _
, Operator:=xlFilterValues, Criteria2:=Array(TargetCD)
でエラーが出てしまいます。
実行時エラー '1004':
Range クラスの AutoFilter メソッドが失敗しました。
Sub Threshol()
Dim MaxRow As Integer
Dim TargetCD
Dim CDDiff As Integer
Dim MinCD As Single
Dim MaxCD As Single
Dim i As Integer
Dim j As Single
MaxRow = Range("C1").End(xlDown).Row
With ActiveSheet.Range(Cells(3, 3), Cells(MaxRow, 3))
MinCD = ThisWorkbook.Worksheets(1).Range("D1").Value * 100
MaxCD = Application.Round(Application.Max(.Cells) * 100, 1)
CDDiff = (MaxCD - MinCD) * 10
ReDim TargetCD(1 To CDDiff + 1)
For i = 1 To UBound(TargetCD)
TargetCD(i) = FormatPercent(MinCD / 100 + j, 1)
j = Format(j + 0.001, "#.###")
Next
.AutoFilter Field:=3, Criteria1:=Array("%") _
, Operator:=xlFilterValues, Criteria2:=Array(TargetCD)
End With
End Sub
皆様、良いご助言を宜しくお願い致します。
No.4ベストアンサー
- 回答日時:
いや、スミマセン。
お騒がせしちゃって。うっかり確認が漏れていました。
というより、私の当初の見立ては大きく外れていて、
これは、実際XL2007環境で確認しないでいては、
そもそも無茶な話を振った私だったようです。
一応、2010での挙動と、問題点を挙げておきます。
.AutoFilter Field:=1, Criteria1:=">=" & MinCD, Operator:=xlAnd
↑これは、
> 動きとしては、閾値以上の結果を出す...
という要求には適っていますが、
> フィルタの▽をクリックした時に、チェックがされている事を確認したいのです。
という要求からは外れています。
.AutoFilter Field:=1, Criteria1:=TargetCD, Operator:=xlFilterValues
↑これは、
> フィルタの▽をクリックした時に、チェックがされている事を確認したいのです。
という要求には適っていますが、
> 動きとしては、閾値以上の結果を出す...
という要求からは外れています。
ご提示のコード、私があげた2種類、何れもうまくいきません。
> セルC3から列方向に、“=A3/B3”が入力されており、書式は パーセンテージ(小数点以下の桁数は“1”)
表示上丸められた数字文字列が"10.5%"でも、実際の数値は10.5%未満のケースでは
閾値未満のものが抽出されてしまいます。
※ここら辺↑は仕様が変わっていそうなので、2007では違う結果なのかも知れませんが、、、。
例を挙げます。
(1) 50/ 476=0.10504
(2) 50/ 477=0.10482
(3) 50/ 478=0.1046
> 閾値以上
は(1)だけですが、
> パーセンテージ(小数点以下の桁数は“1”)
で表示させると、(1)(2)(3)ともに"10.5%"で
"10.5%"を Operator:=xlFilterValues でフィルターにかけると
(2)(3)も抽出されてしまいます。
10.5%未満の値で表示が"10.5%"のセルについては
10.5%以上の値で表示が"10.5%"のセルの有無によって
抽出されたりされなかったり、という結果になってしまいます。
比較的小さい数字で 10.5%未満の値で表示が"10.5%" になる例を挙げると
9/ 86=0.10465
11/ 105=0.10476
13/ 124=0.10484
15/ 143=0.1049
16/ 153=0.10458
17/ 162=0.10494
18/ 172=0.10465
19/ 181=0.10497
20/ 191=0.10471
. . .
結構、ありますね。
私がわかってないだけで(←仕様の違い、と、仕様そのもの)
何か、方法が別にあるのかも知れませんが、今私が知る限りでは、
> 動きとしては、閾値以上の結果を出す...
> フィルタの▽をクリックした時に、チェックがされている事を確認したいのです。
ふたつの要求を同時に満たす為には、
C列の値(数式での丸め方、丸める桁)や表示(小数点以下の桁数)の方を工夫すること
しか、思い浮かびません。
(↑10.5%未満の値で表示が"10.5%"のセル と 10.5%以上の値で表示が"10.5%"のセル
とが、見かけ上、異なる表示になるようにする、という意味です)
ということで、お騒がせしたわりに、
私の回答では、要求仕様に応えるものが提示出来ていないようなのです。
申し訳ありません。他の回答をお待ちください。
この回答への補足
cj_moverさま
おはようございます。
閾値10.5%のままで、C列が10.45####%以上であれば、C列の見かけ上の10.5%を抽出&フィルタチェックが行えれば良いと考え、(今までもそこまでは厳密にしていなかったので。。。)
ご回答頂いた、#3のSub Threshol_jj()の内、
MinCD = .Range("D1").Value - 0.0005
であれば、見かけ上ですが私の仕様を満足出来そうです。(本当に良いのかなとは思いますが)
cj_moverさまとしては、いかが思われますでしょうか?
cj_moverさま
深夜までご検討下さり、誠にありがとうございます。
代案の丸め方で再検討してみます。
今回も大変勉強になりました!!
No.3
- 回答日時:
たびたびすみません、再度#1の訂正です。
"以上" を "より大きい" と思い込んでしまっていましたので
拙コード、不適切でした。
2か所、2文字ですが、プロシージャごと加筆再掲します。
Sub Threshol_j()
Dim TargetCD
Dim MinCD
Dim MaxRow As Long
Dim i As Integer
Dim Target As Range ' ">=閾値"で抽出済みの範囲
Dim r As Range ' Target 全セル ループ用
Dim CntTgt As Long ' ">=閾値"で抽出された個数
With ActiveSheet
.AutoFilterMode = False
MaxRow = .Range("C1").End(xlDown).Row
MinCD = .Range("D1").Value
With .Range("C2:C" & MaxRow)
.AutoFilter Field:=1, Criteria1:=">=" & MinCD, Operator:=xlAnd
Set Target = .SpecialCells(xlCellTypeVisible)
CntTgt = Target.Count
ReDim TargetCD(1 To CntTgt)
For Each r In Target
i = i + 1
TargetCD(i) = r.Text
Next r
.AutoFilter Field:=1, Criteria1:=TargetCD, Operator:=xlFilterValues
End With
End With
Set Target = Nothing
End Sub
Sub Threshol_jj()
Dim TargetCD
Dim MinCD As Variant
Dim MaxRow As Long
Dim r As Range ' ループ用
With ActiveSheet
.AutoFilterMode = False
MaxRow = .Range("C1").End(xlDown).Row
MinCD = .Range("D1").Value
With .Range("C2:C" & MaxRow)
For Each r In .Cells
If r.Value >= MinCD Then TargetCD = TargetCD & " " & r.Text
Next r
TargetCD = Split(Mid(TargetCD, 2))
.AutoFilter Field:=1, Criteria1:=TargetCD, Operator:=xlFilterValues
End With
End With
End Sub
#2007で使えれば、、、の話ですが(^^;
度重なるご回答ありがとうございます。
">="の部分は、私も修正してトライしておりました。
しかし、既に御気付きの通り#4の御回答の通りの結果となりました。
No.2
- 回答日時:
すみません。
#1の訂正です。///> 確認事項5■
ご提示の TargetCD は、配列。
Array()関数 は、Array("10.6%", "10.7%", "10.8%") のように
個々の値を指定しながら配列を作る時に使う関数。
Array()関数 の引数に配列を指定すると、返ってくるのは、
配列の中身が配列 になっている二段階配列(ジャグ配列)。
Criteria に指定する 配列 は一次元で十分なので
Array(TargetCD) は単に TargetCD で足ります。
///
それと、
XL2007 と XL2010 で、フィルターの仕様が
いろいろと異なっていることだけは承知しているのですが、
こちらでは2010しか確認できる環境がないので、
もし、頓珍漢な答えになっていた場合は、ごめんなさい。
No.1
- 回答日時:
こんにちは
確認事項1■
? Range("D1").Value -> 0.105
? Range("D1").Text -> "10.5%"
今回抽出対象として扱うのは数値。
表示形式の如何を問わず、皆、数値です。
確認事項2■
.AutoFilter の引数 Criteria に指定する 配列 の中身は、文字列。
Range("any").Text で取得できるセルの表示内容(文字列)と同じものです。
確認事項4■
.AutoFilter で、タイトルを抽出条件に加えるのは必要ではないです。
確認事項5■
ご提示の TargetCD は、配列。
Array()関数 は、Array("9.9", "10.4", "10.6") のように
個々の値を指定しながら配列を作る時に使う関数。
Array()関数 の引数に配列を指定すると、返ってくるのは、
配列の中身が配列になっている二次元配列。
Criteria に指定する 配列 は一次元で十分。
Array(TargetCD) は単に TargetCD で足ります。
確認事項5■
1列のRangeオブジェクトにAutoFilterを指定するのに
> Field:=3
とすれば存在しない3列めを指定していることになり、エラーになります。
ご質問に対する直接的な回答■
> しかし、下記コードの
> .AutoFilter Field:=3, Criteria1:=Array("%") _
> , Operator:=xlFilterValues, Criteria2:=Array(TargetCD)
> でエラーが出てしまいます。
>
> 実行時エラー '1004':
> Range クラスの AutoFilter メソッドが失敗しました。
.AutoFilter Field:=1, Criteria1:=TargetCD, Operator:=xlFilterValues
で、いける筈です。
> j = Format(j + 0.001, "#.###")
この行の意図は汲み取れませんでした。
備考■
実在しているかどうかわからない値を Criteria 配列 に指定するのは必要以上のこと。
データによっては負荷が大きすぎちゃうんじゃないでしょうか。。
実在する値だけを追うように書いてみました(2種類)。
尚、当方、Excel2010にて動作の確認をしています。
1)一旦、">閾値"で、フィルターをかけ、
可視セル(抽出されたセル)を取得し、
セル数を取得→配列のサイズを再定義→各セルの表示文字列を配列に要素に設定。
Sub Threshol_j()
Dim TargetCD
Dim MinCD
Dim MaxRow As Long
Dim i As Integer
Dim Target As Range ' ">閾値"で抽出済みの範囲
Dim r As Range ' Target 全セル ループ用
Dim CntTgt As Long ' ">閾値"で抽出された個数
With ActiveSheet
.AutoFilterMode = False
MaxRow = .Range("C1").End(xlDown).Row
MinCD = .Range("D1").Value
With .Range("C2:C" & MaxRow)
.AutoFilter Field:=1, Criteria1:=">" & MinCD, Operator:=xlAnd
Set Target = .SpecialCells(xlCellTypeVisible)
CntTgt = Target.Count
ReDim TargetCD(1 To CntTgt)
For Each r In Target
i = i + 1
TargetCD(i) = r.Text
Next r
.AutoFilter Field:=1, Criteria1:=TargetCD, Operator:=xlFilterValues
End With
End With
Set Target = Nothing
End Sub
2)C列2行目から最下行まで、全セルをループして
閾値より大きい値を持つセルの表示文字列を " "半角スペース区切りで連結した文字列
を Split() 関数を使って配列にします。
(簡素化する為に、タイトル"%"の分だけ余計な条件を加えたままにしています)
Sub Threshol_jj()
Dim TargetCD
Dim MinCD As Variant
Dim MaxRow As Long
Dim r As Range ' ループ用
With ActiveSheet
.AutoFilterMode = False
MaxRow = .Range("C1").End(xlDown).Row
MinCD = .Range("D1").Value
With .Range("C2:C" & MaxRow)
For Each r In .Cells
If r.Value > MinCD Then TargetCD = TargetCD & " " & r.Text
Next r
TargetCD = Split(Mid(TargetCD, 2))
.AutoFilter Field:=1, Criteria1:=TargetCD, Operator:=xlFilterValues
End With
End With
End Sub
以上、ご参考になれば。
早速のご回答誠にありがとうございます。
現実的には、200列程あり、それぞれフィルターの値を選択し、(フィルターを掛け、値を取得し
更にフィルターを掛け文字を取得し…)と何回かの過程を経て最終値を別のBookに反映するのが
目的なのです。
>確認事項1~2、4■
大変参考になりました。
>確認事項3■
>.AutoFilter で、タイトルを抽出条件に加えるのは必要ではないです。
2列目全てにフィルタを掛けると文字列が▽で見えなくなるのと、2列目の文字を検索するよりも
1列目の数字で検索するのが私としてはやりやすいので、タイトル(2列目)も条件に加えたいのです。
>確認事項5■
>1列のRangeオブジェクトにAutoFilterを指定するのに
>> Field:=3
>とすれば存在しない3列めを指定していることになり、エラーになります。
前述の通り、実際は3列目では無く、質問を簡略コードに書き換えておりました。
もっと具体的且つ詳細に質問するべきでした。すみません。
>> j = Format(j + 0.001, "#.###")
>この行の意図は汲み取れませんでした。
私のコードで確認した時に、最初は j = j + 0.001 でしたが、j の値が 1回目で
0.9####### と表示されたので、フォーマットしました。
>備考■
>実在しているかどうかわからない値を Criteria 配列 に指定するのは必要以上のこと。
>データによっては負荷が大きすぎちゃうんじゃないでしょうか。。
おっしゃる通りでございます。-100.0%未満も含まれる事があるのですが、最近質問したご回答を
引用出来るかなと思い。。。なにぶん超初心者なもので・・・
皆さんはどこでどうやってVBAを勉強されたのでしょうか??
私は、マクロを記録し、類似した質問を検索しカット&トライでなんとか効率を上げている事務員で
御座います^^;
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
表にフィルターをかけ、絞った...
-
[エクセル]連続する指定範囲か...
-
array関数で格納した配列の型を...
-
エクセルで、絶対値の平均を算...
-
DataSetから、DataTableを取得...
-
CSVファイルの読み込みと表示(V...
-
スプレットシートのGetTextにつ...
-
C#で配列のフィールドを取得したい
-
Excel オートフィルタのリスト...
-
Datatableへの代入
-
VBAで指定期間の範囲を抽出し、...
-
配列の受け渡しについて
-
【VBA】ユーザーフォーム リス...
-
VB6.0 ファイルの一括読込み
-
[VBA]改行入りのセルの値を配列...
-
エクセル 条件を指定した標準...
-
Excelのオートフィルタで抽出し...
-
Excelのセルの色指定をVBAから...
-
SUMPRODUCT関数を用いた最小値
-
iniファイルのキーと値を取得す...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで、絶対値の平均を算...
-
[エクセル]連続する指定範囲か...
-
表にフィルターをかけ、絞った...
-
ExcelのINDEXとMATCH関数でスピ...
-
Excelのセルの色指定をVBAから...
-
array関数で格納した配列の型を...
-
【VBA】ユーザーフォーム リス...
-
Excel オートフィルタのリスト...
-
配列がとびとびである場合の書き方
-
iniファイルのキーと値を取得す...
-
.NET - 配列変数を省略可能の引...
-
DataSetから、DataTableを取得...
-
読み込みで一行おきに配列に格納
-
エクセルでエラーを無視して一...
-
VBAでの100万行以上のデータの...
-
配列のSession格納、及び取得方...
-
Datatableへの代入
-
[VBA]改行入りのセルの値を配列...
-
SUMPRODUCT関数を用いた最小値
-
エクセル 条件を指定した標準...
おすすめ情報