VBA:空白セルに、上の行のセルに入力されている同じ値を埋めていく方法

Question

添付図のようなデータを取り込んだExcelデータがあります。

添付のように、
やりたいこと１：空欄となっているA,B列の行を自動的に上のセルの内容で埋めて、
やりたいこと２：空白が埋まったら値が入っている最下行の1つ下の行から新しいデータを取り込むようなマクロを組みたいのですが、うまくいきません…

Option Explicit
Sub データ読込()
    '転記元ファイルの取得用変数
    Const FILE_PATH = "C:あいうえお"
    Dim sFileName As String

    '転記元のシートの変数
    Dim copyWb As Excel.Workbook
    Dim copyWs As Excel.Worksheet
    '転記先のシートの変数
    Dim pasteWs As Excel.Worksheet
    '出力行
    Dim iPasteRow As Integer

    '転記先のシートを取得
    'マクロが書かれているブックの、アクティブなシート
    Set pasteWs = Excel.ThisWorkbook.ActiveSheet

    '出力する空白セルの指定
    Dim pasteCell As Excel.Range
    Set pasteCell = pasteWs.Cells(pasteWs.Rows.Count, "A").End(xlUp).Offset(1)

    '出力行を取得（先頭行）
    iPasteRow = pasteCell.Row


ソースコード途中省略

　　　　　　　　　With pasteWs
                Dim a As String
                Dim r As Range
                a = "A2:B" & Range("C" & Format(Rows.Count)).End(xlUp).Row
                For Each r In Range(a)
                If r.Value = "" Then r.Value = r.Offset(-1, 0).Value
                Next
                End With 'pasteWs

            '空白も埋まったらデータ取り込みのため、行を１行下に進める
            iPasteRow = iPasteRow + 1
         End If

マクロを実行すると、空白は埋まらず、Ａ列の値が入っている次の行から一行ずつ新しいデータを取り込んでしまいます。
過去にも同じような質問をさせていただいており、恐縮ですが、
いまいちどうソースコード組みなおせばよいのかわかりません。

どうかお助けください。よろしくお願いします。

Answer 1

Range、Rows、Columns、Cells、いずれも頭に「pasteWs.」を付けなければ、
自動的に ActiveSheet が参照されます。

なので、システム開発的な目線からは必須です。

折角「With pasteWs」としていても、
ご自身が意味を分かって使ってなければ、
無意味なバグを引き起こして
不要な時間を過ごすことになります。

余談ながら、With ～　End With の「～」部分のコードは、
インデントを下げると見やすいと思いますし、
With をかけている、という事を意識しやすいです。

Comments

[退会済みユーザー]

ありがとうございます。
Range、Rows、Columns、Cells、いずれも頭に「pasteWs.」を付けなければ、
自動的に ActiveSheet が参照されます。
＝＞withをつけたらすべて適用されると思っていたので、ご指摘くださり理解に助かりました。
試してみます。

[ExcelVBAer]

With の範囲では、.Range の様に、[.]から記述できます。
この[.]の前には、「pasteWs」があるという事になり、「pasteWs」を省略できます。
それが With の意味です。

[ExcelVBAer]

余談ですが、「Format(Rows.Count)」の Format は不要では？
文字列型に変換させるだけなら、Cstr を使うべきです。

[退会済みユーザー]

追加のアドバイス誠にありがとうございます。
まだうまく実行できていないので自力で理解できず恐縮なのですが、
いただいた最後から2つ前のアドバイスは下記のようにソースコードを記述するという理解であってるでしょうか？
　　　　　With pasteWs
Dim a As String
Dim r As Range
a = "A2:B" & .Range("C" & Format(Rows.Count)).End(xlUp).Row
For Each r In .Range(a)
If r.Value = "" Then r.Value = r.Offset(-1, 0).Value
Next
End With 'pasteWs

[退会済みユーザー]

もう一つのアドバイスもありがとうございます。
文字列は、Cstrとformatとstringで３つの関数を使い分ける必要があるということなのでしょうか？？
今回の場合は、日付のような表示設定をしていないので、Cstｒという関数を使うべきということでしょうか？？

[ExcelVBAer]

Withの使い方、あってます。
ただ、正確には「Rows」も「.Rows」とすべきです。
(稀なケースかもしれませんが、xlsとxlsx が混在している場合でエラーになる可能性が考えられます)

