ＥＸＣＥＬ ＶＢＡで思ったように動いてくれないのですが。

質問

度々すみません。お世話になります。Win98-EXCEL2000での処理です。

やりたいことは、A～Eまでのファイルを変数で開き、各ファイルの総シート数の1/2のシート数のシート(1)～シート(?)までを処理し、１つ目のファイルのシート処理が終わった段階で合計をして、別シートに合計のみを転記、次のファイルの処理へ移動したいのです。
WithやLoopの使い方や、SETやNextはどこで書いていいのかがよく解っていないので、何をどこでどう書いてよいのか教えてください。多分意味不明だと思うので、補足しますのでお願いします。今の処理だとSheetYOの合計処理がすっ飛ばされているような気がします。
Sub TEST()
各種変数宣言
For iii = 1 To 4
If iii = 1 Then f = "A_log"
ElseIf ・・・残りB～Eまで入力
Exit For
End If
Next iii

Workbooks.Open Filename:=f & ".xls"
c = Worksheets.Count / 2　

With sheetSET
Set sheetSET = Workbooks(f & ".xls").Sheets(iiii)
Set sheetYO = マクロを書いているファイルです。

Do Until iiii = c
For iiii = 1 To c
　 If iiii = 1 Then
　　　　　オートフィルタ処理
Sheets(iiii).AutoFilter.Copy
sheetYOにコピー処理
Else
上記と同じオートフィルタ処理
Sheets(iiii).AutoFilter.Range.Offset(1, 0).Copy
上記の最終行+1にコピー処理
Exit For
End If
Next iiii
Loop
End With

With sheetYO
　　　別シートに合計のみを転記
End With
Workbooks(f & ".xls").Close False
End Sub

回答No.4

TTak · ベストアンサー

ループで変数を代入して処理する場合は、処理のコードはすべてループ内に記述する必要があります。また、処理が複雑な場合はサブルーチン（別のプロシージャ）で変数を渡して、処理を飛ばしてもいいです。

さて、内容的には少し判りにくいので、次のような処理かなぁと予想してます。違ってたら、補足してください

ファイルAを開く
開いたファイルのシート1のオートフィルタ結果をコピー
現在のファイルのシートYOに結果をペースト
開いたファイルのシート2のオートフィルタ結果をコピー
現在のファイルのシートYOに結果をペースト
開いたファイルのシート3のオートフィルタ結果をコピー
・・・シートの半数分繰り返す・・・
ファイルAを閉じる
ファイルBを開く
・・・以下ファイルAと同様にFまで繰り返す・・・

するとマクロの例は次のようになります。オートフィルタ云々、コピーペースト云々は、別に質問してください。このマクロは動作確認してませんので、流れだけつかんでください。

Sub test()
Dim iii As Integer
Dim iiii As Integer
Dim openFileName As String '---オープンファイル名
Dim openFilePath As String '---オープンファイルパス
Dim c As Integer '---ファイルに含まれるシート数÷２

'iiiを1から6までループ
For iii = 1 To 6
'Chr関数を使って文字コードからA～E文字列を取得
'し、ファイル名A～E_log.xlsを順次作成する
　openFileName = Chr(64 + iii) & "_log.xls"
'開くファイルが格納されているフォルダのパスを取得
'ここでは、このマクロがあるファイルと同じパスを指定
　openFilePath = ThisWorkbook.Path
'ファイルを開く
　Workbooks.Open Filename:= _
　openFilePath & "\" & openFileName
'開かれたファイルのシート数÷２の値を取得
'シート数が奇数の場合は切り捨て
　c = Int(Worksheets.Count / 2)
'iiIiを1からcまでループ
　For iiii = 1 To c
　　If iiii = 1 Then
　　'シート1の場合の処理
　　　Sheets(iiii).AutoFilter.Copy
　　　'****************************
　　　'シートYOへペースト処理マクロ
　　　'をここに記入する
　　　'****************************
　　Else
　　'シート1以外の場合の処理をここに記入
　　　Sheets(iiii).AutoFilter.Range.Offset(1, 0).Copy
　　　'****************************
　　　'シートYOへペースト処理マクロ
　　　'をここに記入する
　　　'****************************
　　End If
　Next iiii
Workbooks(openFileName).Close False
Next iii
End Sub

