入力済み最終行セルを探して同じ行の複数セルの値を一括取得する方法

エクセルVBAセルの操作セルの操作
セルの文字色・背景色まとめて変更

エクセルで指定したセルの右隣のセルの値を、ユーザーフォームに配置したテキストボックスに転記出来れば便利だと思いませんか?

更に見つけたセルの1行上や下の同じ列の値をテキストボックスに、コマンドボタンを押すたびに取得できたら、もっと便利だと思いませんか?

波乗りアヒル
波乗りアヒル

住所録や商品リストであったら便利なので、そんなコマンドボタン作ってみました。

商品修正フォーム

例えば、当たり前ですけどA1セルの右隣といえばB1セルになります。

通常B1セルの値を、ユーザーフォーム1に配置したテキストボックス1に表示する時のVBAコードはこのようになりますよね。

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Text = Range(“B1”).Value
End Sub

または

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Text = Cells(1,2).Value
End Sub

または

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Text = Range(“A1”).Offset(0, 1).Value
End Sub
波乗りアヒル
波乗りアヒル

3つどの方法でもB1セルの値をテキストボックスに表示できる

上記3つのようにセルの場所が決まっていれば簡単にVBAコードを書くことができますが、セルの場所が固定できない場合、入力済み最終行はどうしますか?
スポンサーリンク


スポンサーリンク

入力済み最終行と同じ行のセルの値を複数取得

住所録や商品台帳などでは、登録するたびにリスト最終行の行番号はどんどん増えていきますので、行番号を指定することは困難です。

波乗りアヒル
波乗りアヒル

入力済み最終行を自動選択させよう!

A列の入力済み最終行のセルを選択するVBAコードはこのようになります。

Private Sub CommandButton1_Click()
Cells(Rows.Count, 1).End(xlUp).Select
End Sub

ちなみにCells(Rows.Count, 1)がA列を指定しているので、Cells(Rows.Count, 2)とすれば2列目のB列を指定することができます。

波乗りアヒル
波乗りアヒル

ここから本番!複数のテキストボックスに表示させる

入力済み最終行のセルは先ほどのVBAコードで見つけられるようになりましたので、今度は同じ行のセルの値をテキストボックスに表示させましょう。

まずはシンプルにA列の入力済み最終行のセルの値をテキストボックス1へ、右隣のセル(B列)の値をテキストボックス2へそれぞれ表示させましょう。

Private Sub CommandButton1_Click()
Cells(Rows.Count, 1).End(xlUp).Select
UserForm1.TextBox1.Text = ActiveCell.Value
UserForm1.TextBox2.Text = ActiveCell.Offset(0, 1).Value
End Sub
  1. コマンドボタン1を押したら
  2. 列番号1(A列)の入力済み最終行を選択する
  3. ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
  4. ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
  5. マクロ終了

これで、テキストボックス1と2にそれぞれ入力済み最終行のセルの値が表示されます。

もしテキストボックスの数がたくさんあってもActiveCell.Offset(0, 1).Valueの1を2に代えれば2つ隣になるので、続けてVBAコードを書き続けても問題はなくできます。

波乗りアヒル
波乗りアヒル

面倒なので繰り返し処理で一括表示しよう!

指定した回数分繰り返し処理を行ってくれるFor~Nextを組み合わせますが、For~Nextの詳細はここでは省略します。

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Cells(Rows.Count, 1).End(xlUp).Select
UserForm1.TextBox1.Text = ActiveCell.Value
UserForm1.TextBox2.Text = ActiveCell.Offset(0, 1).Value
For i = 3 To 8
With UserForm1.Controls(“TextBox” & i)
.Text = ActiveCell.Offset(0, i + 3).Value
End With
Next i
Application.ScreenUpdating = True
End Sub
  1. コマンドボタン1を押したら
  2. 画面のちらつきを停止する
  3. 列番号1(A列)の入力済み最終行を選択する
  4. ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
  5. ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
  6. 繰り返し処理「変数i」を宣言して3~8まで行う
  7. ユーザーフォーム1に配置したテキストボックス「i」(3~8という意味)
  8. 表示は選択されたセルの「i」+3の値(右に6列目~11列目)のセル値にする
  9. Withステートメント終了
  10. 繰り返し処理をする
  11. 画面のちらつき防止再開
  12. マクロ終了
