Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7全般 : Excel 2010)
縦列全部のセル内の指定文字数以外を他の文字に置き換え
投稿日時: 18/06/20 19:17:46
投稿者: Mapleleaf

はじめまして。マクロやVBAに関してまだ興味持ち始めたばかりですが宜しくお願い致します。
 
縦列全体のセルに例えば指定した文字桁数が3桁で、それ以外の文字桁数(1桁/2桁/4桁以上は)
については任意の文字に置き換えたい(例:OTH)にする場合はどのようにコードをかけば
良いのでしょうか?
 

回答
投稿日時: 18/06/20 22:20:02
投稿者: simple

できているところまで示してみませんか?
例えばA1からA列最終行までのセルを順次繰り返して作業するコード(For ... Nextなど)は書けますか?
セルの値の長さで場合分けをして(Select Caseを利用)、
それぞれの場合にどのような文字列にすればよいかを書き出して見ましょう。

回答
投稿日時: 18/06/20 23:59:48
投稿者: WinArrow
投稿者のウェブサイトに移動

プログラムを組む(コーディング)のは、
小さな単位(部品という)を完成させ、それらを組み立てる
といったアプローチがよいでしょう。
 
まず、全体のイメージを箇条書きにします。
(1)指定列のセル範囲を特定する
(2)セル範囲に含まれるセルの個数だけ、以下を繰り返す。
(3)文字列の置換パターン別に
  検索文字列と置換文字列の対応を列挙
  条件があるならば、追加する。
 
上記の1つ1つの部品をコード化します。
 
(1)は、
@どこのセルから、どこのセルまでという考え方にする。
若しくは
A列全体とする
セル範囲をループする必要がある場合は、@
置換コマンドで対応できるような場合は、A
 
(2)ループする前提あらば、For 〜 Nextになるかな?
置換コマンドで対応できるならば、ループは必要なし
 
(3)検索文字列と置換文字列の対応表通りのオードを記述する。
 これは、いろいろな書き方があるので、
 条件とその対応表を見てからのアドバイスになります。
 
 
なお、マクロの記録というのがあるので
それを利用すると、部分的な部品を作成することができます。
ぜひ、トライしてみてください。

回答
投稿日時: 18/06/21 07:09:51
投稿者: simple

ああ、意味を取り違えていました。
        v = Cells(k, 1).Value
        If Len(v) <> 3 Then Cells(k, 1).Value = "OTH"
という意味だったのですかね。
あとは、繰り返しにするだけですから、
ご自分で基本テキストをみてトライしてください。

投稿日時: 18/06/21 14:18:07
投稿者: Mapleleaf

Simple 様
WinArrow様
 
早速ご丁寧なアドバイスいただき有り難うございます。いただいた回答を自分なりに
やってみたところここまではできました。具体的に説明させていただきますと、
外国の船から船員リストが送られてきた時に職名(英語)を電子入力通報の為に
3桁の指定コードにする必要があります。2000人ぐらいの船員がいる中で例えば
船長:CaptainであればCAPに置き換える必要がり他にも数名そのような3桁コードの
置き換えが必要になります。 指定職員以外のコードについてはOTH(OTHERの意味)に
置き換える必要があります。
 
順として: 1.まず数名の職員をマクロで記録して3桁コードに置き換える作業をマクロボタンにおく。
      2.数名先に三桁に置き換えてあとは全てOTHにしたいので3桁以外は、OTHに置き換える
       ボタンを割り当てる?
 
下記をマクロで三桁に置き換えはできたので、三桁コード以外をOTHにとしたいとう事です。
縦列についてはシート全体(下記であればGの縦列)で実行してOKだったマクロの記録の
コードは下記でした。これにsimple様のアドバイスにある下記を加える形に持っていけば
いいのかなと理解はしておりますが、まだ興味持ってマクロをいじりはじめたばかりなので
いきづまっております。
 
v = Cells(k, 1).Value
         If Len(v) <> 3 Then Cells(k, 1).Value = "OTH"
 
===============================================================================
Sub NACCS船員コード()
'
' NACCS船員コード Macro
' NACCS船員コード変換
'
 
' Columns("G:G").Select
    Range(Selection, Selection.End(xlDown)).Select
     
    Selection.Replace What:="CAPTAIN", Replacement:="CAP", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
========================================
個別にいただいたお二人様アドバイスにそれぞれ、返信コメントを入れれば良いのか
サイト初めて利用でルールがわかりませんでしたので、マナー違反であれば失礼致しました。

