Excel VBA質問箱 IV

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

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


7785 / 13644 ツリー ←次へ | 前へ→

【36730】オートフィルタの結果を他のオートフィル... 初心者 06/4/11(火) 11:11 質問[未読]
【36732】Re:オートフィルタの結果を他のオートフィ... Jaka 06/4/11(火) 11:40 発言[未読]
【36734】Re:オートフィルタの結果を他のオートフィ... 初心者 06/4/11(火) 12:30 質問[未読]
【36737】Re:オートフィルタの結果を他のオートフィ... Jaka 06/4/11(火) 15:40 発言[未読]
【36743】Re:オートフィルタの結果を他のオートフィ... 初心者 06/4/11(火) 18:07 質問[未読]
【36748】Re:オートフィルタの結果を他のオートフィ... Jaka 06/4/12(水) 14:24 発言[未読]
【36803】Re:オートフィルタの結果を他のオートフィ... 初心者 06/4/14(金) 11:04 質問[未読]
【36816】Re:オートフィルタの結果を他のオートフィ... Jaka 06/4/14(金) 16:08 発言[未読]
【36820】Re:オートフィルタの結果を他のオートフィ... Jaka 06/4/14(金) 17:22 発言[未読]
【36828】Re:オートフィルタの結果を他のオートフィ... 初心者 06/4/14(金) 18:38 質問[未読]
【36869】めちゃくちゃ間違ってました。 Jaka 06/4/17(月) 10:33 発言[未読]
【36893】Re:オートフィルタの結果を他のオートフィ... 初心者 06/4/17(月) 15:45 お礼[未読]

【36730】オートフィルタの結果を他のオートフィル...
質問  初心者  - 06/4/11(火) 11:11 -

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

表題通りなのですが、仕入・販売・在庫と3つのシートがあり

仕入の数量から販売の数量を引いて在庫シートに転記しています。

問題なのが、この3つのシートが全て別ブックの注文表という入力シート

から値だけを転記しているので、3つのシートをいじるわけにはいかないのです。

そこで、注文表から、仕入・在庫のシートをオートフィルタをかけて、

在庫シートに転記する際に、仕入れシートに在庫数を転記させたいのですが、

仕入れシートのオートフィルタをかけても、データの最終行に転記されてしまうの

です。

オートフィルタそかけた行の指定セルに貼り付けするにはどうしたらいいでしょうか?

最終的には、在庫シートに転記された販売数を仕入れシートに転記して、現在数を

だしたいのです。

現在、下記のコードを入れていますが、CeV.Copy 範囲 のところで、転記したい

列ではなく、現在、先頭列にコピーされてしまいます。

(先頭列にデータが入っていないからかもしれませんが。。)

それと、’AS−BE列で現在数を出す。というところで、計算がされていないよ

うです。

ご助言いただきたいので、よろしくお願いします。


******標準モジュール*******

Sub フィルタオン()
 Dim Wb1 As Workbook
 Dim WB2 As Workbook
 Dim WS3 As Worksheet
 Dim WS2 As Worksheet
 Dim WS2 As Worksheet
 Set Wb1 = Workbooks("入力.xls") 
 Set WS2 = Wb1.Sheets("入力")
 Set WB2 = Workbooks("集計.xls")
 Set WS3 = WB2.Sheets("在庫")
 Set WS5 = WB2.Sheets("仕入")
 
'入力シートのB23を基準に仕入れシートのオートフィルタをオンにする。
 
If WS5.AutoFilterMode = False Then
  WS5.Range("A:BF").AutoFilter Field:=1, Criteria1:=WS2.Range("B23")
 
  End If
 
End Sub

Sub オートフィルタオフ()

 Dim Wb1 As Workbook
 Dim WB2 As Workbook
 Dim WS3 As Worksheet
 Dim WS2 As Worksheet
 Dim WS2 As Worksheet
 Set Wb1 = Workbooks("入力.xls") 
 Set WS2 = Wb1.Sheets("入力")
 Set WB2 = Workbooks("集計.xls")
 Set WS3 = WB2.Sheets("在庫")
 Set WS5 = WB2.Sheets("仕入")

'仕入れシートのオートフィルタ解除。

 If WS5.AutoFilterMode = True Then
  WS4.Range("B23").AutoFilter
 End If

End Sub


