VBA100本ノック 6本目:セルに計算式
セルに計算式を入れるVBAです。
どのような形式の計算式をどのプロパティに入れるか。
ツイート文字数制限があるので、ブック・シート指定等省略可能な記述は省略しています。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。
出題
出題ツイートへのリンク
#VBA100本ノック 6本目
画像のようにA1から始まる表があります。
D列にB列×C列の計算式を入れてください。
ただし商品コードに"-"の枝番が付いている場合は計算式を入れずそのままにしてください。
例.D2にはA2×B2の計算式を入れる。D4:D5には計算式を入れない。
サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_06.xlsm
https://excel-ubara.com/vba100sample/VBA100_06.zip
画像のようにA1から始まる表があります。
D列にB列×C列の計算式を入れてください。
ただし商品コードに"-"の枝番が付いている場合は計算式を入れずそのままにしてください。
例.D2にはA2×B2の計算式を入れる。D4:D5には計算式を入れない。
https://excel-ubara.com/vba100sample/VBA100_06.xlsm
https://excel-ubara.com/vba100sample/VBA100_06.zip
VBA作成タイム
この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。
他の人の回答および解説を見て、書いたVBAを見直してみましょう。
頂いた回答
解説
シート指定は省略します。
最下行が非表示ではないことが前提になります。
Like演算子はInstr関数を使っても良いです。
If InStr(Cells(i, 1), "-") = 0 Then
Like演算子、Instr関数、R1C1参照については記事の補足にて。
また、Unionメソッドを使って一括で相対参照で入れるコードも掲載しておきました。
Dim i As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Not Cells(i, 1).Value Like "*-*" Then
Cells(i, 4).FormulaR1C1 = "=RC[-2]*RC[-1]"
End If
Next
最下行が非表示ではないことが前提になります。
If InStr(Cells(i, 1), "-") = 0 Then
Like演算子、Instr関数、R1C1参照については記事の補足にて。
また、Unionメソッドを使って一括で相対参照で入れるコードも掲載しておきました。
補足
この問題の場合、R1C1形式を使わないとかなり面倒な処理になります。
範囲全てに同一数式を入れてしまって良ければ、相対参照で一括で数式を入れられます。
これは相対参照になっているので、実際に入るセルによって参照するセルが勝手にずれてくれます。
しかし、個別の行に数式をいれるとなると、その行からみた参照で数式を入れなければなりません。
このような場合は、R1C1で数式を作成すると、どの行にも同一の数式を入れることができます。
R1C1参照の数式は、FormulaやValueにも入れることができますが、可読性の観点からもFormulaR1C1プロパティを使った方が良いと思います。
また、Unionメソッドを使って対象セルをRangeオブジェクトに入れればA1形式の相対参照でも一括で入れられます。
これはシートで飛び飛びのセルを選択して、数式を一括入力している操作になります。
範囲全てに同一数式を入れてしまって良ければ、相対参照で一括で数式を入れられます。
Dim rng As Range
Set rng = Range("A1").CurrentRegion
Set rng = Intersect(rng, rng.Offset(1, 3))
rng = "=B2*C2"
これは相対参照になっているので、実際に入るセルによって参照するセルが勝手にずれてくれます。
しかし、個別の行に数式をいれるとなると、その行からみた参照で数式を入れなければなりません。
このような場合は、R1C1で数式を作成すると、どの行にも同一の数式を入れることができます。
R1C1参照の数式は、FormulaやValueにも入れることができますが、可読性の観点からもFormulaR1C1プロパティを使った方が良いと思います。
Dim i As Long, rng As Range
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If InStr(Cells(i, 1).Value, "-") = 0 Then
If rng Is Nothing Then
Set rng = Cells(i, 4)
Else
Set rng = Union(rng, Cells(i, 4))
End If
End If
Next
If Not rng Is Nothing Then
rng.Formula = Replace("=B2*C2", 2, rng.Row)
End If
これはシートで飛び飛びのセルを選択して、数式を一括入力している操作になります。
Like演算子
文字列比較のための便利なパターン文字列式を、
ワイルドカード、文字リスト、文字範囲などを組み合わせて指定できます。
パターン文字列式(ワイルドカード、文字リスト、文字範囲)
ワイルドカード、文字リスト、文字範囲などを組み合わせて指定できます。
文字パターン | 引数stringの中の一致する文字 |
? | 任意の1文字 |
* | 任意の数の文字 |
# | 任意の1文字の数字(0-9) 1バイト(半角)の数字 半角全角どちらのの数字にも一致します |
[charlist] | 文字リストcharlistに指定した文字の中の任意の1文字 |
[!charlist] | 文字リストcharlistに指定した文字以外の任意の1文字 |
1文字とは
2バイト文字(全角文字)も1文字と数えて文字列比較を行います。
特殊文字の指定
特殊文字の左角かっこ([)、疑問符(?)、数値記号(#)、およびアスタリスク(*)を文字列比較するには、
これらの文字を角かっこで囲み、[*]のように指定します。
これらの文字を角かっこで囲み、[*]のように指定します。
文字リスト
ハイフン (-) を使用して範囲の上限と下限を分離することで、charlistで文字の範囲を指定できます。
たとえば、
[A-Z]では、string内の対応する文字位置にAからZの範囲内の大文字が含まれる場合に一致します。
たとえば、
[A-Z]では、string内の対応する文字位置にAからZの範囲内の大文字が含まれる場合に一致します。
Instr関数
文字列の中から指定した文字列を検索し、最初に見つかった文字位置を返す文字列処理関数です。
InStr([start, ]string1, string2[,
compare])
start | 省略可能です。 検索の開始位置を表す数式を指定します。 省略すると、先頭の文字から検索されます。引数 start に Null 値が含まれている場合、エラーが発生します。 引数 compare を指定した場合は、start も指定する必要があります。 |
string1 | 必ず指定します。 検索対象となる文字列式を指定します。 |
string2 | 必ず指定します。 引数 string1 内で検索する文字列式を指定します。 |
compare | 省略可能です。文字列比較の比較モードを指定する番号を設定します。 引数 compare が Null 値の場合は、エラーが発生します。 引数 compare を指定した場合は、引数 start も指定する必要があります。 引数 compare を省略すると、Option Compare ステートメントの設定に応じて、比較モードが決まります。 ローカル固有の比較ルールを使用するには、有効なLCID (LocaleID) を指定します。 |
R1C1参照
セルの相対位置、つまり、計算式を設定しようとしているセルからの移動量を、
行位置をRに続けて記述し、列位置をCに続けて記述します。
これらを組み合わせて、計算式を設定するセルからの位置を指定します。
例.C5セルに計算式を設定する場合、
行位置をRに続けて記述し、列位置をCに続けて記述します。
R ・・・ Rの後に何も記述しない場合は同一行
C ・・・ Cの後に何も記述しない場合は同一列
R1 ・・・ Rの後に数値のみ記述した場合はその絶対行数
C1 ・・・ Cの後に数値のみ記述した場合はその絶対列数
R[1]・・・ Rに続けて[数値]とした場合は、数値行数分、行位置をずらした行
C[1]・・・ Cに続けて[数値]とした場合は、数値列数分、列位置をずらした列
これらを組み合わせて、計算式を設定するセルからの位置を指定します。
例.C5セルに計算式を設定する場合、
RC1 ・・・ A5セル
R1C ・・・ C1セル
R[1]C[2] ・・・ E6セル
RC[-1] ・・・ B5セル
サイト内関連ページ
第10回.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
・Cells(行番号, 列番号) ・Rows(行番号) ・Columns(列番号) ・RangeとCellsの関連記事
第11回.RangeとCellsの使い方|VBA入門・RangeとCellsの基本的な使い分け方 ・固定セル(固定位置)の指定 ・Rangeに変数は使わないようにします ・1つの(VBAで位置を変化させる)セルを指定する場合 ・セル範囲(複数セル)を指定する場合 ・複数行全体、複数列全体の指定 ・RangeとCellsの使い分け方のまとめ ・RangeとCellsの基本の関連記事 ・RangeとCellsの応用の関連記事
第38回.セルに計算式を設定(Formula)|VBA入門・計算式を設定できるプロパティ ・Valueプロパティ ・Formulaプロパティ , FormulaLocalプロパティ ・FormulaR1C1プロパティ , FormulaR1C1Localプロパティ ・R1C1参照形式 ・Localが付くプロパティについて ・それぞれの違い(Localは除く) ・何故、こんなに多くのプロパティが存在しているのか ・R1C1形式を使うメリット ・たった1行のVBAで複数のセルに計算式を入れる
第49回.Like演算子とワイルドカード・Like演算子 ・パターン文字列式(ワイルドカード、文字リスト、文字範囲) ・Like演算子の使用例 ・正規表現について
第47回.VBA関数(文字列操作,Replace,InStr,StrConv)・文字列操作に関するVBA関数の一覧 ・Replace関数 ・InStr関数 ・StrConv関数 ・最後に
同じテーマ「VBA100本ノック」の記事
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
4本目:セルの消去・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
5本目:セルの計算・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
6本目:セルに計算式7本目:日付データの扱い
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
8本目:点数の合否判定・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
9本目:フィルターコピー・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
10本目:行の削除・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
11本目:セル結合の警告・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
12本目:セル結合を解除・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
13本目:文字列の部分フォント・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
新着記事NEW ・・・新着記事一覧を見る
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.ひらがな⇔カタカナの変換|エクセル基本操作
5.繰り返し処理(For Next)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.Findメソッド(Find,FindNext,FindPrevious)|VBA入門
- ホーム
- マクロVBA入門編
- VBA100本ノック
- 6本目:セルに計算式
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。