エクセルVBA100本ノック。6本目:セルに計算式
1 今回のお題
画像のようにA1から始まる表があります。
C列にA列×B列の計算式を入れてください。
ただし商品コードに"-"の枝番が付いている場合は計算式を入れずそのままにしてください。
例.C2にはA2×B2の計算式を入れる。C4:C5には計算式を入れない。
注:おそらく列Cではなく列Dですが、その辺は気にせずいきましょう!
2 今回のお題の意図
1 計算結果ではなく、計算式を代入すること。
2 特定の文字列が含まれているか判断する処理を考えること。
3 回答
私の最初の回答
Sub ノック6本目_1() Dim LastLine As Long Dim Ws As Worksheet: Set Ws = Worksheets("Sheet1") Dim ItemNo As String '最終行の取得 LastLine = Ws.Cells(Rows.Count, 1).End(xlUp).Row For k_ItemNo = 2 To LastLine ItemNo = Ws.Cells(k_ItemNo, 1) If InStr(ItemNo, "-") = 0 Then Ws.Cells(k_ItemNo, 4) = Ws.Cells(k_ItemNo, 2) * Ws.Cells(k_ItemNo, 3) Ws.Cells(k_ItemNo, 4).NumberFormatLocal = "\#,##0;\-#,##0" End If Next End Sub
数式を代入できてないですね(;^ω^)。
Ws.Cells(k_ItemNo, 4) = Ws.Cells(k_ItemNo, 2) * Ws.Cells(k_ItemNo, 3)、で定数を入れていることになるんですね。お題の意図が分かっていませんでした。
私は、数式を直接代入するという記述は使ってませんでした。なぜなら、セルに数式入れるとファイルが重くなります。それが嫌なので、VBAで計算した結果(定数)を入れる。こんなポリシーをもっておりました。そんなポリシーを持つようになったきっかけが、10万行のVlookupです。セルの数式に入れて実行すると固まるんですよね。しかしVBAで実行すると固まらずにすんなり終わる。この経験からセルに数式は入れないようにしようと思いました。
まえおきが長くなりましたが、結論「数式の入れ方を知らない」ということです(;^ω^)。なので、回答をカンニングしました。
最終回答
Sub ノック6本目_2() Dim LastLine As Long Dim Ws As Worksheet: Set Ws = Worksheets("Sheet1") Dim ItemNo As String '最終行の取得 LastLine = Ws.Cells(Rows.Count, 1).End(xlUp).Row For k_ItemNo = 2 To LastLine ItemNo = Ws.Cells(k_ItemNo, 1) If InStr(ItemNo, "-") = 0 Then Ws.Cells(k_ItemNo, 4).FormulaR1C1 = "=RC[-2]*RC[-1]" End If Next End Sub
Ws.Cells(k_ItemNo, 4).FormulaR1C1 = "=RC[-2]*RC[-1]"???どういうことやねん!初見は思いましたw
つまりは、Ws.Cells(k_ItemNo, 4)を基準セルとして、R(Row)、C(Column)がどの位置にあるのか、ということを表現しています。
以下の画像を見てください。
D6セルの数式に注目してください。「=B6*C6」となっていますね。
つまりこれをR1C1参照方式で表現すると、=RC[-2]*RC[-1]ということになるんですね。
いや~、勉強になりました。しかし、個人的には使う機会はないかなと辛口コメントです。方法は知っておいて損はないかなくらいですね。
4 特定の文字列が含まれているか判断する
今回は、InStr関数を使っています。変数ItemNoに商品コードを代入します。
そして以下の条件で判断します。
If InStr(ItemNo, "-") = 0 Then
これで、ItemNoの中にハイフン(-)が入っているか調べます。
では、具体的にどう判断するのかが分かるために以下の画像を見てください。
3行目のA002-01の部分
InStr関数は指定した文字(今回はハイフン(-))が文字列の何番目にあるかを返します。
つまり、商品コード「A002-01」の5文字目にハイフン(-)が存在しています。なので、処理をしません。
ハイフン(-)が無い場合はInStr関数は0を返します。その場合は処理をするということになります。
InStr関数ではなく、Like演算子で判断する方法もあります。
If ItamNo Like ”*-*” Then、となります。
今回はこの辺で(^^)/~~~