Sub フィルタオン1()
 Dim Wb1 As Workbook
 Dim WB2 As Workbook
 Dim WS3 As Worksheet
 Dim WS2 As Worksheet
 Dim WS2 As Worksheet
 Set Wb1 = Workbooks("入力.xls") 
 Set WS2 = Wb1.Sheets("入力")
 Set WB2 = Workbooks("集計.xls")
 Set WS3 = WB2.Sheets("在庫")
 Set WS5 = WB2.Sheets("仕入")

’在庫シートのA〜AQにオートフィルタをかける(入力シートB23基準)

If WS3.AutoFilterMode = False Then
  WS3.Range("A:AQ").AutoFilter Field:=2, Criteria1:=WS4.Range("B23")

Sub オートフィルタオフ1()

 Dim Wb1 As Workbook
 Dim WB2 As Workbook
 Dim WS3 As Worksheet
 Dim WS2 As Worksheet
 Dim WS2 As Worksheet
 Set Wb1 = Workbooks("入力.xls") 
 Set WS2 = Wb1.Sheets("入力")
 Set WB2 = Workbooks("集計.xls")
 Set WS3 = WB2.Sheets("在庫")
 Set WS5 = WB2.Sheets("仕入")


'在庫シートのオートフィルタ解除。
 If WS3.AutoFilterMode = True Then
  WS4.Range("B23").AutoFilter
 End If

End Sub

******シートモジュール*******
Private Sub CommandButton2_Click()

 Dim Wb1 As Workbook
 Dim WB2 As Workbook
 Dim WS3 As Worksheet
 Dim WS2 As Worksheet
 Dim WS2 As Worksheet
 Set Wb1 = Workbooks("入力.xls") 
 Set WS2 = Wb1.Sheets("入力")
 Set WB2 = Workbooks("集計.xls")
 Set WS3 = WB2.Sheets("在庫")
 Set WS5 = WB2.Sheets("仕入")

 Dim 範囲 As Range
 Dim CeV As Range

’オートフィルタをかけた仕入シートのBE列の最後の行
 Set 範囲 = WS5.Range("BE65536").End(xlUp).Offset(1)

’オートフィルタをかけた在庫シートのP列の最後の行
 Set CeV = WS3.Range("P65536").End(xlUp)

’P列の最終データ(セルの値)をBE列でフィルタをかけて抽出した行に転記

CeV.Copy 範囲
 
 WS5.Activate
 
’仕入シートのAS1には仕入れ数が入っている。
’AS−BE列で現在数を出す。

  Range("AS1", Range("AS65536").End(xlUp)).Offset(, 2).Formula = _
      "=IF(BF1,AS1-BE1,""AS"")"

 Call オートフィルタオフ1
 Call オートフィルタオフ
 
  With WS5.Parent
    .Save
  End With
 
  With WS3.Parent
    .Save
    .Close False
  End With
 
  WS4.Activate
 
 
End Sub

【36732】Re:オートフィルタの結果を他のオートフ...
発言  Jaka  - 06/4/11(火) 11:40 -

引用なし
パスワード
   >オートフィルタそかけた行の指定セルに貼り付けするにはどうしたらいいでしょうか?
>最終的には、在庫シートに転記された販売数を仕入れシートに転記して、現在数を
>だしたいのです。
「オートフィルタで抽出したデータ」がどうな風にフィルタをかけているのか解らないから、どの部分だか解らない。
また指定セルにって簡単に言っているけど、どんな状態のセルなのかさっぱり解らない。
どのように張付けるのかも解らないです。

もっと簡単に書けたかなと思っていますが....。
(最も仕様をどうするか決めていなかったから試作みたいな物。)
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=142;id=FAQ

【36734】Re:オートフィルタの結果を他のオートフ...
質問  初心者  - 06/4/11(火) 12:30 -

引用なし
パスワード
   ご指摘ありがとうございます。

>>オートフィルタそかけた行の指定セルに貼り付けするにはどうしたらいいでしょうか?
>>最終的には、在庫シートに転記された販売数を仕入れシートに転記して、現在数を
>>だしたいのです。
>「オートフィルタで抽出したデータ」がどうな風にフィルタをかけているのか解らないから、どの部分だか解らない。

オートフィルタで抽出したデータとは、在庫シートの入力フォーマットを入力する時に、仕入れ番号と販売数を入れるので、仕入れ番号のデータをオートフィルタをかけて表示するということです。
取引を全て商品名ではなく、番号を振り当てて管理しています。

