Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Home : Excel 2016)
マクロでデータが上手く取り込めません。
投稿日時: 19/09/27 21:23:43
投稿者: etty3080

「シート@」と「シートA」のデータを「まとめ」シート1枚に取り込むマクロを作成したいのですがなかなかうまくいきません。
シート@とAにそれぞれ2〜10行目までデータが入っているとすると、シート@は2〜10行目全部取り込めるのですが、シートAの方は10行目しか取り込みができない状況です。
全部情報を取り込めるようにするにはどこを修正すれば良いでしょうか?
ご教示いただけますと幸いです。よろしくお願いいたします。
 
Dim f As Worksheet
   Set f = Sheets("シート@")
   With Sheets("まとめ")
       For i = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row
          If f.Cells(i, 18).Value = "A" Then
                   .Cells(i, 1) = f.Cells(i, 2)
                   .Cells(i, 2) = f.Cells(i, 4)
                   .Cells(i, 3) = f.Cells(i, 8)
                   .Cells(i, 4) = f.Cells(i, 9)
                   .Cells(i, 5) = f.Cells(i, 10)
                   .Cells(i, 6) = f.Cells(i, 11)
                   .Cells(i, 7) = f.Cells(i, 20)
                   .Cells(i, 8) = f.Cells(i, 13)
                   .Cells(i, 9) = f.Cells(i, 14)
                   .Cells(i, 10) = f.Cells(i, 15)
                   .Cells(i, 11) = f.Cells(i, 16)
                   .Cells(i, 12) = f.Cells(i, 17)
           Else
                   .Cells(i, 1) = f.Cells(i, 3)
                   .Cells(i, 2) = f.Cells(i, 5)
                   .Cells(i, 3) = f.Cells(i, 6)
                   .Cells(i, 5) = f.Cells(i, 7)
 
           End If
 
                   .Cells(i, 4).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 5).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 6).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 7).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 8).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 9).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 10).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 11).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 12).NumberFormatLocal = "[h]:mm"
       Next
   End With
 
   Dim i As Long
   Set f = Sheets("シートA")
   With Sheets("まとめ")
       For r = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row
          If f.Cells(r, 18).Value = "A" Then
                   .Cells(i, 1) = f.Cells(r, 2)
                   .Cells(i, 2) = f.Cells(r, 4)
                   .Cells(i, 3) = f.Cells(r, 8)
                   .Cells(i, 4) = f.Cells(r, 9)
                   .Cells(i, 5) = f.Cells(r, 10)
                   .Cells(i, 6) = f.Cells(r, 11)
                   .Cells(i, 7) = f.Cells(r, 20)
                   .Cells(i, 8) = f.Cells(r, 13)
                   .Cells(i, 9) = f.Cells(r, 14)
                   .Cells(i, 10) = f.Cells(r, 15)
                   .Cells(i, 11) = f.Cells(r, 16)
                   .Cells(i, 12) = f.Cells(r, 17)
           Else
                   .Cells(i, 1) = f.Cells(r, 3)
                   .Cells(i, 2) = f.Cells(r, 5)
                   .Cells(i, 3) = f.Cells(r, 6)
                   .Cells(i, 5) = f.Cells(r, 7)
 
           End If
 
                   .Cells(i, 4).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 5).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 6).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 7).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 8).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 9).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 10).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 11).NumberFormatLocal = "[h]:mm"
                   .Cells(i, 12).NumberFormatLocal = "[h]:mm"
       Next
   End With
 
End Sub 
 
 
 

回答
投稿日時: 19/09/27 21:33:44
投稿者: WinArrow
投稿者のウェブサイトに移動

>シートAの方は10行目しか取り込みができない状況です
の問題点は、
変数:i が変化しないからです。
 
コード修正ヶ所(シート@もシートAも)
> For i = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row

       For i = 2 To f.Cells(f.Rows.Count, 1).End(xlUp).Row

投稿日時: 19/09/27 21:48:00
投稿者: etty3080

WinArrowさん
 
