エクセルマクロVBA:セルの操作
エクセルVBAで処理をしているときに、セルに数式を設定・取得する方法です。
数式を設定する場合
Cells(1, 1).Formula = "=SUM(C1,C2)"
数式を取得する場合
Cells(1, 2) = Cells(1, 1).Formula
以下のサンプルコードで試してみてくださいね。
'****************************************
'セルに数式を設定する
'****************************************
Sub Test14()
'C列に数字を設定
Cells(1, 3).Value = 10
Cells(2, 3).Value = 20
'A列に数式を設定
Cells(1, 1).Formula = "=SUM(C1,C2)"
Cells(2, 1).Formula = "=C1*C2"
'B列に数式を取得
Cells(1, 2) = Cells(1, 1).Formula
Cells(2, 2) = Cells(2, 1).Formula
End Sub
※上記の計算式がA1形式ですが、R1C1形式の場合は、FormulaをFormulaR1C1に変更します。
数式を設定する場合
Cells(1, 1).Formula = "=SUM(C1,C2)"
数式を取得する場合
Cells(1, 2) = Cells(1, 1).Formula
以下のサンプルコードで試してみてくださいね。
'****************************************
'セルに数式を設定する
'****************************************
Sub Test14()
'C列に数字を設定
Cells(1, 3).Value = 10
Cells(2, 3).Value = 20
'A列に数式を設定
Cells(1, 1).Formula = "=SUM(C1,C2)"
Cells(2, 1).Formula = "=C1*C2"
'B列に数式を取得
Cells(1, 2) = Cells(1, 1).Formula
Cells(2, 2) = Cells(2, 1).Formula
End Sub
※上記の計算式がA1形式ですが、R1C1形式の場合は、FormulaをFormulaR1C1に変更します。
エクセルのちょっとしたテクニックを紹介します。
Rnd関数を使用することで、自分が指定した範囲からランダムに数字を抽出することができます。
AとBを整数とした場合、A〜Bの間の整数値をランダムに取得する場合、以下のような構文になります。
構文
Int((B−A+1)*Rnd+A)
次のサンプルを試してみてくださいね。
50から100までの間の数字を、1行目から20行目までにランダムに取得します。
'***************************************
'乱数を取得する
'***************************************
Sub Test16()
Dim i As Integer
Randomize
For i = 1 To 20
Cells(i, 1).Value = Int((100 - 50 + 1) * Rnd + 50)
Next i
End Sub
ちなみに、サンプル中のRandomizeステートメントは、乱数発生ルーチンの初期値を設定するステートメントです。
☆エクセルVBAのおすすめ書籍⇒Excel VBA逆引き大全600の極意
Rnd関数を使用することで、自分が指定した範囲からランダムに数字を抽出することができます。
AとBを整数とした場合、A〜Bの間の整数値をランダムに取得する場合、以下のような構文になります。
構文
Int((B−A+1)*Rnd+A)
次のサンプルを試してみてくださいね。
50から100までの間の数字を、1行目から20行目までにランダムに取得します。
'***************************************
'乱数を取得する
'***************************************
Sub Test16()
Dim i As Integer
Randomize
For i = 1 To 20
Cells(i, 1).Value = Int((100 - 50 + 1) * Rnd + 50)
Next i
End Sub
ちなみに、サンプル中のRandomizeステートメントは、乱数発生ルーチンの初期値を設定するステートメントです。
☆エクセルVBAのおすすめ書籍⇒Excel VBA逆引き大全600の極意
エクセルのちょっとしたテクニックを紹介します。
表などでデータが入力されていないセルが点在していて、その行全体を削除したいという場合、手作業では大変ですよね。VBAであらかじめコードを書いておけば、楽に処理できますよ。
'**********************************************
'アクティブセル列で空欄の場合、行全体を削除
'**********************************************
Sub Test12()
Dim Clmn As Long
Clmn = ActiveCell.Column
Application.ScreenUpdating = False
Columns(Clmn).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
上記のコードを使用した場合でも、空白のセルが残ることがあります。その場合は、残ったセルを調べてみてください。半角スペースや全角スペースが入っていることがあります。入っていたスペースを削除して再度上記のコードを実行すれば削除されます。
エクセルのちょっとしたテクニックを紹介します。
エクセルのデータで文字列の一部が半角だったりするのを全角、半角どちらかにそろえるにはどうしますか?データを受け取ったときに、全角と半角が混在して見にくいということがありますよね。VBAで一括変換しちゃいましょう。
このStrconv関数を使用すると、文字列の全角と半角、大文字と小文字、ひらがなとカタカナの変換ができます。
構文は、Strconv(文字列,引数)となります。引数については、下記のようになります。
vbUpperCase:文字列を大文字に変換します。
vbLowerCase:文字列を小文字に変換します。
vbProperCase:文字列の各単語の先頭の文字を大文字に変換します。
vbWide:文字列内の半角文字を全角文字に変換します。
vbNarrow:文字列内の全角文字を半角文字に変換します。
vbKatakana:文字列内のひらがなをカタカナに変換します。
vbHiragana:文字列内のカタカナをひらがなに変換します。
vbUnicode:文字列をUnicodeに変換します。
vbFromUnicode:文字列をUnicodeから既定のコードページに変換します。
以下のサンプルコードで試してみてくださいね。
'**********************************************
'アクティブセル列の文字を全角または半角にそろえる
'**********************************************
Sub Test10()
Dim Str As String
Worksheets.Add before:=Worksheets(1)
With Worksheets(1).Range("A1")
.Activate
.Value = "ABC123アイウ"
End With
Str = ActiveCell.Value
MsgBox "半角にします"
ActiveCell.Value = StrConv(Str, vbNarrow)
MsgBox "全角にします"
ActiveCell.Value = StrConv(Str, vbWide)
MsgBox "小文字にします"
ActiveCell.Value = StrConv(Str, vbLowerCase)
MsgBox "大文字にします"
ActiveCell.Value = StrConv(Str, vbUpperCase)
MsgBox "先頭を大文字にします"
ActiveCell.Value = StrConv(Str, vbProperCase)
MsgBox "カタカナをひらがなにします"
ActiveCell.Value = StrConv(Str, vbHiragana)
MsgBox "ひらがなをカタカナにします"
ActiveCell.Value = StrConv(Str, vbKatakana)
MsgBox "半角で小文字にします"
ActiveCell.Value = StrConv(Str, vbNarrow + vbLowerCase)
MsgBox "全角で先頭を大文字にします"
ActiveCell.Value = StrConv(Str, vbProperCase + vbWide)
End Sub
エクセルのワークシート関数を利用して変換する場合は、ASC関数:全角数字を半角数字に変換する@エクセルまたは、JIS関数:半角数字を全角数字に変換する@エクセルを参照してください。
エクセルのちょっとしたテクニックを紹介します。
選択した範囲のアドレスを取得する方法です。
'*********************************************
'選択した範囲のアドレスを取得する
'*********************************************
Sub Test7()
Dim Rng As Range
Set Rng = ActiveWindow.RangeSelection
'絶対参照
MsgBox "選択した範囲のアドレスは " & Rng.Address
'A1形式
MsgBox "選択した範囲のアドレスは " & Rng.Address(False, False)
'R1C1形式
MsgBox "選択した範囲のアドレスは " & Rng.Address(False, False, xlR1C1)
End Sub
※印刷範囲やスクロールエリアを設定するときなどに使えますよ。
選択した範囲のアドレスを取得する方法です。
'*********************************************
'選択した範囲のアドレスを取得する
'*********************************************
Sub Test7()
Dim Rng As Range
Set Rng = ActiveWindow.RangeSelection
'絶対参照
MsgBox "選択した範囲のアドレスは " & Rng.Address
'A1形式
MsgBox "選択した範囲のアドレスは " & Rng.Address(False, False)
'R1C1形式
MsgBox "選択した範囲のアドレスは " & Rng.Address(False, False, xlR1C1)
End Sub
※印刷範囲やスクロールエリアを設定するときなどに使えますよ。
エクセルのちょっとしたテクニックを紹介します。
行全体や列全体で処理させるのではなく、行の一部とか列の一部など自分が選択した範囲内だけマクロで処理したいと言った場合どうしますか?その都度、コードを書き直すのは面倒くさいし、非効率ですよね。自分が選択した範囲を一つのコレクションと考え、その中で処理をさせるようにすれば良いのです。
例として選択したセルにOKという文字を入力する場合、以下のように記述します。
'***********************************
'選択範囲内で処理をさせる
'***********************************
Sub Test6()
Dim Rng As Range
Dim rn As Range
Set Rng = ActiveWindow.RangeSelection
For Each rn In Rng
rn.Value = "OK"
Next rn
End Sub
※上記の場合、選択するセルは必ずしも隣接している必要はありません。
※似たような処理として、手作業で選択したセルに同じ内容を入力する操作は複数のセルに一括入力@エクセルを参照してください。
For〜Next構文内の処理を変えれば、メニューバーに自作マクロを登録しておいたりするときなどに、結構使えるテクニックなので、ぜひ使ってみてくださいね。
エクセルのちょっとしたテクニックを紹介します。
セルの列幅や行の高さを入力されている文字列に合わせて調節できます。
・列幅を調節する場合
Range("A1").EntireColumn.AutoFitまたは
Columns(1).AutoFit
・行の高さを調整する場合
Range("A1").EntireRow.AutoFitまたは
Rows(1).AutoFit
以下のサンプルコードで試してみてくださいね。
Sub Test2()
With Range("A1")
.Value = "あっという間にセルの列幅や行の高さが変わります。"
.RowHeight = 30
.ColumnWidth = 30
End With
MsgBox "列幅を調節"
Range("A1").EntireColumn.AutoFit
MsgBox "行の高さを調節"
Range("A1").EntireRow.AutoFit
With Range("A1")
.RowHeight = 30
.ColumnWidth = 30
End With
MsgBox "列幅を調節"
Columns(1).AutoFit
MsgBox "行の高さを調節"
Rows(1).AutoFit
End Sub
セルの列幅や行の高さを入力されている文字列に合わせて調節できます。
・列幅を調節する場合
Range("A1").EntireColumn.AutoFitまたは
Columns(1).AutoFit
・行の高さを調整する場合
Range("A1").EntireRow.AutoFitまたは
Rows(1).AutoFit
以下のサンプルコードで試してみてくださいね。
Sub Test2()
With Range("A1")
.Value = "あっという間にセルの列幅や行の高さが変わります。"
.RowHeight = 30
.ColumnWidth = 30
End With
MsgBox "列幅を調節"
Range("A1").EntireColumn.AutoFit
MsgBox "行の高さを調節"
Range("A1").EntireRow.AutoFit
With Range("A1")
.RowHeight = 30
.ColumnWidth = 30
End With
MsgBox "列幅を調節"
Columns(1).AutoFit
MsgBox "行の高さを調節"
Rows(1).AutoFit
End Sub
エクセルのちょっとしたテクニックを紹介します。
エクセルでVBAを使用してコードを書く時、よく使うのが、データの端の行や列を取得するコードです。データの端の行や列を取得することで、データ数が変わっても処理ができるようになります。
・最終行(データの一番下の行番号)を取得する場合
Cells(Rows.Count, 1).End(xlUp).Row
※A列の最終行を取得します。セルの下端から上方向に向かって一番最初にデータのあるセルの行を返します。数字の部分を変えることでA列以外の列の最終行を取得できます。
・最終列(データの一番右の列番号)を取得する場合
Cells(1, Columns.Count).End(xlToLeft).Column
※1行目の右端の列を取得します。セルの右端から左方向に向かって一番最初にデータのあるセルの列を返します。数字の部分を変えることで指定した行の右端の列を取得できます。
・最初の行(データの一番上の行番号)を取得する場合
Cells(1, 1).End(xlDown).Row
※セルA1から下方向に向かって一番最初にデータのあるセルの行を返します。数字の部分を変えることで開始するセルを変更できます。
・最初の列(データの一番左の列番号)を取得する場合
Cells(1, 1).End(xlToRight).Column
※セルA1から右方向に向かって一番最初にデータのあるセルの列を返します。数字の部分を変えることで開始するセルを変更できます。
セルにいくつかデータを入力し、以下のサンプルコードで試してみてくださいね。
Sub Test1()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
a = Cells(Rows.Count, 1).End(xlUp).Row
b = Cells(1, Columns.Count).End(xlToLeft).Column
c = Cells(1, 1).End(xlDown).Row
d = Cells(1, 1).End(xlToRight).Column
MsgBox "最終行は、" & a & vbCrLf & "最終列は、" & b _
& vbCrLf & "セルA1から下へ向かって最初の行は、" & c _
& vbCrLf & "セルA1から右へ向かって最初の列は、" & d
End Sub
※手作業で行う場合は、ショートカットキー3@エクセルまたは、データの端へ移動する@エクセルという方法もあります。
エクセルでVBAを使用してコードを書く時、よく使うのが、データの端の行や列を取得するコードです。データの端の行や列を取得することで、データ数が変わっても処理ができるようになります。
・最終行(データの一番下の行番号)を取得する場合
Cells(Rows.Count, 1).End(xlUp).Row
※A列の最終行を取得します。セルの下端から上方向に向かって一番最初にデータのあるセルの行を返します。数字の部分を変えることでA列以外の列の最終行を取得できます。
・最終列(データの一番右の列番号)を取得する場合
Cells(1, Columns.Count).End(xlToLeft).Column
※1行目の右端の列を取得します。セルの右端から左方向に向かって一番最初にデータのあるセルの列を返します。数字の部分を変えることで指定した行の右端の列を取得できます。
・最初の行(データの一番上の行番号)を取得する場合
Cells(1, 1).End(xlDown).Row
※セルA1から下方向に向かって一番最初にデータのあるセルの行を返します。数字の部分を変えることで開始するセルを変更できます。
・最初の列(データの一番左の列番号)を取得する場合
Cells(1, 1).End(xlToRight).Column
※セルA1から右方向に向かって一番最初にデータのあるセルの列を返します。数字の部分を変えることで開始するセルを変更できます。
セルにいくつかデータを入力し、以下のサンプルコードで試してみてくださいね。
Sub Test1()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
a = Cells(Rows.Count, 1).End(xlUp).Row
b = Cells(1, Columns.Count).End(xlToLeft).Column
c = Cells(1, 1).End(xlDown).Row
d = Cells(1, 1).End(xlToRight).Column
MsgBox "最終行は、" & a & vbCrLf & "最終列は、" & b _
& vbCrLf & "セルA1から下へ向かって最初の行は、" & c _
& vbCrLf & "セルA1から右へ向かって最初の列は、" & d
End Sub
※手作業で行う場合は、ショートカットキー3@エクセルまたは、データの端へ移動する@エクセルという方法もあります。