上記箇所の

>If WS3.AutoFilterMode = False Then
>  WS3.Range("A:AQ").AutoFilter Field:=2, Criteria1:=WS4.Range("B23")

のB23とは仕入れ番号です。

>また指定セルにって簡単に言っているけど、どんな状態のセルなのかさっぱり解らない。
>どのように張付けるのかも解らないです。
>

説明不足で申し訳ありません。
指定セルというのは、仕入シートも在庫シートも同じ取引番号が入力されているので、両方とも同じ取引番号でオートフィルタをかけた後のセルです。
仕入れシートでオートフィルタをかけると、在庫数という列があり、オートフィルタをかけた在庫数というセルに在庫シートから抽出したデータを貼り付けたいとおもっています。

今回のコードは仕様書も設計書もなく、いろいろと要求がきて随時機能を追加している。。。という感じなので、私の方でも対応ができず、以前に作っていたものを
途中から書き換えて・・・。
という形でできてしまったので、
上記のような形式になってしまいました。

もっと簡単な方法があるのなら、そうしたいのですが・・。

>もっと簡単に書けたかなと思っていますが....。
>(最も仕様をどうするか決めていなかったから試作みたいな物。)
>http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=142;id=FAQ

【36737】Re:オートフィルタの結果を他のオートフ...
発言  Jaka  - 06/4/11(火) 15:40 -

引用なし
パスワード
   レイアウトがどうなっているのか聞きなおすのも面倒なので、
適当にカスタマイズしてください。
参考になるかわからないけど...。

Dim Cel As Range, CelAry() As Range
G = 0
For Each Cel In Sheets("Sheet1").AutoFilter.Range.Columns("B").SpecialCells(xlVisible)
  G = G + 1
  ReDim Preserve CelAry(1 To G)
  Set CelAry(G) = Cel
Next
G = 0
For Each Cel In Sheets("Sheet2").AutoFilter.Range.Columns("M").SpecialCells(xlVisible)
  G = G + 1
  If G <> 1 Then Cel.Value = CelAry(G)
  If UBound(CelAry) = G Then Exit For
Next
Erase CelAry

【36743】Re:オートフィルタの結果を他のオートフ...
質問  初心者  - 06/4/11(火) 18:07 -

引用なし
パスワード
   ▼Jaka さん:
>レイアウトがどうなっているのか聞きなおすのも面倒なので、
>適当にカスタマイズしてください。
>参考になるかわからないけど...。
>
>Dim Cel As Range, CelAry() As Range
>G = 0
>For Each Cel In Sheets("Sheet1").AutoFilter.Range.Columns("B").SpecialCells(xlVisible)
>  G = G + 1
>  ReDim Preserve CelAry(1 To G)
>  Set CelAry(G) = Cel
>Next
>G = 0
>For Each Cel In Sheets("Sheet2").AutoFilter.Range.Columns("M").SpecialCells(xlVisible)
>  G = G + 1
>  If G <> 1 Then Cel.Value = CelAry(G)
>  If UBound(CelAry) = G Then Exit For
>Next
>Erase CelAry

アドバイスありがとうございます。
上の構文でわからないところがあるのですが、

For Each Cel In Sheets("Sheet1").AutoFilter.Range.Columns("B").SpecialCells(xlVisible)

の("B")は何をさしているのでしょうか?
また、 ReDim Preserve CelAry(1 To G)にオートメーションエラーが出てしまい、範囲対象外となってしまいました。

きちんとコードが理解できていないので、時間がかかってしまいました。
もしできるなら、意味を教えていただけませんでしょうか?

お手数をおかけしますが、よろしくお願いします。

【36748】Re:オートフィルタの結果を他のオートフ...
発言  Jaka  - 06/4/12(水) 14:24 -

引用なし
パスワード
   >For Each Cel In Sheets("Sheet1").AutoFilter.Range.Columns("B").SpecialCells(xlVisible)
>
>の("B")は何をさしているのでしょうか?
オートフィルタした範囲の左から2番目の列。
B列といった表現は、フィルタ範囲がA:BF列だったので2番目と書くよりB列と書いた方がわかりやすいと思ったんですが....。

因みに
MsgBox Range("C:G").Columns("A").Address
とすると、C:G間でのA列と言う事で全体でみたC列を挿します。

