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

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

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

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

Contents

1 Cellsそれは、Rangeの立役者
2 CellsとRangeのコードを見比べる
3 Cellsの役目です、お任せあれ。
- 3.1 数値と共に ~Episode1~
  - 3.1.1 与えられし力
  - 3.1.2 これが力の証
4 まとめ

Cellsそれは、Rangeの立役者

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

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

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

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

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

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

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

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

手塩にかけた時間が、一瞬でヽ(ﾟ∀ﾟ)ﾉﾊﾟｯ☆って消えてしまいます。

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

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

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

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

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

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まで繰り歌詞行います。

コードになります。

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