波乗りアヒル
波乗りアヒル

これで完成で、今のところこのVBAコードでトラブルなく使っています。

2行目と11行目の『画面ちらつき処理』も追加しています。

この追加した処理を入れておかないと、コマンドボタン押すたびに選択したセルの場所へ画面が行ったり来たりして、目障りなのとマクロ処理中に処理が速くなるのでいつも入れるようにしています。

これで入力済み最終行の同じ行の複数のセルの値を、テキストボックスに表示できるようになったので、ついでに「ひとつ上の行」や「ひとつ下の行」もボタンひとつで同じように表示できるようになったら、便利ですよね。

スポンサーリンク

【戻る】【次へ】ボタンを作る

商品修正フォーム

波乗りアヒル
波乗りアヒル

一般的には【戻る】【次へ】という名称かな?

新規データを登録すると1行下へ追記されるので、1行上のデータを表示させるには、ひとつ前に入力したデータということになりますよね。

そこで画像では【ひとつ前】ボタンとして、サンプルを作りました。

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
ActiveCell.Offset(-1, 0).Select
UserForm2.TextBox1.Text = ActiveCell.Value
UserForm2.TextBox2.Text = ActiveCell.Offset(0, 1).Value
For i = 3 To 8
With UserForm2.Controls(“TextBox” & i)
.Text = ActiveCell.Offset(0, i + 3).Value
End With
Next i
Application.ScreenUpdating = True
End Sub
  1. コマンドボタン1を押したら
  2. 画面のちらつきを停止する
  3. 選択されているセルのマイナス1行下(1行上という事)のセルを選択する
  4. ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
  5. ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
  6. 繰り返し処理「変数i」を宣言して3~8まで行う
  7. ユーザーフォーム1に配置したテキストボックス「i」(3~8という意味)
  8. 表示は選択されたセルの「i」+3の値(右に6列目~11列目)のセル値にする
  9. Withステートメント終了
  10. 繰り返し処理をする
  11. 画面のちらつき防止再開
  12. マクロ終了

入力済み最終行は既に選択されている状態なので、「1行上」と指定するだけで1行上の行にある複数のセルの値を表示できるようになるのです。

波乗りアヒル
波乗りアヒル

3行目が違うだけでその他11行は全て一緒なので簡単ですね。

では次に、【ひとつ後】ボタンを設定しましょう。

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
If ActiveCell = “” Then
MsgBox “データがありません”
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
UserForm2.TextBox1.Text = ActiveCell.Value
UserForm2.TextBox2.Text = ActiveCell.Offset(0, 1).Value
For i = 3 To 8
With UserForm2.Controls(“TextBox” & i)
.Text = ActiveCell.Offset(0, i + 3).Value
End With
Next i
End If
Application.ScreenUpdating = True
End Sub

3行目~6行目には選択したセルが空欄だった時に、メッセージを出してこれ以上下の行へ行かず、元に戻るようにしています。

7行目はさきほどと違い1行下のセルを選択するので(-1,0)が(1,0)になっています。

8行目以降は全く一緒です。

波乗りアヒル
波乗りアヒル

更に、テキストボックスに表示されたデータを修正して、書き換えたデータをセルに戻すこともできると便利ですよね。

このまま書き続けると、住所録でのデータ修正と同じことを書くことになるので詳細は省略します。

波乗りアヒル
波乗りアヒル

ご覧いただきありがとうございます。

ブログランキングに登録しています。

少しでもお役に立てれば幸いで、参考になったようでしたら応援よろしくお願いします。


Microsoft Officeランキング

ユーザーフォームを使えばこんなに便利に早く作業が終わりますよ
波乗りアヒル
波乗りアヒル

エクセルの勉強お疲れ様です。ちょっとひと休みしませんか?

タイトルとURLをコピーしました