ご回答ありがとうございます!
仰る通りに
【シート@】
For i = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To f.Cells(f.Rows.Count, 1).End(xlUp).Row
【シートA】
For r = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To f.Cells(f.Rows.Count, 1).End(xlUp).Row
 
に変更してみましたが、
「実行時エラー1004 アプリケーション定義またはオブジェクト定義のエラーです」
と表示が出てきてしまいます・・・。
どのようにすればよろしいでしょうか?

回答
投稿日時: 19/09/27 22:15:15
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
【シートA】
For r = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row
 ↓
 For i = 2 To f.Cells(f.Rows.Count, 1).End(xlUp).Row
  

 
For r を
For i に変更したらダメでしょう。

回答
投稿日時: 19/09/27 22:18:12
投稿者: WinArrow
投稿者のウェブサイトに移動

ところで
シート@

For i = 2 To〜〜〜〜
と変数:iを使っていますが、
データ定義はどこにあるのですか?
 
また、なぜ、シートA側に
Dim i As Long
があるのですか?

投稿日時: 19/09/27 22:42:54
投稿者: etty3080

WinArrowさん
 
見落としておりました。申し訳ございません。
 
シートAのほうは
For r = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row
 ↓
For r = 2 To f.Cells(f.Rows.Count, 1).End(xlUp).Row
に変更し先ほどのエラーはなくなったのですが、
それでもやはり10行目のみしか取り込まれないようです・・・
 
Dim i As Longは入力誤りでしたので消しました。
 
今まではシート@のみ取り込めばOKだったのですがシートAも取り込む必要が出てきたため
既にあったシート@のコードをコピーしてA用に下に貼り付けてみただけで、
それまでマクロというものをまともに触ったことがなく、データ定義というものも全く理解できておりません・・・
こんなんで本当に申し訳ございません><
 
 

回答
投稿日時: 19/09/28 00:45:19
投稿者: simple

後半のシートAの部分は、こんな風にすると良いのでは?
■をつけた行が変更箇所です。
 

    Set f = Sheets("シートA")
    With Sheets("まとめ")
        For r = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row
            If f.Cells(r, 18).Value = "A" Then
                .Cells(i, 1) = f.Cells(r, 2)
                .Cells(i, 2) = f.Cells(r, 4)
                .Cells(i, 3) = f.Cells(r, 8)
                .Cells(i, 4) = f.Cells(r, 9)
                .Cells(i, 5) = f.Cells(r, 10)
                .Cells(i, 6) = f.Cells(r, 11)
                .Cells(i, 7) = f.Cells(r, 20)
                .Cells(i, 8) = f.Cells(r, 13)
                .Cells(i, 9) = f.Cells(r, 14)
                .Cells(i, 10) = f.Cells(r, 15)
                .Cells(i, 11) = f.Cells(r, 16)
                .Cells(i, 12) = f.Cells(r, 17)
            Else
                .Cells(i, 1) = f.Cells(r, 3)
                .Cells(i, 2) = f.Cells(r, 5)
                .Cells(i, 3) = f.Cells(r, 6)
                .Cells(i, 5) = f.Cells(r, 7)

            End If

            .Cells(i, 4).Resize(1, 9).NumberFormatLocal = "[h]:mm"  '■一行で書けます。
            i = i + 1   ' ■書込先の行を一行進める
        Next
    End With

回答
投稿日時: 19/09/28 08:49:21
投稿者: simple

変数の宣言について追記します。
 
変数i だけに注目して骨格を示すと、こんな感じになっていました。

Sub test()
    For i = 2 To 10  '簡単にしています。
        '省略
    Next
    Dim i As Long
    .Cells(i, 1) = f.Cells(r, 2)
End Sub
つまり、
@変数 i の宣言する前に、 変数i を使い、
Aその後の行で、 Dim i As Long という宣言を行っている
わけである。
 
質問者さんは、何もコメントしていなかったが、上記どおりだとすると
本当はエラーが起きるはず。
なお、明示的に変数の宣言を強制するものとして、モジュールの先頭に書く
Option Explicitステートメントというものがあります。
これをあなたは使っていないようだが、
(1) Option Explicit としていれば、
    宣言する前に変数i を使っているので、コンパイルエラーになったはず。