回答
投稿日時: 18/06/21 14:25:41
投稿者: mattuwan44

>船長:CaptainであればCAPに置き換える必要がり他にも数名そのような3桁コードの
>置き換えが必要になります。 指定職員以外のコードについてはOTH(OTHERの意味)に
>置き換える必要があります。

 
コードの変換の対応表をシート上に作っておいて、
Vlookup関数を入力して変換したらいいんじゃ?
そこでエラー値が返ってきたらそれをOTHに置き換えればいいと思います。

回答
投稿日時: 18/06/21 14:59:59
投稿者: WinArrow
投稿者のウェブサイトに移動

掲示のコードでやりたいことは、おおよそ察しが付きます。
 
当該列セルをいきなり置換して、3桁にすることは、
少し問題があります。
理由は、もともと3桁のものと置換された3桁との区別なつきません。
もともと3桁のデータは、OTHに置換できません。
 
 
置換(変換)するデータの方が少ないのですね?
 
一般機能(関数)を使った対処の方法を提案します。
 
最初に変換表を作成します。
当該ブック内の別シートでも、他ブックでもよいでしょう。
とりあえず、当該ブックの別シートで説明します。
 
まず変換表を別シートに作成します。
 

A列   B列
CAPTAIN CAP
※必要なだけ入力します。
※必要が生じたら追加すればよい。
この表に名前を定義します、・・・例;変換表
 
当該シートの方で・・・
作業列を使います。
作業列に数式を入力します。
当該データがG2~始まるとして
=IF(COUNTIF(INDEX(変換表,,1),G2),VLOOKUP(G2,変換表,2,FALSE),"OTH")
下までフィルコピーします。
 
作業列で仕事ができれば、そのまま
若し元データの列を置き換えたいのでしたら、作業列から「値」複写すればよいです。
 
なお、変換表を別ブックに作成し、次回の使用する場合は、数式をじゃ間変更する必要があります。
 
 
 

投稿日時: 18/06/21 15:02:35
投稿者: Mapleleaf

 mattuwan44様
 
アドバイス有り難うございます。
約10数名だけが必要不可欠な指定3桁コードに必要でマクロ内で全て指定に
置き換えはできてます。
それ以外の膨大な数の職名は全てOTHの三桁にひとくくりで置き換えで良いのでVLOOK関数とか
エクセル関数を使わないでマクロやVBAを興味持ち始めてやってみようと思いました。

回答
投稿日時: 18/06/21 15:29:59
投稿者: WinArrow
投稿者のウェブサイトに移動

関数での対応の方が処理速度も速い・・・・
 
でも、どうしてもと言うならば、ループしない方法を
 
やはり、作業列を使います。
仮にZ列とします。
 
Dim RowMax As Long
 
RowMAx = Range("G" & Rows.count).End(xlUp).Row
 
Range(Cells(2,"Z"),Cells(RowMax,"Z")).Formula = "=IF(COUNTIF(INDEX(変換表,,1),G2),VLOOKUP(G2,変換表,2,FALSE),""OTH"")"
 
試してみて

投稿日時: 18/06/21 19:46:20
投稿者: Mapleleaf

WinArrow様
 
誠に本件にお付き合いいただきアドバイスいただき有り難うございます。
 
仰有る通り、職名で実際OTH以外の三桁コードに変換するのは20名というところです。
 
確かに職名がいろんな船によっては短縮して,たまたま三桁(例CHIEF OFFICER→C/O)
等ということもありますので仰有る通りそういえば最初からたまたま三桁できたものは
変換がOTHにできないんだなという事に気付きました。ご指摘有り難うございます。
 
>関数での対応の方が処理速度も速い・・・・
 
上記も有り難うございます。ただせっっかくですの今後の勉強のためにもやってみようと
思います。
 
慣れない部分もあるのでちょっと時間かかるかもしれませんがいただいたアドバイスを理解しながら
トライさせていただきます。 結果を早くご報告できればと思います。
 
見ず知らずの者に本当に有り難うございます。
 

回答
投稿日時: 18/06/21 20:00:38
投稿者: mattuwan44

まぁ、いろいろ寄り道してみてですね^^
 
 
Sub test()
    Dim Rng As Range
    Dim c As Range
    Dim s As Range
     
    Set Rng = ranga(Range("G2"), Cells(Rows.Count, "G").End(xlUp))
     
    For Each c In Rng
        Select Case c.Value
            Case "CAPTAIN": s = "CAP"
            Case "CHIEF OFFICER": s = "C/O"
            Case Else: s = "OTH"
        End Select
        c.Value = s
    next