お礼

TTakさん、いつもありがとうございます。
すごいです、その通りの処理がしたいんです！
ですが、私の方の理解が遅くてループの説明んとこでつまずいてます(T_T)
この'Chr関数を使って文字コードからA～E文字列を取得
'し、ファイル名A～E_log.xlsを順次作成する
ですが、説明が悪くて申し訳ありません。例としてＡ～Ｅとしてしまいましたが、実際のファイル名は各々恐ろしくややこしい名前がついてます。が、その場合もこれでいけるのでしょうか・・・？

補足

度々申し訳ありあません。「openFileName = Chr(64 + iii) & "_log.xls" 」がちょっと理解できなかったのでlaputartさんのを流用させて頂いて、チェンジディレクトリの仕方が分からなくて「openFilePath =」以降に普通に入力したら、当然ですがエラーになってしまいました。お手数ですが、方法教えてください。

For iii = 1 To 4
Select Case iii
Case 1
f = "ALR252偏貼_log"
Case 2
f = "ALR252Cof_log"
Case 3
f = "ALR252終検セル_log"
Case 4
f = "ALR252M2点灯_log"
End Select

openFileName = f & ".xls"
openFilePath =
　　　　ChDrive "Z"
　　　　ChDir "\共有\検査データ\外注データ\コスモ電子\Alr252\update".Path

回答No.5

TTak

ファイル名が単純で連続な文字でないならChr関数は使えません。ファイル名に関わらず、特定のフォルダ内、たとえば"C:\My Documents"内のexcelファイルすべてについて同じ処理をするなら、FileSearch関数とFor～Next文の組み合わせが便利です。

With Application.FileSearch
　.NewSearch
　.LookIn = "C:\My Documents"
　.FileType = msoFileTypeExcelWorkbooks
　If .Execute() > 0 Then
　　For iii = 1 To .FoundFiles.Count
　　　openFileName = .FoundFiles(i)

　　'----この間の処理は前の解答に同じ

　　Next iii
　End If
End With

所感ですが、このような処理をさせるコーディングはExcelVBAでも、上級者が扱う範囲と思います。

nanamiさんはいつも実務的な処理に関する質問が多いですね(^^;)

VBAは使うことができると便利ですが、必ずしもすべてに有効というわけではありません。ちなみに私の場合、いかにVBAを使わずに済ませるか、たとえ使ってもいかに簡素であるかということを最初に考えています。

そのためにフォーマットの方を変更したり、シート名やファイル名を変えたりする場合もありますし、場合によってはフォーマットを作った業者に頼んで、変更して貰う場合もあります。フォーマットが統一されると、自分で作ったプログラムをコンポーネント化して、他のプログラムから呼び出して使うこともできるのです。

まずは、今やっている作業をフローにして、共通化できる部分、変数にする部分を十分に把握してから質問してみてはいかがでしょう。フローを示すことができれば抽象的でもかまいませんよ。解答も的確に付くでしょう。

がんばってください(^^).

お礼

ごめんなさい、補足が入れ違いになっちゃいましたね。

ＶＢＡ使える方は便利でいいなと思いましたが、「いかに使わないか」を考えるんですね・・・反省です。

今、各ファイルごとにマクロを組んでいて、５回ボタンを押せばちゃんと動くのですが、実際使用する人に１つのボタンでなんとかならないの？といわれてちょっとよくばって力量以上のことをしてしまいました。
今回もまた聞いたことない文が出てきて、やっぱりこれ以上は無理かなと・・・
いつも親切なアドバイスありがとうございます。
また、宜しくお願いします。

回答No.3

happypoint

こんにちは。
回答ではなく、アドバイスを。

＞WithやLoopの使い方や、SETやNextはどこで書いていいのかがよく解っていないので、
＞何をどこでどう書いてよいのか教えてください