(2) Option Explicit としていない場合、
    Dim i As Long  がコンパイルエラーとなり、エラーメッセージは、
    「同じ適用範囲内で宣言が重複しています」というものになるはず。
  (最初に使った段階で、暗黙的にVariantとしたとみなされ、
    そのあとLongとせよとは何だ、ということになる)

エラーになるはずなのに、ならないのは不思議と思うので、
事実関係だけを確認のため教えて欲しい、というのが第一点。
---------------------------------
次に、あなたが取った対応策として、
Dim i As Long
を消しました、
とあるが、それは本来的な対応ではありません。
 
変数は必ず宣言するようにしてください。
変数を宣言しない場合に起こりうる弊害については、
「変数って宣言しなくちゃいけないの?」
http://officetanaka.net/excel/vba/beginner/06.htm
を参照してください。
 
そして、宣言する位置は、その変数を使用する前であればどこでも可。
普通は前にまとめて宣言することが多い。使用する直前で宣言する方法もあります。
 
-------------------------------
以下、上記の記事と重複するが、やって欲しいことを改めて書いておきます。
Option Explicit
をモジュールの一行目に挿入するようにして下さい。
そうすれば、未宣言の変数があれば警告が出て、 
しかも場所を特定してくれますから、原因が直ぐに判明します。[/color] 

ツール − オプション − 編集 で
「変数の宣言を強制する」にチェックを入れておけば、
今後、モジュールを作成した時点で、Option Explicitが自動的に挿入されるので、
手間が省けます。
一度だけチェックを入れておけば、今後一切、気にする必要はありません。

回答
投稿日時: 19/09/28 17:51:13
投稿者: WinArrow
投稿者のウェブサイトに移動

申し遅れましたが、
私の最初のレス

WinArrow さんの引用:
>シートAの方は10行目しか取り込みができない状況です
の問題点は、
変数:i が変化しないからです。
 
コード修正ヶ所(シート@もシートAも)
> For i = 2 To f.Cells(Rows.Count, 1).End(xlUp).Row

       For i = 2 To f.Cells(f.Rows.Count, 1).End(xlUp).Row

の後半の「コード修正ヶ所」に関する記述は、
>シートAが10行目しか・・・・
に対応する回答ではありません。
 
>シートAが10行目しか・・・・
に対応する回答は、「変数:i が変化しないからです。」です。

回答
投稿日時: 19/09/29 17:54:59
投稿者: simple

返事がないのですが、もし解決していたら閉じてください。
まだでしたら、追加質問して下さい。
 
念のため、説明を追加しておきます。
例として下記のコードを考えます。

Sub test()
    Dim i as Long
    Dim r as Long
    
    For i = 1 To 10
         '作業1  省略。
    Next
   
  For r = 1  to 5
        Cells(i,1).Value = r 
    Next
End 

【なにがマズかったのか】
(最初のループを抜けた段階で、変数 i は何になっていると思いますか?
10と思うかもしれませんが、Next の効果があって、11になっています。)
二つ目のループでは、11行目から書き込まれます。
しかし、ループ変数は r です。
ひとつずつ自動的に増加してくれるのは、あくまで r です。 
i は少しも増加しません。
したがって、常に11行目のセルに常に書込がされます。
そこが問題だったのです。
 
【どうしたらよいのか】
ループ内の処理が終わるつど、 i をひとつずつ増やして行けばよいのです。
  For r = 1  to 5
        Cells(i,1).Value = r 
        i = i + 1
    Next
とします。
i と i + 1 は等しくなんか無いのにぃ、と思うかも知れません。
これは気持ちとしては、
 i ← i + 1
という感じです。
 i に 1を加えたものを、改めて変数 i に代入しなさい、という意味です。
 
これで解りますか?

投稿日時: 19/09/30 09:09:54
投稿者: etty3080

返信が遅れて申し訳ございません。
無事に解決致しました!
コメント下さった皆様、ありがとうございます!

回答
投稿日時: 19/09/30 09:22:46
投稿者: simple

こちらの説明分かりました?
 
なお、解決したら閉じてくださいね。

トピックに返信