VBAで関数を使う方法

前へ 次へ
このQ&Aのポイント
  • VBAで関数を使って文字列の集計を行いたい場合、COUNTIFS関数を使用します。
  • COUNTIFS関数を使う際には、検索対象の文字列をダブルクォーテーションで囲む必要があります。
  • さらに、COUNTIFS関数に条件を追加する場合は、条件の文字列もダブルクォーテーションで囲みます。
回答を見る
  • ベストアンサー

関数をVBAで使用する方法

こんばんは、VBAについて質問させてください、初心者のため、初歩的な質問で申し訳ありません。 '検索対象は「りんご」や「みかん」などの文字列 Dim 検索対象の文字 As String '３行目から最終行＋１行を処理対象にする For 行 = 3 To Cells(Rows.Count, 1).End(xlUp).Row + 1 '３列目の文字が下の行と違う場合（１行下から別の文字列のデータが始まる場合）３行上の１９列目に３列目と同じ文字を入力し、数を集計 If Cells(行, 3) <> Cells(行 + 1, 3) Then Cells(行 - 3, 19) = Cells(行, 3) 検索対象の文字 = Cells(行, 3).Value Cells(行 - 2, 19) = "=CountIfs(C:C," & 検索対象の文字 & ")" End If Next 行 上記の処理を行っても関数の中には"=CountIfs(C:C,みかん)としか表示されず、 数が集計できません。文字列なので、「みかん」の前後に「"」が必要なのですが、 どのようにすれば「"」を追記できるでしょうか…？！また、もう一つCOUNTIFSの条件がありまして、「M列が0でないならば」（<>0）という条件を加えたいのですが、これも追記しようと "=CountIfs(C:C," & 検索対象の文字 & "M:M,<>0)"にしますと「アプリケーション定義またはオブジェクト定義のエラーです。」と表示されます。 ネットにある関数と変数の組み合わせ方は色々読んで色々と試してみたのですが、中々うまくいきません。 どなたかご助力いただけると大変嬉しいです…！

質問者が選んだベストアンサー

  • ベストアンサー
  • f272
  • ベストアンサー率46% (8016/17133)
回答No.1

> どのようにすれば「"」を追記できるでしょうか…？ 文字列の中で「"」を入力するには「""」のように2個並べて書きます。そう進ことで「"」を入力したことになります。 > "=CountIfs(C:C," & 検索対象の文字 & "M:M,<>0)"にしますと「アプリケーション定義またはオブジェクト定義のエラーです。」と表示されます 「,」が足りません。 最終的には "=CountIfs(C:C,""" & 検索対象の文字 & """,M:M,<>0)" としてください。

levitooicompass
質問者

お礼

なるほど！&と変数を囲う「"」と、文字列を囲う「"」というわけですね！ご回答いただいて、ありがとうございました(^_^)

その他の回答 (3)

  • kkkkkm
  • ベストアンサー率65% (1619/2458)
回答No.4