たとえば、同じセルに対して書式をいくつも設定するときなどのように、
Withを使えば対象となるオブジェクトの指定をまとめて記述できるので、
見た目すっきり＆処理も若干早くなります。

しかし、使い方がよくわかっていない時点で、
無理にWithは使わなくてもいいのではないでしょうか？
現状では、ご自分でもどのようなプログラム構造になっているか、
把握できていらっしゃらないのでは？

ひとまずWithをつかわない（対象を省略しない）書き方で、
[動作するコード]を書くことが肝心ですよ。

自分で書いたコードの全体像がつかめたら、
それからコードの整理に着手すれば良いのではないでしょうか。

あと、For～Nextなどのように、
つねに一対のものとして扱う処理は、
最初にFor～Nextの構文を完成させておいて、
あとからその行間にコードを挿入していく、というスタイルのほうが
間違いが少なくなると思います。

それと、もう実践されているかもしれませんが、
行頭にタブなどを効果的に挿入して、ループの範囲を字下げするなどして
極力コードの視認性を良くする工夫が必要かもしれませんね。

あと、Setをする位置ですが、
Setしたものを実際に参照する行より前に書かないと意味がないですよ。
With sheetSET の行が、
Set sheetSET = …　の行よりも前に来てますよね。
これでは、まずいわけです。

お礼

ありがとうございます。
そうなんですよね、上から順番に書いてくからいつも？？？が多くなってしまって。。。
ありがとうございました。作り方をまず変えてみますね。

回答No.2

laputart

分法的にIF文を構造的にしないとエラーに
なりませんか
（次の行のELSEから．．．．）
それと入れ子にした方が構想的なので、処理の詳細は
分かりませんが全体の構造は私ならこんな風に設計
します。

それとiiiiのループ文ですが
２回目以降に
Do Until iiii = c
に来た時iiii=cならその後が実行されませんので
こんな構造にしました。
---------------------

For iii = 1 To 4
Select Case iii
Case 1
f = "A_log"
Case 2
f = "A_log"
Case 3
f = "A_log"
Case 4
f = "A_log"
End Select

'-----ファイルオープン------
Workbooks.Open Filename:=f & ".xls"
c = Worksheets.Count / 2

With sheetSET
Set sheetSET = Workbooks(f & ".xls").Sheets(iiii)
Set sheetYO = マクロを書いているファイルです。
iiii = 1
'----------ループの始め--------
Do Until iiii = c
Select Case iiii
Case 1
オートフィルタ処理
Sheets(iiii).AutoFilter.Copy
sheetYOにコピー処理
Case Else
上記と同じオートフィルタ処理
Sheets(iiii).AutoFilter.Range.Offset(1, 0).Copy
'上記の最終行+1にコピー処理
End Select

iiii = iiii + 1
Loop
'----------ループ終了--------
End With
'-----------------
With sheetYO
別シートに合計のみを転記
End With
'------------------
Workbooks(f & ".xls").Close False

Next iii

お礼

ありがとうございます！
この構造に変えたら、ループの終了までは正常に動いてくれました！
そか、Select Case を使ったほうがいいんですね。

今度は「別シートに合計のみを転記 」の段階でつまずいてますが・・・見直してまた解らなかったら宜しくお願いします。
ありがとうございました。

回答No.1

taknt

なんか いろいろ問題がありそうですね。

まず、最初のほう

For iii = 1 To 4
If iii = 1 Then f = "A_log"
ElseIf ・・・残りB～Eまで入力
Exit For
End If
Next iii

これは 最後 iiiが 4になりますので、
多分 fに１から順に A B C D E とセットしていったら
４番目の Ｄと なって ループから 抜けるでしょう。

つまり、その前まで A B C とセットしたのが 意味が
なくなります。

もう少し、ロジックを見直す必要がありますね。

お礼

そうなんですよね。
最初に開くファイルが最後のになったので、Exit For～Nextiiiを最後に持ってきたらExit Forに対するForがありませんとか、同様にIfがありませんとか出てきました。
なのでこういう場合はどう処理したらよいのでしょうか。