こんばんは、こんにちは、おはようございます。( ノﾟДﾟ)おやすみなさい！ とっちーです。
本日は、VBAのコードを作成する上でなくてはならないCells！
いったいその秘めた力とはどんなものなのか！ワクワクしながらみてね。
Contents
Cellsそれは、Rangeの立役者
CellsにはCellsで大きな役割が、あります。
仕事を速やかに正しく行うために必要なんです。
Rangeの代わりにもなるCellは、ブックやシートの指示通りに的確に動いてくれる優秀な存在です。
そんなCellsですが、VBAで記述するときには、行と列の順番を忘れないでください。
Cells(行,列)=Range(“列行”)
Cells(１,１)=Range(“Ａ１“)のように行列の記述が違います。
VBAで検索や書き換え消去するなど重要な処理の場合には、特に細心の確認をお願いいたします。
実データ上で試作段階のプロシージャを実行したら、暴走してしまいおまけに消えちゃったら、もう戻りません。
手塩にかけた時間が、一瞬でヽ(ﾟ∀ﾟ)ﾉ ﾊﾟｯ☆って消えてしまいます。
CellsとRangeのコードを見比べる
”さがしもの”という文字列を検索するコードになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
b t() Dim WH As Worksheet Dim WR As Range Dim KK As String Dim KS As String Set WH = ActiveSheet KK = "さがしもの" Set WR = WH.Range("A:XFD").Find(what:=KK, LookAt:=xlWhole, SearchDirection:=xlNext) If WR Is Nothing Then MsgBox "さがしものがないため終了します" Else KS = Replace(WR.Address, "$", "") If KK = Range(KS) Then MsgBox "探し物のセル番地は " & KS Set WH = Nothing Exit Sub End If End If Set WH = Nothing End Sub Sub m() Dim WH As Worksheet Dim WR As Range Dim KK As String Dim KS As String Dim r As Long Dim c As Long Set WH = ActiveSheet KK = "さがしもの" Set WR = WH.Cells.Find(what:=KK, LookAt:=xlWhole, SearchDirection:=xlNext) If WR Is Nothing Then MsgBox "さがしものがないため終了します" Else r = WR.Row c = WR.Column If KK = Cells(r, c) Then MsgBox "探し物の列は " & c & " 列目、" & "行は " & r & " 行目です。" Set WH = Nothing Exit Sub End If End If Set WH = Nothing End Sub |
大きな違いは、
Set WR = WH.Range(“A:XFD”).Find(what:=KK, LookAt:=xlWhole, SearchDirection:=xlNext)
か
Set WR =WH.Cells.Find(what:=KK, LookAt:=xlWhole, SearchDirection:=xlNext)
になります。
後は、位置確認をするためのコードが、 Rangeの場合.Addressで取得、Cellsの場合は.Rowと.columnで取得する部分になります。
もう一つ、このコードの場合のRangeは、セル番地を記述しましょう。
Cellsは、Cellsで大丈夫です。
Cellsの役目です、お任せあれ。
数値と共に ~Episode1~
Cells(1,1)のように数値でセルの参照を行えることができるCellsですが、これはCellsにしか出来ない重要なお話なんです。
与えられし力
Cells(a,b)の”a”や”b”のように変数を活用して列や行の場所「インデックス」を探しだすことが出来るのは、まさにCellsにだけ与えられた力(フォース)なのです。
Cellsのよきパートナー.Offset(引数)と一緒に行動すれば、その力は幾重にもなることでしょう。
これが力の証
今回、お伝えするコードは、コピー元のシートのA2セルからA14セルのデータとB2セルからB14セルのデータをコピー先であるシートの設定した位置にコピーをする単純なストーリーです。（シートの名前とコピー元には、データがあることを前提にさせていただきます。）
とりあえず、まず変数宣言をしましょう。
必要な宣言のデータ型は、オブジェクト型のWorksheetが2つ、長整数型のLong（整数型のIntegerでも可）を2つ用意します。
Dim WH1 As Worksheet
Dim WH2 As Worksheet
Dim r As Long
Dim c As Long
次に宣言したオブジェクト型 WorksheetにSet構文を使用して記憶さえましょう。
Set WH1 = Worksheets(“コピー元”)
Set WH2 = Worksheets(“コピー先”)
以下は、してもしなくてもどちらでも大丈夫ですが、やはり綺麗なところに入ってみたいものです。
WH2.Select ＜——コピー先へ出向く。
Selection.ClearContents ＜——なにもかも掃除しまくる。
WH2.Cells(1, 1).Select ＜——とりあえず先頭セル番地に待機してもらう。
WH1.Select ＜——コピー元に戻る。
そして作業開始をします。
c = 1 ＜——列の位置を記憶させましょう。
「繰り返し処理の構文」の活用
|
For 引数１=値1（※任意の値）to 値2 (Step 値3（値1の間隔設定）) 処理 Next 引数１ |
今回は、引数1に変数”ｒ”を値1に2行目からスタートなので”2”を代入し、値2には何回繰り返すかということで、14行目の”14”を代入します。
Stepと値3の設定は不要ですが一応記述（2と記述すると、2→4→6→8のように2間隔で処理をするように設定できます。今回は飛ばさず行いますので”1”を代入します。）
For r = 2 To 14 Step 1 ＜——2から14までの間、間隔は+1ずつ処理をします。。
|
「プロパティである.Offset(行,列 )」の活用 行、列共に代入できる値はプラスだけでなく、”0”やマイナス設定も可能です。 ただし、セルが存在している範囲に限ります。 |
WH2.Cells(r, c).Offset(, 1).Value = WH1.Cells(r, c).Value
↑コピー先シートWH2のインデックスである.Cells(r, c)のｃの列に対し1列プラスしたいので、.Offsetの列に”1”を代入し最後に.Valueを記述したものに、代入演算子”＝”（イコール）を使用し、コピー元のインデックス.Cells(r, c).valueの値を代入します。
WH2.Cells(r, c).Offset(, 3).Value = WH1.Cells(r, c).Offset(, 1).Value
↑コピー先シートWH2のインデックスである.Cells(r, c)のｃの列に対し2列プラスしたいので、.Offsetの列に”3”を代入し最後に.Valueを記述したものに、代入演算子”＝”（イコール）を使用し、コピー元のインデックス.Cells(r, c).Offset(, 1).valueの値を代入します。コピー元にも.Offsetを活用しています。
Next r＜——次の行へいきましょうと変数ｒをForへ引き渡しています。
Forは、Nextから受け取ると1プラスして上記の作業を決められた値2まで繰り歌詞行います。
コードになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Sub COPICOPI() Dim WH1 As Worksheet Dim WH2 As Worksheet Dim r As Long Dim c As Long Set WH1 = Worksheets("コピー元") Set WH2 = Worksheets("コピー先") WH1.Select WH2.Select Selection.ClearContents WH2.Cells(1, 1).Select WH1.Select c = 1 For r = 2 To 14 Step 1 WH2.Cells(r, c).Offset(, 1).Value = WH1.Cells(r, c).Value WH2.Cells(r, c).Offset(, 3).Value = WH1.Cells(r, c).Offset(, 1).Value Next r End Sub |
動作をするとこんな感じです。
動画をどうぞ。
おわり。
まとめ
今日は、Cellsにしか出来ない重要な仕組みのお話をしました。
無理難題をスムーズにRangeと共に活躍するCellsには、特に元気はつらつでいてもらいたいですね。
VBAでコードを作成するときにCellsさん、よろしくって思いながら作ってみると、案外うまく出来るかも。