End Sub

投稿日時: 18/06/21 20:09:52
投稿者: Mapleleaf

 mattuwan44様
 
 有り難うございます!。また皆様本当にこんなにご指摘やアドバイスいただき感謝しております。
 また皆様スラスラと解決策を見いだして書けることに、感心しつくしております。
 
 皆様からいただいたアドバイスをじっくり寄り道理解しながらやらせていただきます。
 
 有り難うございます。

投稿日時: 18/06/22 17:14:22
投稿者: Mapleleaf

WinArrow様
 
変換表を別のシートにしっかり作成しました。
A列     B列          
役職名   指定3桁コード
CAPTAIN CAP
CHIEF OFFICER COF
CHIEF ENGINEER CEG
以下同様
 
のようにして、変換表というタイトルはシートにつけました。(ここがシートに名前を入れるのか
A列B列の全体の枠内に変換表という文字をいれるのかがわかりませんでした。)
====================================
Dim RowMax As Long
   
 RowMAx = Range("G" & Rows.count).End(xlUp).Row
   
 Range(Cells(2,"Z"),Cells(RowMax,"Z")).Formula = "=IF(COUNTIF(INDEX(変換表,,1),G2),VLOOKUP(G2,変換表,2,FALSE),""OTH"")"
 

についてはもとのシートのG列で、変換表の変換前のCAPTAINがA列であれば
 
Range(Cells(2,"A"),Cells(RowMax,"A")).Formula = "=IF(COUNTIF(INDEX(変換表,,1),G2),VLOOKUP(G2,変換表,2,FALSE),""OTH"")"
 
で宜しいのでしょうか?変換表1というのはA列で変換表2というのはB列という事になるのでしょうか?
また今回質問していく中でエクセル自体や関数についても勉強していかないといけない事を痛感致しました。
目の前にご指摘と有り難いアドバイスがあるにも関わらずうまくいかないのに、申し訳ないもどかしさを
感じております。
 
 
 
 
 

投稿日時: 18/06/22 17:21:11
投稿者: Mapleleaf

mattuwan44 様
 
皆様からいただいたアドバイスも含めて、四苦八苦しております。(汗)。
mattuwan44様からいただいたのも試しましたが、
 
オブジェクト変数またはwithブロック変数が設定されておりませんとでて、
これは何を意味するのか調べながら寄り道をやっております。 頑張ります!。

回答
投稿日時: 18/06/22 22:44:37
投稿者: simple

>オブジェクト変数またはwithブロック変数が設定されておりませんとでて、
>これは何を意味するのか調べながら寄り道をやっております。

Dim s As Range
は、
Dim s As String
のミスでしょう。

回答
投稿日時: 18/06/22 23:01:53
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
のようにして、変換表というタイトルはシートにつけました。(ここがシートに名前を入れるのか
A列B列の全体の枠内に変換表という文字をいれるのかがわかりませんでした。)

 
シート名を「変換表」にしても、しなくてもよいです。
 
A1からB列の最後まで選択して
名前ボックスに「変換表」と入力するだけです。
 
従って、変換表1も変換表2も不要です。
VLOOKUP関数は、
=VLOOKUP(検索キー,検索範囲,n,FALSE)
が基本形です。
検索キーは、元シートのG列セルになります。
検索範囲は、変換表になります。
変換表の左端列のセルが、検索キーと照合されます。
nは、検索範囲の中の何番目のデータを取得するかを指定します。
今回は2番目なので2です。
最後のFALSEは、検索キーと完全一致の指定です。
 

回答
投稿日時: 18/06/22 23:15:24
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
「シート」=「表」ではありません。
「表」は「シート」の中に作成するものです。
一般的には、シートの一部分(セル範囲)にリスト形式で作成した一覧形式の表を「表」といいます。
リスト形式とは、セル結合をせずに、先頭行を項目名とし、それ以下をデータ行としたものです。
ワープロでよく使う「同上」を意味する「〃」や「同上」は、使ってはいけません。
(並べ替えると「同上」ではなくなってしまいます。)
当該表は、用途に応じて昇順、又は、降順にならべます。またはランダムでもよい。
並べてあった方が、検索方法に依存しますが、高速処理が期待できます。
データ件数が少ない場合は、余り気にすることはない。