>また、ReDim Preserve CelAry(1 To G)にオートメーションエラーが出てしまい、範囲対象外となってしまいました。
これについては、解りません。(なった事がない?)

実際、どういった状態で試されているのか解らないし、カスタマイズ及び今作っているプログラムに組み込んで試された結果なのか、それらが解らないので何ともいえないです。

質問者で意味不明なのは、カスタマイズや、今作っているプログラムに組み込んで動きません、エラーになります。と言われても、こちらは単品で動く物を書いているから、かみ合わない事も多々あります。
この辺はどうなのでしょうか?

【36803】Re:オートフィルタの結果を他のオートフ...
質問  初心者  - 06/4/14(金) 11:04 -

引用なし
パスワード
   Jakaさん
返信が遅くなりまして申し訳ありません。
解説ありがとうございました。

>実際、どういった状態で試されているのか解らないし、カスタマイズ及び今作っているプログラムに組み込んで試された結果なのか、それらが解らないので何ともいえないです。

集計ブック
仕入シート
A   B    C     D      E   ・・・  BE    BF
1番号 商品名  単価   数量  合計金額 ・・・  販売数  残数
2 1  りんご  120   100   12,000 ・・・
3 2  ばなな  100   500   50,000 ・・・

販売シート
  A    B    C     D       E   ・・・    
1 番号 商品名  単価  販売数量  金額合計 ・・・  
2 3   りんご  120   50    6,000  ・・・
3 4   ばなな  100   100   10,000  ・・・ 
4 5   りんご  120   20    2,400  ・・・
在庫シート
  A   B     C    D   ・・・   O      P
1 番号 購入番号 販売番号 商品名 ・・・  初期数量  販売数
2 a   1    3    りんご ・・・   100    50 
3 b   2    4    ばなな ・・・   500    100
4 c   1    5    りんご ・・・   100    20 

注文表ブック
在庫入力シート
  A       B    
1 番号      c
2 購入番号    1  
3 販売番号    5
4 商品名    りんご (VLOOKUPで抽出)
   ・      ・
   ・      ・
   ・      ・
 
23 初期数量   100  (VLOOKUPで抽出) 
24 販売数     20  (VLOOKUPで抽出)

    転記ボタン

上記のようになっていて、在庫入力シートのB列を在庫シートに行列を入れ替えて転記しています。
転記後、在庫入力シートの購入番号で在庫シートのオートフィルタかけ、
同じように在庫入力シートの購入番号で仕入シートをオートフィルタかけています。
最終的には在庫シートの販売数を仕入シートのBE列の対応番号に在庫シートのP列の最後の行のセルを張付け、仕入れシートのD-BEの計算をさせて結果をBFに表示する。
ということをしているのです。
というわけで、

Private Sub CommandButton2_Click()
On Error Resume Next
 Dim Wb1 As Workbook
 Dim WB2 As Workbook
 Dim WS3 As Worksheet
 Dim WS2 As Worksheet
 Dim WS2 As Worksheet
 Dim CeV As Range
 Dim Cel As Range, CelAry() As Range
 
 Set Wb1 = Workbooks("入力.xls") 
 Set WS2 = Wb1.Sheets("入力")
 Set WB2 = Workbooks("集計.xls")
 Set WS3 = WB2.Sheets("在庫")
 Set WS5 = WB2.Sheets("仕入")


(myCopyは標準モジュールに定義済み)

myCopy WS2.Range("B1:B43"), _
      WS3.Range("A65536").End(xlUp).Offset(1), True


G = 0
For Each Cel In Sheets("仕入").AutoFilter.Range.Columns("B").SpecialCells(xlVisible)
  G = G + 1
  ReDim Preserve CelAry(1 To G)
  Set CelAry(G) = Cel
Next
G = 0
For Each Cel In Sheets("在庫").AutoFilter.Range.Columns("B").SpecialCells(xlVisible)
  G = G + 1
  If G <> 1 Then Cel.Value = CelAry(G)
  If UBound(CelAry) = G Then Exit For
Next

Erase CelAry

WS5.Activate

 
Range("A1", Range("D65536").End(xlUp)).Offset(,2).Formula = _
      "=IF(BF1,D1-BE1,""D1"")"

ActiveSheet.AutoFilterMode = False

WS3.Activate
ActiveSheet.AutoFilterMode = False

With WS3.Parent
    .Save
    .Close False
End With
 
  Wb1 .Activate

End sub


