「Excel」VBAで検索を簡単にする!それは、Cells!

 

こんばんは、こんにちは、おはようございます。( ノ゚Д゚)おやすみなさい! とっちーです。

本日は、VBAのコードを作成する上でなくてはならないCells!

いったいその秘めた力とはどんなものなのか!ワクワクしながらみてね。

 

 

Cellsそれは、Rangeの立役者

 

CellsにはCellsで大きな役割が、あります。

 

仕事を速やかに正しく行うために必要なんです。

 

Rangeの代わりにもなるCellは、ブックやシートの指示通りに的確に動いてくれる優秀な存在です。

そんなCellsですが、VBAで記述するときには、行と列の順番を忘れないでください。

 

Cells(行,列)=Range(“列行”)

Cells(,)=Range(““)のように行列の記述が違います。

 

VBAで検索や書き換え消去するなど重要な処理の場合には、特に細心の確認をお願いいたします。

 

実データ上で試作段階のプロシージャを実行したら、暴走してしまいおまけに消えちゃったら、もう戻りません。

 

手塩にかけた時間が、一瞬でヽ(゚∀゚)ノ パッ☆って消えてしまいます。

 

CellsとRangeのコードを見比べる

 

”さがしもの”という文字列を検索するコードになります。

 

 

大きな違いは、

 

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=値1(※任意の値)to 値2 (Step  値3(値1の間隔設定))

 処理 

Next 引数1

 

今回は、引数1に変数”r”を値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)の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)のcの列に対し2列プラスしたいので、.Offsetのに”3”を代入し最後に.Valueを記述したものに、代入演算子”=”(イコール)を使用し、コピー元のインデックス.Cells(r, c).Offset(, 1).valueの値を代入します。コピー元にも.Offsetを活用しています。

 

Next r<——次の行へいきましょうと変数rをForへ引き渡しています。

Forは、Nextから受け取ると1プラスして上記の作業を決められた値2まで繰り歌詞行います。

 

コードになります。

 

 

動作をするとこんな感じです。

動画をどうぞ。

 

 

 

おわり。

 

まとめ

 

今日は、Cellsにしか出来ない重要な仕組みのお話をしました。

 

無理難題をスムーズにRangeと共に活躍するCellsには、特に元気はつらつでいてもらいたいですね。

 

VBAでコードを作成するときにCellsさん、よろしくって思いながら作ってみると、案外うまく出来るかも。