回答
投稿日時: 18/06/23 15:47:02
投稿者: mattuwan44

>オブジェクト変数またはwithブロック変数が設定されておりませんとでて、
ああああああああああああああああああああああ!
 
失礼しました。
Rangeオブジェクトを入れるように変数を宣言しておいて、値を代入するように書いているから、
オブジェクトを代入してくれよと叱られてます。
 
simpleさんから指摘があるように、文字列を入れる用の変数だから、
String型で変数の型を宣言する必要があります。
 
高速化を図りました。違いが解りますかね。
 
Sub 寄り道2()
    Dim Rng As Range
    Dim c As Range
    Dim s As String
    Dim v As Variant
    Dim i As Long
      
    Set Rng = ranga(Range("G2"), Cells(Rows.Count, "G").End(xlUp))
    v = Rng.Value
      
    For i = LBound(v, 1) To UBound(v, 1)
        Select Case v(i, 1)
            Case "CAPTAIN": s = "CAP"
            Case "CHIEF OFFICER": s = "C/O"
            Case Else: s = "OTH"
        End Select
        v(i, 1) = s
    Next
    Rng.Value = v
End Sub

投稿日時: 18/06/23 19:28:40
投稿者: Mapleleaf

simple様
 
有り難うございます。
>Dim s As String
 
という事で、後程トライしようと思っていた最中に、mattuwan44様より追加アドバイスいただきました。
有り難うございます。

投稿日時: 18/06/23 20:00:53
投稿者: Mapleleaf

 WinArrow様
 アドバイス有り難うございます。意味がだいぶつかめて勉強なります。
 後程時間をたっぷり使ってやらせていただきます。
 
mattuwan44様
 
 有り難うございます!下記説明わかりやすく勉強になります。
 >Rangeオブジェクトを入れるように変数を宣言しておいて、値を代入するように書いているから、
 >オブジェクトを代入してくれよと叱られてます
 
 変数の種類が増えて、調べたらそこからなんとなくではありますが
 高速化への違いがそこにあるのかな?と思っております。
 
 また度々申し訳ないんですが早速トライさせていただいたら
 今度は、’’型が一致しません’’とでてしまいました。
 
 ↑
 について調べたら
 対処:同じ型に変換してから処理を実行します。
 
 とありましたので、今そこに寄り道中です!。
 
 

回答
投稿日時: 18/06/23 20:26:28
投稿者: mattuwan44

>今度は、’’型が一致しません’’とでてしまいました。
 
どこだろー^^;読んだだけではわかりませんね^^;
試してみろという話なんですが^^;
そういう時は、メッセージの内容とともに、エラーが出る行も教えていただけるとありがたいです。
 
ま、寄り道ついでに試行錯誤するのは有意義なことだとおもいますが^^;;
(自己弁護orz)

回答
投稿日時: 18/06/23 21:10:11
投稿者: WinArrow
投稿者のウェブサイトに移動

エラーの原因は、おそらく↓
> Set Rng = ranga(Range("G2"), Cells(Rows.Count, "G").End(xlUp))

投稿日時: 18/06/23 21:54:31
投稿者: Mapleleaf

mattuwan44 様 (と、mattuwan44様とのやりとりにご助言いただいたsimple様・WinArrow様)
 
ありがとうございます!。
mattuwan44様からのコードに、ご助言いただいた点を参考に試していったら
できました。こんなにアドバイスいただきながら、私の能力不足でできなかったら
申し訳ないとプレッシャーもあったので、実行かけて変換された瞬間は大げさかもしれませんが
感無量でした。(実行して変換が行われたことでコードの一つ一つの意味を改めて勉強
できます。)
 
あとは職員名と三ケタコードを追加していけば、mattuwan44様からのアドバイスのコードで
対処できそうです!。
 
またWinArron様からアドバイスいただいた方法の変換表作成してvolook関数のほうも
しっかり達成できてものにできればと思い、すぐにチャレンジします。結果は報告いたします。
 
皆様お忙しい中時間割いてアドバイスいただき本当にありがとうございます!
 
 
 
 

投稿日時: 18/07/06 09:13:20
投稿者: Mapleleaf

WinArow様
 
報告遅くなり誠にありがとうございました。
おかげさまでできました!。
 
また今後もvbaで質問させていただくことあるかもしれませんが、どうぞよろしくお願いいたします。