となっています。
もしかして、したいこととコードが異なっていますか?

▼Jaka さん:
>>For Each Cel In Sheets("Sheet1").AutoFilter.Range.Columns("B").SpecialCells(xlVisible)
>>
>>の("B")は何をさしているのでしょうか?
>オートフィルタした範囲の左から2番目の列。
>B列といった表現は、フィルタ範囲がA:BF列だったので2番目と書くよりB列と書いた方がわかりやすいと思ったんですが....。
>
>因みに
>MsgBox Range("C:G").Columns("A").Address
>とすると、C:G間でのA列と言う事で全体でみたC列を挿します。
>
>>また、ReDim Preserve CelAry(1 To G)にオートメーションエラーが出てしまい、範囲対象外となってしまいました。
>これについては、解りません。(なった事がない?)
>
>実際、どういった状態で試されているのか解らないし、カスタマイズ及び今作っているプログラムに組み込んで試された結果なのか、それらが解らないので何ともいえないです。
>
>質問者で意味不明なのは、カスタマイズや、今作っているプログラムに組み込んで動きません、エラーになります。と言われても、こちらは単品で動く物を書いているから、かみ合わない事も多々あります。
>この辺はどうなのでしょうか?

【36816】Re:オートフィルタの結果を他のオートフ...
発言  Jaka  - 06/4/14(金) 16:08 -

引用なし
パスワード
   最近、脳軟化が始まっているのか、シートがあっちゃこっちゃ入れ替わるとついていけなくなります。
1、2分ほど前に試し打ちして、捨てた台をこれ打ったっけ?ってな事になるので、
前日との釘の比較はまず無理状態です。
だから、WS5.Parentとなんて書かれると、WS5って何?状態。
最近に始まった事じゃないけどね!
こんなですから、まだ全体を把握で出来ていません。
頭がぐにゃぐにゃ回っている状態で気づいた事。(まとまってなくてすみません。)

1、
下記変数の宣言がされていないようですが...。
Dim Cel As Range, CelAry() As Range
           ↑特にこの配列、これがないとエラーになると思います。
            変数が定義されていないって....。
2、        
>’オートフィルタをかけた仕入シートのBE列の最後の行
> Set 範囲 = WS5.Range("BE65536").End(xlUp).Offset(1)
これは、いつ取得しているのでしょうか?
いつと言うのは漠然としすぎるので、オートフィルタをかけた後でしょうか?
オートフィルタのコードが書いてなかったので、手動でオートフィルタをかけているのかな?
と、思います。
オートフィルタをかけた後に、フィルタ最終行を
Range("BE65536").End(xlUp)・・・
で、取得するととんでもない事になったりするので止めてください。
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=87;id=FAQ

3、
>WS5.Activate
>Range("A1", Range("D65536").End(xlUp)).Offset(,2).Formula = _
>      "=IF(BF1,D1-BE1,""D1"")"
これ、フィルタして表示されているセルに対して関数を書き込んでいるのしょうか?
フィルタして飛び飛びのセルに対して、一括で書き込むことが出来ないです。
1個1個ループ書かないとダメだと思います。
(Unionメソッドの事は、混乱を招きそうなので省きます。)

オートフィルタの全て表示は、こんな感じで出来ます。、

Sub 全表示()  'オートフィルタ解除または全表示時、
        '関数が多いとフリーズしたようになるから計算方法を手動にする。
  Dim CalFLG As Boolean
  With Application
    If .Calculation = xlAutomatic Then
      .Calculation = xlManual
      CalFLG = True
    End If
    Sheets("シート2").ShowAllData '全表示
   
    If CalFLG = True Then
      .Calculation = xlAutomatic
    End If
  End With
End Sub

オートフィルタの解除は、こんな感じでできます。
Sheets("シート2").AutoFilterMode = False

>Sub オートフィルタオフ1()
> Dim Wb1 As Workbook
> Dim WB2 As Workbook
> Dim WS3 As Worksheet
> Dim WS2 As Worksheet
> Dim WS2 As Worksheet
> Set Wb1 = Workbooks("入力.xls") 
> Set WS2 = Wb1.Sheets("入力")
> Set WB2 = Workbooks("集計.xls")
> Set WS3 = WB2.Sheets("在庫")
> Set WS5 = WB2.Sheets("仕入")

>'在庫シートのオートフィルタ解除。
> If WS3.AutoFilterMode = True Then
    ↓ WS4 が上でセットされてないからエラーになります。