検索対象の文字 は変数でしたね、なので Cells(行 - 2, 19).Formula = "=CountIfs(C:C,""" & 検索対象の文字 & """,M:M,""<>0"")"

  • masnoske
  • ベストアンサー率35% (67/190)
回答No.3

VBAコードの中でワークシート関数を使うのであれば，以下のようになります． Cells(行 - 2, 19) = WorksheetFunction.CountIfs(Range("C:C"),検索対象の文字) > 文字列なので、「みかん」の前後に「"」が必要なのですが、 勘違いされているようです． VBA のコードの中で文字列を定数として扱う場合に「"」が必要になります． str = "みかん" の場合は，str に文字定数 みかん が代入されますが， str = みかん の場合は，str に 文字変数みかん の内容が代入されます． みかん = "くだもの" str = みかん であれば，strの内容は くだもの になります． 老婆心ながら，今後もVBAを使われるのであれば，変数名や定数名などには半角英数字を使われることをオススメします．

levitooicompass
質問者

お礼

なるほど、文字列だから「"」が必要なのではなく、文字列を定数として扱うから「"」が必要という考え方なのですね！分かりやすいのでついつい食べ物の名前使ってしまうのですが、今後はアルファベットでの表記を心がけます…！

  • kkkkkm
  • ベストアンサー率65% (1619/2458)
回答No.2

文字列の中の"は""になります。.Formulaも付けると数式だと意識できます。 Cells(行 - 2, 19).Formula = "=CountIfs(C:C,"" & 検索対象の文字 &"",M:M,""<>0"")" 迷ったときはマクロの記録でコードを取得してみるといいかもしれません。 FormulaR1C1になってセルの指定がC[1]とかになりますが、R1C1を外して通常のセル指定に変更したらいいです。

levitooicompass
質問者

お礼

Formula形式でも変数は使えるのですね！今別のVBAでFormula形式を使おうとしているので、参考にさせていただきます、ありがとうございます！m(_ _)m

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • VLOOKUP関数と同じことをVBAでおこなうには

    　初めまして、当方ＶＢＡの素人です。よろしくお願いします。 　同じような質問で、このようなVBAを見つけました。 Sub Macro1() For n = 2 To 5 '処理するSheet2の行数範囲 a = Sheets("Sheet2").Cells(n, 1) 'aにA列の値を代入 For m = 2 To 5 '検索するSheet1の行数範囲 If Sheets("Sheet1").Cells(m, 1) = a Then 'Sheet2のA列の値とSheet1のA列が一致した場合 v = Sheets("Sheet1").Cells(m, 2) 'vにB列の値を代入 Sheets("Sheet2").Cells(n, 2).Value = v 'Sheet2のB列に値を入力 Exit For '値が見つかったのでForを終了 End If Next Next End Sub このVBAではSheet2での検索、入力が列になるのですが、列でなく、行でできないでしょうか。できればSheet1のB列の値をSheet２の１行で検索、Sheet２の２行に入力されるだけではなく、Sheet1のＣ列の値をSheet３の１行で検索、Sheet３の２行に入力されるようにしたいと思います。 　解る方、よろしくお願いします。

  • VBA 処理の途中でエラーが出る

    VBA初心者です。 シート1とシート2があり、 シート1の表の列Mと列Nにそれぞれシート2のデータを参照する COUNTIFS関数を入れたいと思い、以下のように作成しました。 ※シート1は場合よって行の最後行が違います。 Dim Test As String Dim Test2 As String Dim i As Long For i = 1 To Cells(Rows.Count,1).End(xlUP).Row Test=Range("A" & i ) If MyStr<>""Then Range("M" & i) ="=COUNTIFS(Sheet2!,A:A"& Test &",Sheet2!B:B,M1"&")" End If Test2=Range("A" & i ) If MyStr<>""Then Range("N" & i )="=COUNTIFS(Sheet2!,A:A"& Test2 &",Sheet2!B:B,N1"&")" End If Next i この内容にて、処理が成功していたのですが、 急に、行150のあたりで処理が止まり、実行時エラー（アプリケーション定義またはオブジェクト定義のエラーです） が出るようになりました。 行150より上の方は、列Mと列N共にCOUNTIFS関数が入っています。 この場合、どのようにすればよろしいのでしょうか？ エラーの対処、または別の記述方法があれば、ご教授頂きたく存じます。

  • 下記のマクロはC列5行目から文字の

    下記のマクロはC列5行目から文字の入っている最後の行までの範囲で セル内に蜜柑や林檎、苺の文字が入っていたら同一行のA列にも蜜、林、苺 の文字を入れるというマクロなのですが・・・ たとえばC列12行目が 『蜜柑林檎苺』 となっていた場合、A列に入る言葉は『苺』となり『蜜』『林』という言葉が 消えてしまいます。 そこでこのマクロを少し改造して、 C列が『蜜柑林檎苺』や『蜜柑苺』となっている場合 A列に入る言葉は『蜜林苺』ないし『蜜苺』という風に積み重ねていくように改造はできないでしょうか？ ↓この部分を改造すればできるようになりますか？ Cells(i, 2).Offset(0, -1).Value = "蜜" Sub 蜜柑林檎苺() Dim i As Long With ActiveSheet For i = 5 To .Cells(Rows.count, "C").End(xlUp).Row If InStr(.Cells(i, "C"), "蜜柑") > 0 Then MsgBox i & "行目アウト！" Cells(i, 2).Offset(0, -1).Value = "蜜" End If If InStr(.Cells(i, "C"), "林檎") > 0 Then MsgBox i & "行目アウト！" Cells(i, 2).Offset(0, -1).Value = "林" End If If InStr(.Cells(i, "C"), "苺") > 0 Then MsgBox i & "行目アウト！" Cells(i, 2).Offset(0, -1).Value = "苺" End If Next i End With End Sub

  • エクセルVBAで、文字列の検索方法について

    先日、こちらで教えていただいたVBAがあります。 E列のセルの文字列の末尾が「計」のものを検索し、その行に色をつけるものです。 Sub iroiro() Dim x, y x = 1 Do If Right(Cells(x, 5), 1) = "計" Then For i = 2 To 5 Cells(x, i).Interior.ColorIndex = 3 Next End If x = x + 1 Loop Until Right(Cells(x, 5), 1) = "" End Sub これはばっちりで、助かっているのですが、今度は末尾ではなく、文字列中に「営業」という文字があるのを検索し、色をつけたいのです。 If Right(Cells(x, 5), 1) = "計" Thenを どう変えればいいのでしょうか？

  • VBAで教えてください

    こんにちは、VBAを作成したのですが、意図する動作にならず、ご教授お願いします。やりたいことは、2行目を検索して、"AB"を含む文字があったら、その行を削除するというものです。ですが、下記コードでは "AB"を含む文字がB列、C列と続けてあった場合、Ｃ列は削除してくれません。 思うに、最初のB列を削除したときに、左に列がSHIFTしてしまい検索にかからないのでは？と思っています。最初のB列を削除した後に、Columnの値を-1してあげればいいのかな？ と思うのですが、うまくコードが書けませんでした。どうぞよろしくお願いします Sub ab_sakujyo() Dim i As Integer For i = 1 To 50 If Cells(2, i).Value Like "*AB*" Then Columns(i).Delete End If Next End Sub

  • エクセルVBAについての質問です。

    エクセルVBAについての質問です。 A列のCという商品名が入った列を削除したい場合下記のようにすれば可能かと思いますが、C列のCという商品名が入った列を削除したい場合どのようにすればよいか教えて下さい。 VBAに関してまだ初心者ですがどうぞよろしくお願いします。 行 = 1 Do 行 = 行 + 1 If Cells(行, 1) = "" Then Exit Do End If '行の値がC以外の時は次の行に移る Do If Cells(行, 1) = "C" Then Rows(行 & ":" & 行).Select Selection.Delete Shift:=xlUp Else Exit Do 'ジャンプ先は内側のDo～Loopのすぐ下 End If Loop 'ジャンプ先はここ If Cells(行, 1) = "" Then Exit Do End If Loop End Sub

  • vbaリストと同じ値があったら、隣のセルに値を反映

    エクセル　マクロ(vba)の質問です。 a列に検索リストがあり、b列に検索対象があります。 このとき、a列の検索リストと合致した文字のみをb列から取り出し、c列に反映させたいです。 以下のコードを走らせましたが、何も起こらず、どこを修正すればいいのか困っています。 お手数ですが、ご教示いただけますと幸いですm(_ _)m dim i as long for i = 1 to cells(rows.count,1).end(xlup).row if Instr(cells(i,2),cells(i,1)) then cells(i,3) = cells(i,1) i = i + 1 end if next

  • VBAで教えてください。

    以前ここで教えていただいたＶＢＡで http://jisaku.155cm.com/src/1371930716_9b9006528605642980beed48a8998013b0731e4b.jpg のようにＡ列のテスト４をクリックしたときにＣ列のテスト４が一発で解るようにしたいです。 もちろん、テスト１１をクリックしたときは、テスト４塗りつぶしは解除され、 テスト１１が塗りつぶされるようにしたいです。 写真は塗りつぶししていますが、解るようにしたいだけなので、塗りつぶしにはこだわっていません。 あと、Ｅ、Ｆ、Ｇ列は解りやすく並べているだけで、実際はＡ、Ｂ、Ｃ列だけです。 それと、Ｃ列は関数を使って表示してあります。 という質問で Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から Dim i As Long Range("C:C").Interior.ColorIndex = xlNone If Application.Intersect(Target, Range("A:A")) Is Nothing Or Target.Count <> 1 Then Exit Sub On Error Resume Next Application.ScreenUpdating = False ActiveSheet.Cells.interio.ColorIndex = xlNone For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Cells(i, "C") = Target Then Cells(i, "C").Interior.ColorIndex = 3 End If Next i Application.ScreenUpdating = True End Sub 'この行まで をシートのコードに張り付ければいいですよ。と教えてくれたものがあるのですが、 Ａ列でクリックした文字をＣ列からすべて見つけて反転してくれないようです。何個か反転してくれない ものが出てきてしまいました。 Ｃ列が何百行とかなってしまうと、すべての同じ文字を検索してくれないのでしょうか？ ちなみに列がここに掲載しているものと違うので Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から Dim i As Long Range("R:R").Interior.ColorIndex = xlNone If Application.Intersect(Target, Range("B:B")) Is Nothing Or Target.Count <> 1 Then Exit Sub On Error Resume Next Application.ScreenUpdating = False ActiveSheet.Cells.interio.ColorIndex = xlNone For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Cells(i, "R") = Target Then Cells(i, "R").Interior.ColorIndex = 3 End If Next i Application.ScreenUpdating = True End Sub 'この行まで のＣをＲにＡをＢに変更して使ってます。 これがいけないのかな？ よろしくお願いします。

  • VBA教えて下さい

    ＶＢＡ初心者です やりたいこと 変数を宣言し 今開いているシート(activesheet)に for nextを使用し 列5～20を調べ 行3～5を指定し もし、5～20列の2行目のどれかに”No.８”という文字があれば(ここまでのコードは書けました) その当てはまる列の3～5行を選択し(1) 更に、(1)の下2列を選択する(2) そして、(1)と(2)を結合させる といったコードが書きたいです 考えたコード Sub test() Dim i As Long Dim j As Long With ActiveSheet For j = 5 To 20 For i = 3 To 5 If .Cells(j, 2) Like "*No.８*" Then 'ここからがわかりません End If Next i Next j End With End Sub 変な書き方なので例えを書きます Ａ1セルにNo.８の文字があれば Ｅ1セルとＦ1セルを選択し 更に、Ｅ3セルとＦ3セルも選択し Ｅ1セルとＦ3まとめてセルを結合といったことがしたいです。 質問頂ければ追記しますｍ(ーー)ｍ おそらくoffsetを使用して選択すると思うのですが上手く出来ませんでした 回答お願い致します

  • vbaプログラミングについて教えてください。

    vba初心者です。下記のようにプログラミングしましたがもっといいプログラムの仕方はないでしょうか。ちょっとごちゃごちゃしていて見にくいです。どなかたお力をお貸しください。 Private Sub データUPDATE輸入_Click() ActiveSheet.Unprotect Dim Line As String Dim Maxrow As String Sheets("Invoice").Select Line = 5 　 Do Until Cells(Line, 7).Value = "" On Error Resume Next 'A列の空欄をコピーして埋める If Cells(5, 1).Value = "" Then Cells(Line, 1).Value = "" ElseIf Cells(Line, 1).Value = "" Then Cells(Line, 1).Value = Cells(Line - 1, 1).Value End If 'B列の空欄をコピーして埋める If Cells(5, 2).Value = "" Then Cells(Line, 2).Value = "" ElseIf Cells(Line, 2).Value = "" Then Cells(Line, 2).Value = Cells(Line - 1, 2).Value End If 'C列の空欄をコピーして埋める If Cells(5, 3).Value = "" Then Cells(Line, 3).Value = "" ElseIf Cells(Line, 3).Value = "" Then Cells(Line, 3).Value = Cells(Line - 1, 3).Value End If 'D列の空欄をコピーして埋める If Cells(5, 4).Value = "" Then Cells(Line, 4).Value = "" ElseIf Cells(Line, 4).Value = "" Then Cells(Line, 4).Value = Cells(Line - 1, 4).Value End If 'E列の文字を「輸入シート」から検索しF列に貼り付ける If Cells(Line, 5).Value = "" Then Cells(Line, 5).Value = Cells(Line - 1, 5).Value End If Cells(Line, 6).Value = Application.WorksheetFunction.VLookup(Cells(Line, 5).Value, Worksheets("輸入Parts").Range("A2:R20000"), 2, 0) 'E列を検索しデータが存在しない場合はF列に「データがありません」を表記 If Cells(Line, 6).Value = "" Then Cells(Line, 6).Value = "データがありません" GoTo コピー貼り付け End If コピー貼り付け: If Cells(Line, 6).Value = "データがありません" Then Cells(Line, 5).Copy 'コピーする Maxrow = Worksheets("輸入Parts").Range("A1").End(xlDown).Row + 1 Worksheets("輸入Parts").Range("A" & Maxrow).PasteSpecial Paste:=xlPasteValues '値を貼り付け End If 'H列の空欄をコピーして埋める If Cells(5, 12).Value = "" Then Cells(Line, 12).Value = "" ElseIf Cells(Line, 12).Value = "" Then Cells(Line, 12).Value = Cells(Line - 1, 12).Value End If 'E列の文字を「輸入シート」から検索しZ列に貼り付ける Cells(Line, 26).Value = Application.WorksheetFunction.VLookup(Cells(Line, 5).Value, Worksheets("輸入Parts").Range("A2:R20000"), 3, 0) 'E列を検索しデータが存在しない場合はZ列に「データがありません」を表記 If Cells(Line, 26).Value = "" Then Cells(Line, 26).Value = "データがありません" End If 'AD列の空欄をコピーして埋める If Cells(5, 30).Value = "" Then Cells(Line, 30).Value = "" ElseIf Cells(Line, 30).Value = "" Then Cells(Line, 30).Value = Cells(Line - 1, 30).Value End If 'E列の文字を「輸入シート」から検索しAM列に貼り付ける Cells(Line, 39).Value = Application.WorksheetFunction.VLookup(Cells(Line, 5).Value, Worksheets("輸入Parts").Range("A2:R20000"), 18, 0) 'E列を検索しデータが存在しない場合はAM列に「データがありません」を表記 If Cells(Line, 39).Value = "" Then Cells(Line, 39).Value = "データがありません" End If '「Unit price」の計算・円建と外貨建が合わさったインボイスの場合の合計金額 If Cells(Line, 14).Value = "" Then Cells(Line, 13).Value = Cells(Line, 17).Value * Cells(Line, 33).Value / Cells(Line, 7).Value Else Cells(Line, 17).Value = Application.WorksheetFunction.RoundDown(Cells(Line, 14).Value * Cells(Line, 16), 0) Cells(Line, 15).Value = Cells(Line, 16).Value * Cells(Line, 33).Value / Cells(Line, 7).Value End If 'T.Invoice Priceの計算 Cells(Line, 23).Value = Application.WorksheetFunction.Sum(Cells(Line, 17), Cells(Line, 18), Cells(Line, 19), Cells(Line, 20), Cells(Line, 21), Cells(Line, 22)) 'VLOOKUP関数が終わり、エラーが発生したら止まる On Error GoTo 0 '次の行に移り最後の行まで検索 Line = Line + 1 Loop End Sub

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する