[ExcelVBAer]

使い分ける必要、とも言えるかもしれませんが、
正確な使い方をすべき、という話ですね。

Format、CStr、String関数について、
各関数がどういう処理の為の関数なのか調べれば分かると思います。

[退会済みユーザー]

早速のご回答ありがとうございます。
場所を具体的に指示することが大事だったのですね。
型と関数などまだまだ理解がごっちゃなので、引き続き調べてみたいと思います。
アドバイスありがとうございました！

Answer 2

pasteWs.Range("C1").End(xlDown).Row
の方が良いかと。
空欄となっているA,B列の行を自動的に上のセルの内容で埋めるのであれば
下記のようなコードでも実現可能です。ご参考までに。

Dim I As Integer
Dim intEndRow as Integer

intEndRow = pasteWs.Range("C1").End(xlDown).Row
For I = 2 to intEndRow 
    If Nz(pasteWs.Range("A" & I-1),"")<>"" AND Nz(pasteWs.Range("A" & I),"")="" then
        pasteWs.Range("A" & I)=pasteWs.Range("A" & I-1)
    End if
    If Nz(pasteWs.Range("B" & I-1),"")<>"" AND Nz(pasteWs.Range("B" & I),"")="" then
        pasteWs.Range("B" & I)=pasteWs.Range("B" & I-1)
    End if
Next

Comments

[退会済みユーザー]

ありがとうございます！
Ifの次のＮｚはどういう意味なのでしょうか？？
ＮｚはDimの文章を書かなくてよいのでしょうか？

[Mustang32861341]

すみません、癖でAccessの関数を記述してしまいました。
If pasteWs.Range("A" & I-1)<>"" AND pasteWs.Range("A" & I)="" then
で問題ないです。

[退会済みユーザー]

ご返信ありがとうございました。
Ａｃｃｅｓｓはさわったことがなかったのですが、エクセルよりＡｃｃｅｓｓをこのような複数のデータをまとめて分析する場合は使ったほうがよかったのでしょうか？？

[退会済みユーザー]

まだうまく実行ができずにおりまして、追加のヒントを賜れますと幸いです。
「実行時エラー４２４：オブジェクトが必要です」が表示され、
intEndRow = pasteWs.Range("C1").End(xlDown).Row
が黄色くハイライトされます。
Ｃ１を他のセルを指定してみても、同じエラーが出てしまいます。

[Mustang32861341]

運用目的にもよりますので一概には言えませんが、例えば複数のPCで入力して集計したい場合や
マスタを作成し、入力・管理を厳格化したい場合などはAccessのほうが良いと思います。

[Mustang32861341]

pasteWs の適用範囲外に記述していませんか？

[退会済みユーザー]

あ！オブジェクトは、このマクロを実行するシートのことですね！！
うまく実行でき、一気に空白に入れたい値が入力されていきました！！
一個前のコメントは無視くださいませ。失礼いたしました！
ご教示ありがとうございます。大変助かりました。

[退会済みユーザー]

早速のご返信ありがとうございます。
Ａｃｃｅｓｓというアプリ知らなかったので、勉強してみようと思います。
教えてくださりありがとうございます。

なお、実行できたのですが、次のコードがよく内容が理解できていません。
理解があっていますでしょうか？

intEndRow = pasteWs.Range("C1").End(xlDown).Row
→現在データが入っている最終行を探す。（今回の場合は、Ｃ列に値が下まで入っているので、その最終行のを探す。）

For I = 2 To intEndRow
→２がなぜなのかと、intEndRowとＩの違いがよくわかりません。

度々の質問恐縮です。
理解のヒントいただければ幸いです。

Answer 3

別のシート(ブック?)のＡ・Ｂ列を埋めに行っていませんか？

Range("C" & Format(Rows.Count)).End(xlUp).Row
をウォッチ式に入れるなどして、値を確認してみて下さい。

慣れない構文なので確信は持てませんが
With pasteWs　が機能していないような……？

Comments

[退会済みユーザー]

ありがとうございます。
ご指摘のとおり、さっぱり機能していないようです。

Answer 4

VBAのマクロを組む場合、マクロの記録で手動実行したものを参考にすると良いと思います。私もマクロを記録して参考にして必要な部分を修正して組んでいました。

Comments

[退会済みユーザー]

ありがとうございます。