>    WS4.Range("B23").AutoFilter
> End If
>End Sub
       ↓
決まったシートのオートフィルタを解除するなら、
こんな感じでいいです。

Sub オートフィルタオフ1()
  WorkBooks("Book1.xls").Sheets("シート2").AutoFilterMode = False
  Doevant '←入れておいた方が、良いと思います。
End Sub

オートフィルタして表示された列の最後の行は、こんな感じ....。

With Sheets("Sheet2").AutoFilter.Range
  With .Columns(1).SpecialCells(xlCellTypeVisible)
    With .Areas(.Areas.Count)
      MsgBox .Rows(.Cells.Count).Row
    End With
  End With
End With

【36820】Re:オートフィルタの結果を他のオートフ...
発言  Jaka  - 06/4/14(金) 17:22 -

引用なし
パスワード
   最終行を求めるだけなら、こんなまだるっこい事するより
>最後の行は、こんな感じ....。
>
>With Sheets("Sheet2").AutoFilter.Range
>  With .Columns(1).SpecialCells(xlCellTypeVisible)
>    With .Areas(.Areas.Count)
>      MsgBox .Rows(.Cells.Count).Row
>    End With
>  End With
>End With

やっぱし、こっちの方が速いかも、項目名の1つ下の状態を確認すれば言いだけだから。
場合によってですかね?
msgbox Range("A1", Range("D65536").End(xlUp)).row

【36828】Re:オートフィルタの結果を他のオートフ...
質問  初心者  - 06/4/14(金) 18:38 -

引用なし
パスワード
   Jakaさん、いろいろとありがとうございます。

>1、
>下記変数の宣言がされていないようですが...。
>Dim Cel As Range, CelAry() As Range
>           ↑特にこの配列、これがないとエラーになると思います。
>            変数が定義されていないって....。
 
上の方のワークブックやシートの変数の宣言の所に表示しているのですが、
コードのすぐ上に宣言したほうがよかったのでしょうか?

>2、        
>>’オートフィルタをかけた仕入シートのBE列の最後の行
>> Set 範囲 = WS5.Range("BE65536").End(xlUp).Offset(1)
>これは、いつ取得しているのでしょうか?
>いつと言うのは漠然としすぎるので、オートフィルタをかけた後でしょうか?
>オートフィルタのコードが書いてなかったので、手動でオートフィルタをかけているのかな?
>と、思います。
>オートフィルタをかけた後に、フィルタ最終行を
>Range("BE65536").End(xlUp)・・・
>で、取得するととんでもない事になったりするので止めてください。
>http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=87;id=FAQ

↑拝見致しました。
オートフィルタをかけた後に取得しておりました。
すぐに書き換えますね。

>3、
>>WS5.Activate
>>Range("A1", Range("D65536").End(xlUp)).Offset(,2).Formula = _
>>      "=IF(BF1,D1-BE1,""D1"")"
>これ、フィルタして表示されているセルに対して関数を書き込んでいるのしょうか?
>フィルタして飛び飛びのセルに対して、一括で書き込むことが出来ないです。
>1個1個ループ書かないとダメだと思います。
>(Unionメソッドの事は、混乱を招きそうなので省きます。)

フィルタして飛び飛びのセルに対して一括で書き込むことはできないということを初めて知りました。
勉強不足で申し訳ありません。

ループさせないと無理なんですね。。
↑ここもちょっと勉強してみます。

