VBA記事4.二重ループと横列の最終行取得

 

★６．二重ループ処理 と横列の最終列取得

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

Sub sample3()

Dim i As Variant

Dim j As Variant

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

For j = 2 To Cells(1, Columns.Count).End(xlToLeft).Column

Cells(i, j) = Cells(i, 1) * Cells(1, j)

Next

Next

End Sub

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

Sub sample3()

Dim i As Variant

Dim j As Variant

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

For j = 2 To Cells(1, Columns.Count).End(xlToLeft).Column

Cells(i, j) = Cells(i, ”A”) * Cells(1, j)

Next

Next

End Sub

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

上記のようなコードになります。

また列の構成のしかたも真似たほうが読みやすくなりますので、コードを書いてる最中にタブキーを入力して上記の画像のような形になるようにしてみてください。

これにより、対応するForとNextのセットなどがわかりやすくなります。基本的には同じ横列の位置にあるものがセットになっていると考えてもらってOKです。

また、2重ループ処理は最初のうちは混乱するかもしれませんので、ステップインして動きを1コマずつ見て、変数（ｉとｊの中身）を都度確認しながら、しっかりと理解をしてください。

Cells(1, Columns.Count).End(xlToLeft).Columnが最終横列を取得するコードになります。