- ベストアンサー
【VBA】「同じ文字を含むセルがあるならば」とやりたい
こんばんは。 エクセル2003を使用しています。 例えば A1→「りんご」 A2→「りんご食べたい」 の場合、 「りんご」は2個以上あります としたいのですがうまくいきません。 Sub 重複() For 行 = 1 To Cells(65536, 1).End(xlUp).Row If Cells.Find(what:=Range("a" & 行), LookAt:=xlPart) Is Nothing Then Else 'あるならば MsgBox Range("a" & 行) & "は2個以上あります" End If Next End Sub これだと取得セルもカウントされてしまうため、必ずMsgBoxが表示されてしまいます。 どうすれば取得セル意外にも取得セルを含むセルがあるかを調べられるのでしょうか? そしてこれは A1→「りんご」 A2→「りんご食べたい」 A3→「みかん」 A4→「みかんはオレンジ」 A5→「バナナ」 ・ ・ ・ と続いており 最終的には →「りんご食べたい」 →「みかんはオレンジ」 →「バナナ」 にしたいのです。 よろしくお願いします。
- オフィス系ソフト
- 回答数5
- ありがとう数5
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
単に、同じ文字列を含むセルの数をカウントするなら Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & 行) & "*") で良いかと。 最終的には、同じ単語を含む文字列のうち、一番文字数の多い文字列だけを残したいと言うことでしょうか? 以下のマクロは、同じ単語を含む文字列のうち、一番文字数の多い文字列を探します。そして、同じ単語を含む文字列を、探し出した一番文字数の多い文字列で置換します。 例) A1:りんご A2:りんご飴 A3:りんご飴食べたい ↓ A1:りんご飴食べたい A2:りんご飴食べたい A3:りんご飴食べたい 後は、フィルタを掛けて重複を除けば望みの物になるかと。 Sub Sample() Application.ScreenUpdating = False nlast = Range("A1").End(xlDown).Row 'A列の最終行 For 行 = 1 To nlast '同じ文字列を含む行が無いかを確認 rtn = Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & 行) & "*") '同じ文字列を含む行が有った場合 If rtn >= 2 Then '---ある文字列を含む最大文字数の行を調べる nMaxLen = 0 nMaxRow = 0 For 行2 = 1 To nlast '+++ある文字列を含む文字列のうち最大文字数の行を調べる rtn2 = 0 If InStr(Range("A" & 行2), Range("A" & 行)) > 0 Then rtn2 = Len(Range("A" & 行2)) End If If rtn2 > nMaxLen Then nMaxLen = rtn2 nMaxRow = 行2 End If Next 行2 If 行 <> nMaxRow Then '+++ 置換をかける Columns("A:A").Replace What:=Range("A" & 行) & "*", Replacement:=Range("A" & nMaxRow) End If End If Next 行 Application.ScreenUpdating = True End Sub あくまでサンプルですので、変数の宣言やエラー処理は入れて居ません。
その他の回答 (4)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 すでに解決しているような無視して構いませんが、 >A1→「りんご」 >A2→「りんご食べたい」 >A3→「みかん」 >A4→「みかんはオレンジ」 >A5→「バナナ」 「りんご・みかん・バナナ」 は、それぞれ検索キーワードではないでしょうか。 それが、被検索語と同じ場所にあるというのは、ちょっと変ですね。 すくなくとも、「りんご・みかん・バナナ」という検索キーワードを別にしないといけないように思いますが、それぞれのデータをすべて検索キーワードキーワードとしたら、検索してヒットすれば、後は、検索しないようにしてみました。 >最終的には >→「りんご食べたい」 >→「みかんはオレンジ」 >→「バナナ」 実際のデータはどういうものかは分かりませんが、最終時には、重複を除去することだとは思います。 しかし、このようなデータでも、以下の場合は、3個のデータしか抽出しません。 ------------------ りんご りんご食べたい りんご食べたい りんご食べたくない みかんはオレンジ バナナ りんご食べたい りんご食べたい りんご食べたくない ------------------ 出力データ バナナ みかんはオレンジ りんご食べたい '------------------------------------------------- Dim rng As Range Dim k As Long Dim Ar() As String Const SH2 As String = "Sheet2" '書き出すシート Const COL As Integer = 1 'カウントの書き出す列、右ひとつとなり Sub CheckDouble() '昇順に並べられていることが条件です。 Dim buf As Integer Dim i As Long Dim j As Long Dim flg As Boolean Application.ScreenUpdating = False Set rng = Range("A1", Range("A65536").End(xlUp)) rng.Offset(, COL).ClearContents k = 1 With rng For i = 1 To .Rows.Count For j = i + 1 To .Rows.Count If .Cells(i, 1).Value <> "" Then buf = InStr(.Cells(j, 1).Value, .Cells(i, 1).Value) If buf > 0 And .Cells(j, 1).Offset(, COL).Value = "" Then .Cells(j, 1).Offset(, COL).Value = k flg = True End If End If Next j If flg And .Cells(i, 1).Offset(, COL).Value = "" Then .Cells(i, 1).Offset(, COL).Value = "o" & CStr(k) k = k + 1 flg = False ElseIf .Cells(i, 1).Offset(, COL).Value = "" Then .Cells(i, 1).Offset(, COL).Value = k k = k + 1 End If Next i End With Call PickUp Worksheets(SH2).Range("A1").EntireColumn.ClearContents Worksheets(SH2).Range("A1").Resize(k).Value = Application.Transpose(Ar()) rng.Offset(, COL).ClearContents Application.ScreenUpdating = True Set rng = Nothing If Ar(0) <> "" Then MsgBox "データを " & Worksheets(SH2).Name & " に " & k - 1 & " 個出力しました。" End If End Sub Sub PickUp() Dim Ar2() As Long Dim c As Variant Dim i As Long Dim buf As Variant ReDim Ar(k - 1) ReDim Ar2(k - 1) i = 1 For Each c In rng.Offset(, COL) If IsNumeric(c.Value) Then buf = Application.Match(c.Value, Ar2(), 0) If IsError(buf) Then Ar2(i - 1) = c.Value Ar(i - 1) = c.Offset(, -COL).Value i = i + 1 End If End If Next End Sub
お礼
ありがとうございます。
- imogasi
- ベストアンサー率27% (4737/17068)
3,4やり方が有る。 標題どおりの質問ととる。2つ以上は考えない。 「1つでもあれば」渡海する。(標題とその後の内容が違ってないかな。)いくつ有るかとは採らないとして。 (1)Findメソッド 本来はセルの値がそっくり同じセルを探すが、引数をLookAt:=xlPartにすると「文字を含む」に出来る。 マクロの記録で、コードのおおよそはわかる。 Findは最初の該当しか指摘しない。本質問ではそれでよいが。 全て数え上げるのは次からFindNextメソッドを使う。 Sub test02() Set x = Worksheets("Sheet1").Range("A1:E10").Find(what:="aa", LookAt:=xlPart) If x Is Nothing Then Else MsgBox x.Address End If End Sub ーーーーーーーーーーーー (2)VBAのCountIF関数 そこで「*」(ワイルドード)の利用 Sub tesr01() x = Application.WorksheetFunction.CountIf(Range("A1:E10"), "*AA*") MsgBox x End Sub 以上は回答が出ている (3)VBの Instr関数の利用 Sub test03() For Each cl In Range("A1:E10") p = InStr(cl, "aa") If p <> 0 Then MsgBox "aaを含むセルあり" & cl.Address Exit For '打ち切り End If Next MsgBox "aaを含むセルなし" End Sub
お礼
ありがとうございます。
- hotosys
- ベストアンサー率67% (97/143)
こんなのはどうでしょうか? A1=りんご A2=りんご食べたい A3=みかん A4=みかんはオレンジ A5=バナナ とします。 この時 B1=COUNTIF(A:A,"*"&A1&"*") として、B1をB2:B5にコピーすれば、A列の各セルの重複(含む)数がB列に表示されると思います。 ここで B1=IF(COUNTIF(A:A,"*"&A1&"*")>1,1,"") として、B1をB2:B5にコピーすれば、重複(含む)があるセル(削除対象)のB列に1が表示されると思います。 そこで、B列を選択して[編集][ジャンプ][セル選択]で[数式][数値]を選択すると、削除対象の行のB列が選択されると思います。 これを行に拡張して削除すれば求めるデータになるかと思います。 Sub sample() Dim lastRow As Long lastRow = Range("A" & Rows.Count).End(xlUp).Row 'A列の最終行を取得 Columns("B").Insert '作業列挿入 Range("B1:B" & lastRow).Formula = "=IF(COUNTIF(A:A,""*""&A1&""*"")>1,1,"""")" 'データ範囲のB列に=IF(COUNTIF(A:A,"*"&A1&"*")>1,1,"")の式を代入 Range("B1:B" & lastRow).SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Delete 'B列で1のセルを行に拡張して削除 Columns("B").Delete '作業列削除 End Sub
お礼
ありがとうございます!
- KonnaMonde
- ベストアンサー率57% (97/170)
If Cells.Find(what:=Range("a" & 行), LookAt:=xlPart) Is Nothing Then Else 'あるならば MsgBox Range("a" & 行) & "は2個以上あります" End If では、A列に2つ以上のセルにデータが入っていれば、データの内容に関わらず常に 「MsgBox Range("a" & 行) & "は2個以上あります"」 が、表示されませんか? A1→「りんご」、A2→「みかん」 のみ入れて、「Sub 重複()」を走らせてみてください。 データ群の最終行番号を取得するとき、「Cells(65536, 1).End(xlUp).Row」 の代わりに、「Cells(Rows.Count, 1).End(xlUp).Row」 を使えば、エクセル2007でも使えます。 エクセル~2003の最大行数は、256^2=65536 ですが、2007では、1024^2=1048576 行に増えています。 >どうすれば取得セル意外にも取得セルを含むセルがあるかを調べられるのでしょうか? 方法は、いろいろ考えられますが、それよりも、データの内容、と並び方で、やり方が変わります。 質問の内容通りですと簡単ですが、データの並びが、A1→「りんご食べたい」、A2→「りんご」 に変わっただけで、すんなりとはいきません。 つまり、削除したい文字列を判別して、A1、A2に共通の文字列を取り出し、その文字列だけのセルを削除しなければなりません。 しかも例題のように順に並んでいるのが確定していれば楽ですが、離れた場所にあると難しくなります。 最終的には、すべての重複データを一旦配列に取り込み、そこで並び替えなどしてデータを整理した後に必要な処理を施すようになると思います。 いずれにしても、おおよそのデータの総数、重複するであろうデータの種類の数などが分からないと、コードは書けないと思いますので、その辺りの情報を補足欄にでも書いてください。
お礼
あら! 本当だ! 「りんご」しかなくても 必ずシートには「りんご」があるからmsgboxは表示されてしまうのですね。 確認不足でした。すいません。 (そして65536行は2003までなのですね。) このデータは Sub 重複()を実行する前に フィルタをかける →重複するレコードは無視する →重複していないデータをコピー →別シートに貼り付け →フィルタをかける →昇順に並べ替え をしています。 なので →「りんご食べたい」 →「りんご」 になることはないと思っています。 データ量は多くても 300行までです。 再度回答いただけると助かります! よろしくお願いします!!
関連するQ&A
- セルが何行なのかをVBAで取得したい
セルが何行なのかをVBAで取得したいのですが どういうコードにすればいいですか? 例えば、A1セルに a b c と入ってる場合、3行ですが それをVBAで取得するにはどうすればいいですか? Sub test() Dim r As Range Set r = Cells(1, 1) If r.Value Like "*" & Chr(10) & "*" Then MsgBox "改行があります" End If End Sub というコードで改行が有ることは取得できたのですが 何行かまでは取得する方法がわかりません。
- ベストアンサー
- Excel(エクセル)
- 空白セルをとばして転記
A B C D E 1 リンゴ リンゴ 2 ミカン 3 ミカン バナナ 4 5 6 バナナ 7 上記のように、空白のセルをとばして(詰めて)転記するにはどうすれば良いでしょうか? Dim n as long Dim k as long k = Range("A" & Rows.Count).End(xlUp).Row For n = 1 To k If Cells(n, 1) <> "" Then Cells(n, 4) = Cells(n, 1) n = n + 1 End If Next n とやると当然ながらD1のセルのみにしか転記できません A列の範囲を取得し、PasteSpecialのSkipBlanksで貼り付けてもうまくいきませんでした どうぞよろしくお願いします
- ベストアンサー
- Visual Basic
- 下記のマクロは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
- ベストアンサー
- オフィス系ソフト
- Excel2003 VBAで印刷 セル指定
よろしくお願いします。 ボタン5をクリックしてある範囲を印刷したいのですが、私が知っているものはセル指定でActiveSheet.PageSetup.PrintArea = Range("A90:K130") であれが印刷可能なのですが最後の行までにしたいのですがどうしてもうまくいきません。 ActiveSheet.PageSetup.PrintArea = Range(Cells(90, 1), Cells(r, 11)) それと同時に11列全部(文字は小さくてもいいのですが)一枚に印刷したのですが よろしくお願いします。 Private Sub CommandButton5_Click() res = MsgBox("決済記録を印刷します", vbYesNo + vbQuestion) If res = vbYes Then r = Range("A65536").End(xlUp).Row + 1 'Range(Cells(90,1),Cells(r,11)) '印刷したい範囲 ActiveSheet.PageSetup.PrintArea = Range("A90:K130") ActiveSheet.PrintOut preview:=True End If End Sub
- ベストアンサー
- オフィス系ソフト
- EXCEL VBA 文字 アドレス 検索 消去 セル
こんにちは。 EXCELの中にボタンを設置して以下のような動作をさせたいと思っておりますが、うまくいきません。 どのように改良すればよろしいでしょうか? 1.「end」の文字を検索し、そのセルのアドレスを取得する 2.取得したアドレスの行に関する値から一つ引いた値を計算で求める。例えばendがA10にあれば、A9とする 3.次にA3からA9までの範囲を消去する。 以下が自作したプログラムです。 Sub ボタン1_Click() Dim srcSheets As Worksheet Dim sinki As Integer sinki = MsgBox("データを消去しますか", vbYesNo) Select Case sinki Case vbYes '選択肢 Dim lngYLine As Long Dim intXLine As Integer Set Obj = Worksheets("Sheet2").Cells.Find("end") 'Sheet2の中でendを検索する。セルの場所を特定する。 If Obj Is Nothing Then MsgBox "endが見つかりません" Else lngYLine = Worksheets("Sheet2").Cells.Find("end").Row intXLine = Worksheets("Sheet2").Cells.Find("end").Column lngYLine = lngYLine - 1 End If With Sheets("Sheet2").Range("A3:intXLine+lngYLine").ClearContents End With End Select End Sub
- ベストアンサー
- Visual Basic
- 複数選択可能なリストボックス
Excel VBAの質問をさせてください。 シート(sheet1)のA列、セルA1から以下のデータがあるとします。 みかん りんご バナナ 苺 梨 バナナ バナナ みかん フォームのリストボックスで"みかん"と"バナナ"を選択した際、シート(sheet2)のセルA1にコピーしていきたいのですが機能しません。 単品、"みかん"だけを選択しても何もコピーされません。 どこがいけないでしょうか?? Private Sub UserForm_Initialize() With ListBox1 .AddItem "みかん" .AddItem "りんご" .AddItem "バナナ" .AddItem "苺" .AddItem "梨" .MultiSelect = fmMultiSelectMulti End With End Sub Private Sub CommandButton1_Click() Dim i As Long For i = 1 To 8 If Worksheets("Sheet1").Cells(i, "A").Value = Me.ListBox1.Value Then Worksheets("Sheet1").Cells(i, "A").Copy Worksheets("Sheet2").Cells(i, "A") End If End Sub
- ベストアンサー
- Visual Basic
- 【VBA】改行されたセルの条件
VBAにてA1のセルが画像のように改行されたセルであっても条件式で処理を行うようにしたいのですがうまくできません。 ↓式のように作成はしてみました どうしたら改行されたセルでも処理が行えるのでしょうか? Sub test() If Range("A1") = (行1行2の場合) Then 処理 End If End Sub
- ベストアンサー
- Excel(エクセル)
- マクロについて質問です。
A B C 1 3 りんご 2 赤 3 くだもの 4 6 みかん 5 オレンジ 6 くだもの 7 9 ぶどう 8 紫 9 くだもの というデータがシート1にあったとして、シート2のa2セルに6と入力すると以下のようにa5セル以降に抽出し、6という入力を消すと抽出したものも消えるようなマクロ 6 みかん オレンジ くだもの 上のような質問で下のマクロを教えていただけたのですが、もし、みかんのb列も3だった場合いしたのようにみかんの行まで抽出できるようにするには下の構文をどうかえたらよいでしょうか。下手くそな質問ですがよろしくお願いします。 3 りんご 赤 くだもの みかん オレンジ くだもの 現在、わかっている構文↓ Private Sub Worksheet_Change(ByVal Target As Range) 'この行から Dim c As Range, wS As Worksheet Set wS = Worksheets("Sheet1") With Target If .Address = "$A$2" Then If .Value <> "" Then Set c = wS.Range("B:B").Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then c.Offset(, 1).Resize(3).Copy Range("A5") Else MsgBox "該当データなし" End If Else Range("A5").Resize(3).ClearContents End If End If End With End Sub 'この行まで
- ベストアンサー
- Excel(エクセル)
- 空白セルと0値を識別させたい。(VBA)
すみません、誰か教えていただけますか。 シートの53、54、55行目にそれぞれ値入っています。 それを3行目にビジュアル的に表現させています。 55行目はセルの色で、53行目は数値があり同じ値が 続く部分の合計を出しています。 しかし、下記の記述ですと数値が0(変数D=0)の時に うまくいきません。空白セルと認識されてしまうと思います。 何か、良い方法があれば教えて頂けませんでしょうか。 宜しくお願いします。 Sub 表示() Dim a As Long Dim c As Long Dim D As Long Dim e As String Dim f As String c = 3 For a = 3 To 64 With Worksheets("Sheet1") If .Cells(53, a) <> .Cells(53, a + 1) Then .Range(.Cells(53, c), .Cells(53, a)).Select D = WorksheetFunction.Sum(Selection) e = D f = Selection(1).Offset(1, 0).Value G = Selection(1).Offset(2, 0).Value If D <> 0 Then Selection(1).Offset(-50, 0).Value = f + "//" + e .Range(.Cells(53, c), .Cells(53, a)).Offset(-50, 0).Select Selection.Interior.ColorIndex = G End If If Selection(1).Value = "" Then .Range(.Cells(53, c), .Cells(53, a)).Offset(-50, 0).Select Selection.ClearContents Selection.Interior.ColorIndex = xlNone End If c = a + 1 End If End With Next End Sub
- ベストアンサー
- オフィス系ソフト
- VBA:セルの空白を検索
A列を上から検索して(とりあえず100行まで)最初の空白セルのアドレスを取得したいです。 Dim CellAd As Range Set CellAd = Range("A1:A100").CurrentRegion.Find(What:="ABC") If CellAd Is Nothing Then Exit Sub Else MsgBox CellAd.Address End If 以上のコードで、A列にABCがあればそのアドレスを$A$15のような形で表示できました。 検索したいのは空白なのですが、どのように指定すればよいでしょうか。 What:=""やNullではダメだったので(自分、「Null」を勘違いしてるかもしれません)。 また、ここでは取得したアドレスをmsgboxで表示させているだけですが、 実際は取得したアドレスの行番号のみを取得して変数Add1に入れ、 以降のコードのセル範囲指定として使いたいです。 「1行目からAdd1行目までをコピーする」のように。 私のレベルでは、 ・範囲指定はRange("A1:A100")のように、「""」でくくらなければ使えない ・変数は""の中に入れたら文字列として扱われる との認識があるのですが、 このようなコードは可能でしょうか。
- ベストアンサー
- オフィス系ソフト
お礼
「最終的には、同じ単語を含む文字列のうち、一番文字数の多い文字列だけを残したいと言うことでしょうか?」 そうなんです!うまくいきました。 ありがとうございます。