>
>オートフィルタの全て表示は、こんな感じで出来ます。、
>
>Sub 全表示()  'オートフィルタ解除または全表示時、
>        '関数が多いとフリーズしたようになるから計算方法を手動にする。
>  Dim CalFLG As Boolean
>  With Application
>    If .Calculation = xlAutomatic Then
>      .Calculation = xlManual
>      CalFLG = True
>    End If
>    Sheets("シート2").ShowAllData '全表示
>   
>    If CalFLG = True Then
>      .Calculation = xlAutomatic
>    End If
>  End With
>End Sub
>
>オートフィルタの解除は、こんな感じでできます。
>Sheets("シート2").AutoFilterMode = False
>
>>Sub オートフィルタオフ1()
>> Dim Wb1 As Workbook
>> Dim WB2 As Workbook
>> Dim WS3 As Worksheet
>> Dim WS2 As Worksheet
>> Dim WS2 As Worksheet
>> Set Wb1 = Workbooks("入力.xls") 
>> Set WS2 = Wb1.Sheets("入力")
>> Set WB2 = Workbooks("集計.xls")
>> Set WS3 = WB2.Sheets("在庫")
>> Set WS5 = WB2.Sheets("仕入")
>
>>'在庫シートのオートフィルタ解除。
>> If WS3.AutoFilterMode = True Then
>    ↓ WS4 が上でセットされてないからエラーになります。
>>    WS4.Range("B23").AutoFilter
>> End If
>>End Sub
>       ↓
>決まったシートのオートフィルタを解除するなら、
>こんな感じでいいです。
>
>Sub オートフィルタオフ1()
>  WorkBooks("Book1.xls").Sheets("シート2").AutoFilterMode = False
>  Doevant '←入れておいた方が、良いと思います。
>End Sub
>
>オートフィルタして表示された列の最後の行は、こんな感じ....。
>
>With Sheets("Sheet2").AutoFilter.Range
>  With .Columns(1).SpecialCells(xlCellTypeVisible)
>    With .Areas(.Areas.Count)
>      MsgBox .Rows(.Cells.Count).Row
>    End With
>  End With
>End With


>最終行を求めるだけなら、こんなまだるっこい事するより
>>最後の行は、こんな感じ....。
>>
>>With Sheets("Sheet2").AutoFilter.Range
>>  With .Columns(1).SpecialCells(xlCellTypeVisible)
>>    With .Areas(.Areas.Count)
>>      MsgBox .Rows(.Cells.Count).Row
>>    End With
>>  End With
>>End With
>
>やっぱし、こっちの方が速いかも、項目名の1つ下の状態を確認すれば言いだけだから。
>場合によってですかね?
>msgbox Range("A1", Range("D65536").End(xlUp)).row

メッセージボックスで表示させる必要はないのですが、表示させた方がいいのでしょうか?

質問ばかりで申し訳ありません。
少しづつですが、エラーの原因がわかってきました。
解読するのに時間がかかってしまって、まだコードは書けていないので、
もう一度調べて書いてみますね。

【36869】めちゃくちゃ間違ってました。
発言  Jaka  - 06/4/17(月) 10:33 -

引用なし
パスワード
   ▼初心者 さん:
>1、
>下記変数の宣言がされていないようですが...。
>Dim Cel As Range, CelAry() As Range
>           ↑特にこの配列、これがないとエラーになると思います。
すみません。これは忘れてください。

>>やっぱし、こっちの方が速いかも、項目名の1つ下の状態を確認すれば言いだけだから。
>>場合によってですかね?
>>msgbox Range("A1", Range("D65536").End(xlUp)).row
>
>メッセージボックスで表示させる必要はないのですが、表示させた方がいいのでしょうか?
いえ、解りやすいかと思ってメッセージボックスに表示させただけです。
メッセージで表示させる必要はないです。

今さらなんですが、上記コードは間違ってます。
ろくに動きません、すみません。
msgbox Range("D65536").End(xlUp).row

【36893】Re:オートフィルタの結果を他のオートフ...
お礼  初心者  - 06/4/17(月) 15:45 -

引用なし
パスワード
   Jakaさん
検証に時間がかかってしまい、今の投稿になりまして申し訳ございません。


やはりオートフィルタをかけた後に最終行を取得しようとしていたのが問題だったようです。

with でそれぞれのシートのオートフィルタをくくり、

オートフィルタを解除してから計算させるという手順に変えると解決しました。

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


▼初心者 さん:
>Jakaさん、いろいろとありがとうございます。
>
>>1、
>>下記変数の宣言がされていないようですが...。
>>Dim Cel As Range, CelAry() As Range
>>           ↑特にこの配列、これがないとエラーになると思います。
>>            変数が定義されていないって....。
> 
>上の方のワークブックやシートの変数の宣言の所に表示しているのですが、
>コードのすぐ上に宣言したほうがよかったのでしょうか?
>
>>2、        
>>>’オートフィルタをかけた仕入シートのBE列の最後の行
>>> Set 範囲 = WS5.Range("BE65536").End(xlUp).Offset(1)
>>これは、いつ取得しているのでしょうか?
>>いつと言うのは漠然としすぎるので、オートフィルタをかけた後でしょうか?
>>オートフィルタのコードが書いてなかったので、手動でオートフィルタをかけているのかな?
>>と、思います。
>>オートフィルタをかけた後に、フィルタ最終行を
>>Range("BE65536").End(xlUp)・・・
>>で、取得するととんでもない事になったりするので止めてください。
>>http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=87;id=FAQ
>
>↑拝見致しました。
>オートフィルタをかけた後に取得しておりました。
>すぐに書き換えますね。
>
>>3、
>>>WS5.Activate
>>>Range("A1", Range("D65536").End(xlUp)).Offset(,2).Formula = _
>>>      "=IF(BF1,D1-BE1,""D1"")"
>>これ、フィルタして表示されているセルに対して関数を書き込んでいるのしょうか?
>>フィルタして飛び飛びのセルに対して、一括で書き込むことが出来ないです。
>>1個1個ループ書かないとダメだと思います。
>>(Unionメソッドの事は、混乱を招きそうなので省きます。)
>
>フィルタして飛び飛びのセルに対して一括で書き込むことはできないということを初めて知りました。
>勉強不足で申し訳ありません。
>
>ループさせないと無理なんですね。。
>↑ここもちょっと勉強してみます。
>
>>
>>オートフィルタの全て表示は、こんな感じで出来ます。、
>>
>>Sub 全表示()  'オートフィルタ解除または全表示時、
>>        '関数が多いとフリーズしたようになるから計算方法を手動にする。
>>  Dim CalFLG As Boolean
>>  With Application
>>    If .Calculation = xlAutomatic Then
>>      .Calculation = xlManual
>>      CalFLG = True
>>    End If
>>    Sheets("シート2").ShowAllData '全表示
>>   
>>    If CalFLG = True Then
>>      .Calculation = xlAutomatic
>>    End If
>>  End With
>>End Sub
>>
>>オートフィルタの解除は、こんな感じでできます。
>>Sheets("シート2").AutoFilterMode = False
>>
>>>Sub オートフィルタオフ1()
>>> Dim Wb1 As Workbook
>>> Dim WB2 As Workbook
>>> Dim WS3 As Worksheet
>>> Dim WS2 As Worksheet
>>> Dim WS2 As Worksheet
>>> Set Wb1 = Workbooks("入力.xls") 
>>> Set WS2 = Wb1.Sheets("入力")
>>> Set WB2 = Workbooks("集計.xls")
>>> Set WS3 = WB2.Sheets("在庫")
>>> Set WS5 = WB2.Sheets("仕入")
>>
>>>'在庫シートのオートフィルタ解除。
>>> If WS3.AutoFilterMode = True Then
>>    ↓ WS4 が上でセットされてないからエラーになります。
>>>    WS4.Range("B23").AutoFilter
>>> End If
>>>End Sub
>>       ↓
>>決まったシートのオートフィルタを解除するなら、
>>こんな感じでいいです。
>>
>>Sub オートフィルタオフ1()
>>  WorkBooks("Book1.xls").Sheets("シート2").AutoFilterMode = False
>>  Doevant '←入れておいた方が、良いと思います。
>>End Sub
>>
>>オートフィルタして表示された列の最後の行は、こんな感じ....。
>>
>>With Sheets("Sheet2").AutoFilter.Range
>>  With .Columns(1).SpecialCells(xlCellTypeVisible)
>>    With .Areas(.Areas.Count)
>>      MsgBox .Rows(.Cells.Count).Row
>>    End With
>>  End With
>>End With
>
>
>>最終行を求めるだけなら、こんなまだるっこい事するより
>>>最後の行は、こんな感じ....。
>>>
>>>With Sheets("Sheet2").AutoFilter.Range
>>>  With .Columns(1).SpecialCells(xlCellTypeVisible)
>>>    With .Areas(.Areas.Count)
>>>      MsgBox .Rows(.Cells.Count).Row
>>>    End With
>>>  End With
>>>End With
>>
>>やっぱし、こっちの方が速いかも、項目名の1つ下の状態を確認すれば言いだけだから。
>>場合によってですかね?
>>msgbox Range("A1", Range("D65536").End(xlUp)).row
>
>メッセージボックスで表示させる必要はないのですが、表示させた方がいいのでしょうか?
>
>質問ばかりで申し訳ありません。
>少しづつですが、エラーの原因がわかってきました。
>解読するのに時間がかかってしまって、まだコードは書けていないので、
>もう一度調べて書いてみますね。

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