マクロでセルの値を判定して決められた規則で別の値に変換する

質問

マクロでセルの値を判定して決められた規則で別の値に変換する

Ｅ列には従業員番号が入ってます。
半角の数字で５桁です。
00001とか10234とか。
このファイルは約10,000行あります。
ファイルを開くたびに行数は変更されています。
昨日は1,000行、今日は4,000行、明日は8,900行とか。
10,000行の中で同じ従業員番号は何回もあります。

例えば00007は
3,45,678,1023,4567,5345行目に有り計6ケ所とか。

でこの従業員番号を従業員名に変換したいです。
変換した従業員名はF列に入力したいです。
まだ従業員名がなく従業員番号だけが有る場合は
変換しないで番後をそのままセットしたいです。

私のレベルで作成可能なのは

Sub 名前変換()

'2行目から行う
行 = 2
Do
'E列にデータがなくなったら終了
If Cells(行, 5).Value = "" Then Exit Do

'従業員番号00001は山山山山さん
If Cells(行, 5).Value = "00001" Then
Cells(行, 6).Value = "山山山山"

'従業員番号00002は川川川川さん
ElseIf Cells(行, 5).Value = "00002" Then
Cells(行, 6).Value = "川川川川"

'ElseIf Cells(行, 5).Value文とCells(行, 6).Value文を従業員数分ここに追加

'条件が無い場合は従業員番号をそのまま値とする
Else
Cells(行, 6).Value = Cells(行, 5)
End If
行 = 行 + 1
Loop
End Sub

これなら、例えば従業員名が変更された（結婚して苗字がかわったとか）
そのCells(行, 6).Value文の　"　"　の部分を変更。
従業員数が増加の場合は
都度ElseIf Cells(行, 5)文とCells(行, 6).Value文を
追加していく事でメンテが可能です。

なのですが、現在は従業員数が２０名の部署用なのでいいのですが
従業員数が１００名の部署の場合に使う場合、導入時に
ElseIf Cells(行, 5)文とCells(行, 6).Value文を100個も
作成しなければなりません。何かいい方法はないでしょうか？

この対象ファイルはCSVファイルです。
エクセルファイルを開いてその中のマクロ１を起動すると
CSVファイルをインポートし別BOOK上でマクロ２（マクロ１のCallで呼び込む）にて編集
（罫線を入れたり、並べ替えをしたり、色をつけたり、太字にしたり．．．）
を行いマクロ１，２を引きつかず名前をつけて保存までをマクロ１で行います。
その編集するマクロ２の中に組み込みたいです。

回答No.6

kmetu · ベストアンサー

ご自身で解決した方法でもいいですが
(ループして直している分時間がかかるかもしれませんね)

以下のように

Range("F1").Formula = "=VLOOKUP(E1,[自動編集.xls]従業員名簿!$A:$B,2,FALSE)"

↓

Range("F1").Formula = "=IF(ISNA(VLOOKUP(E1,[自動編集.xls]従業員名簿!$A:$B,2,FALSE)),E1,VLOOKUP(E1,[自動編集.xls]従業員名簿!$A:$B,2,FALSE))"

に変更してください。

お礼

ありがとうございました。
まだ行全部を調べるのはループしかわかりませんので難しいです。

教えていただいた
Range("F1").Formula = "=IF(ISNA(VLOOKUP(E1,[自動編集.xls]従業員名簿!$A:$B,2,FALSE)),E1,VLOOKUP(E1,[自動編集.xls]従業員名簿!$A:$B,2,FALSE))"

でできました。
エクセルの関数の式のVOOKUPからすると難しいです。

マクロ２で１４種類の編集作業をしていて、それだけでも時間がかかります。
そのマクロ２の中に本件を追加をしたわけで速度ですが
１０行のテストデータでは差が有りません。

ですが本番の１０，０００行において、#N/Aのセルがほとんどない場合だと
私の記述でも大差ないのですが、#N/Aの値が多いとさすがに遅いですね。

どうもありがとうございました。

回答No.7

kmetu

蛇足ですが

Do～Loopの部分は

以下のようにも書き換えられます

Dim CheckRange As Range

For Each CheckRange In Range("F1:F" & Range("E" & Rows.Count).End(xlUp).Row)
If IsError(CheckRange.Value) Then
CheckRange.Value = Cells(CheckRange.Row, 5)
End If
Next

ちなみに
Range("E" & Rows.Count).End(xlUp).Row
はE列の最終行(Rows.Count)から調べて最初に見つかったデータのある行数を示します。

最初の行から調べる場合には
Range("E1").End(xlDown).Row
とします

途中のデータのない行までを結果として出すかどうかでどちらを使うかを決めます。

お礼

お礼が遅れました。
すでに解決していますが教えていただいたので試しました。
こちらも同じように動きます。
結果は同じでもいろいろなアプローチがあり
センスも必要だと感じました。
どうもありがとうございました。

実用はこの前に教えていただいた物でいこうと思います。
どうもありがとうございました。

----------

Sub 名前変換LOOP文2()

'2010年10月19日
'[●●]の部分はこのマクロを挿入するエクセルファイル名にする事
'従業員名簿にデータが無い場合は#N/Aになるのでそれを検索値に変換を行う

Range("F1").Formula = "=VLOOKUP(E1,[●●.xls]従業員名簿!$A:$B,2,FALSE)"
Range("F1").Copy
Range("F1:F" & Range("E" & Rows.Count).End(xlUp).Row).PasteSpecial

'↑E列のデータがある最後の行まで式を貼り付けます

Columns("F:F").Copy
Range("F1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'↑F列をコピーしF列自身に値の貼り付けをし、式(Vlookup)から値へと変更します。

Application.CutCopyMode = False

'VOOKUPで検出できなかったＦ列の値の#N/AをE列の値を転記する

Dim CheckRange As Range
For Each CheckRange In Range("F1:F" & Range("E" & Rows.Count).End(xlUp).Row)
If IsError(CheckRange.Value) Then
CheckRange.Value = Cells(CheckRange.Row, 5)
End If
Next
End Sub

回答No.5

kmetu

Range("F1").Formula = =VLOOKUP(E1,[BOOK1.xls]従業員名簿!$A:$B,2,FALSE)"

「"」がぬけてました

Range("F1").Formula = "=VLOOKUP(E1,[自動編集.xls]従業員名簿!$A:$B,2,FALSE)"

です。

[自動編集.xls]従業員名簿!$A:$B
の部分は実際にセルに
=VLOOKUP(E1,
と入力してから
自動編集.xlsの該当部分の列を選択すると自動で値が入りますのでそれを参考にしてください。
VLOOKUPをきちんと入力して実際にデータが反映されるのを確認して、その式をコピーしマクロのほうに貼り付けた方が間違いがありません。
正常に動いた式をマクロの記述では「"」で囲ってください。

お礼

できました。ありがとうございます。

ついでで申し訳ないのですが
従業員名簿にない番号がＥ列に有るとＦ列には
#N/A　と代入されます。

この場合は、Ｅ列の値、をＦ列にそのまま代入したいのですが、
これは教えていただいた記述の中で処理が必要なのでしょうか？

記述の後に、この#N/A　の処理の記述をすればいいのでしょうか？

またその記述はどうなりますでしょうか？

If Cells(Line, 6).Value = "" Then
Cells(Line, 6).Value = Cells(Line, 5)

では駄目ですよね。

お手数かけます。お願いします。

補足

すいません。教えていただいた記述の後ろにくっつけてみました。
思ったように動きましたがこれで正しいのでしょうか？
お手数欠けてすみません。

Sub 名前変換()

'2010年10月14日

Range("F1").Formula = "=VLOOKUP(E1,[自動編集.xls]従業員名簿!$A:$B,2,FALSE)"
Range("F1").Copy
Range("F1:F" & Range("E" & Rows.Count).End(xlUp).Row).PasteSpecial
'↑E列のデータがある最後の行まで式を貼り付けます
Columns("F:F").Copy
Range("F1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'↑F列をコピーしF列自身に値の貼り付けをし、式(Vlookup)から値へと変更します。
Application.CutCopyMode = False

'VOOKUPで検出できなかったＦ列の値の#N/AをE列の値を代入する
'1行目から開始する
行 = 1
Do
'6=F列を検索
If IsError(Cells(行, 6).Value) Then
'値がエラーの時、E列の値を代入
Cells(行, 6).Value = Cells(行, 5)
'エラーでない時
Else
'F列の値がなくなったらDoを停止
If Cells(行, 6).Value = "" Then Exit Do
End If
行 = 行 + 1
Loop
End Sub

回答No.4

kmetu

訂正

　　　　A　　　　　　　　B
1　従業員番号　　従業員名

にしたら

Range("F1").Formula = =VLOOKUP(E1,[BOOK1.xls]従業員名簿!$G:$H,2,FALSE)"
↓
Range("F1").Formula = =VLOOKUP(E1,[BOOK1.xls]従業員名簿!$A:$B,2,FALSE)"

になります。

お礼

ありがとうございます。申し訳ありません。

教えてもらった物ですが

Range("F1").Formula = =VLOOKUP(E1,[BOOK1.xls]従業員名簿!$A:$B,2,FALSE)"

が赤字になります。構文エラーと出ます。

=が2個並んでいたのではずしたのですが

Range("F1").Formula = VLOOKUP(E1,[BOOK1.xls]従業員名簿!$A:$B,2,FALSE)"

今度はコンパイルエラーと出ました。

教えていただいた物が実験できません。

[BOOK1.xls]の部分は

マクロ1,2,3が入ったファイルはファイル名が

自動編集

なので

[自動編集.xls]

でいいのでしょうか？

そう修正してもコンパイルエラーでした。

お手数かけます。

回答No.3

kmetu

> データシートはできればマクロ1,2,3が入ったBOOK1のシート２に名称を「従業員名簿」
> とかにして入れておきたいのですが、無理な構想でしょうか？

それでも大丈夫です。

たとえば

Range("F1").Formula = =VLOOKUP(E1,[BOOK1.xls]従業員名簿!$G:$H,2,FALSE)"
Range("F1").Copy
Range("F1:F" & Range("E" & Rows.Count).End(xlUp).Row).PasteSpecial
'↑E列のデータがある最後の行まで式を貼り付けます
Columns("F:F").Copy
Range("F1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
　　'↑F列をコピーしF列自身に値の貼り付けをし、式(Vlookup)から値へと変更します。
Application.CutCopyMode = False

ということをやってみてください。

従業員名簿のフォーマットは

　　　　A　　　　　　　　B
1　従業員番号　　従業員名

にしてください。

お礼

ありがとうございます。試してみます。

ちなみに回答を閲覧するまで独自で頑張りましたが失敗でした。
マクロ２の途中でCallで下の方に記述した
プロシージャーを呼び出すようにしてみました。
結果は

　　'検索されなかったときの処理。上記（※１）の部分Line6=F列に値がない
　　If Cells(Line, 6).Value = "" Then
　　'Line6=F列にLine6=E列の値を代入
　　Cells(Line, 6).Value = Cells(Line, 5)

上記の記述が働くようで、Ｆ列には全てE列の値がコピーされてしまい
名簿の値をひっぱてくれません。原因不明です。

　　　Worksheets("従業員名簿").Range("A1:B100"), 2, 0)

の部分が怪しいと思うのですが？手に負えないので教えてもらった物で挑戦します。

-------------------------------

Sub 名前変換()

'2010 年10月13日

'検索する対象値があるシート選択
Sheets("集計").Select
'そのシートの検索開始の行数を選択2行目。
Line = 2
'そのシートの検索値の列指定5=E列。セルF4の値が検索したい値。
'その値がなくなったら検索を終了させる.Value = ""を追加。
Do Until Cells(Line, 5).Value = ""
'エラーとなっても次に進む
On Error Resume Next
'検索結果を記入する列を指定。Line6=F列（※１）
'検索する値があるシートとその列を指定
'VLookup(Cells(Line, 5)の部分。5=F列
'検索されるシートと検索範囲を指定
'Worksheets("従業員名簿").Range("A2:B100")→セルA2からセルB100まで
'検索されたらその行のどの列の値を結果とするのか指定　2=B列
'検索方法指定0=FALSE完全一致。
Cells(Line, 6).Value = Application.WorksheetFunction.VLookup(Cells(Line, 5).Value, Worksheets("従業員名簿").Range("A1:B100"), 2, 0)
'VLOOKUP関数が終了又はエラーが発生したら止まる
On Error GoTo 0
'検索されなかったときの処理。上記（※１）の部分Line6=F列に値がない
If Cells(Line, 6).Value = "" Then
'Line6=F列にLine6=E列の値を代入
Cells(Line, 6).Value = Cells(Line, 5)
End If
'2行目から開始なので次の行の値を検索値とする
Line = Line + 1
'検索する値がなくなるまで繰返す
Loop
End Sub

回答No.2

kmetu

If～ElseIf
を沢山続けるよりも
Select Case
を利用したほうがいいですよ

ただ今回はIf～ElseIf をやめて
従業員一覧を作成しインポート時に別ブックの利用しないセル(たとえばG列H列)にコピーして

Range("F1").Formula = "=VLOOKUP(E1,$G$1:$H$20,2,FALSE)"

などとしてこれを必要なだけコピーするマクロを作成したほうがメンテが楽でしょう。

お礼

ありがとうございます。

・BOOK1（マクロ１，マクロ２、マクロ３が入っている）を開く
・シート１にコマンドボタンがあるだけ
・このコマンドボタンをクリック
・マクロ１が起動
・マイネットワークの指定CSVファイルを開く
・BOOK2のシート１にそのデータが貼りつく
　（A列からK列まで。行数は開くたび相違）
・そのシート1はシート名を「編集」という名に変更
・CSVファイルは閉じられる
・マクロ１の中のCall　マクロ２が起動
・BOOK2のシート１が編集される
　（色塗り、罫線、太字、セルの値をセンターなど）
　最終的にA列からM列に増える
・マクロ２の中のCall　マクロ３が起動
・B00K2のシート１が編集される
　（ヘッダーやフッダーやタイトル等の印刷設定）
・マクロ１に戻る
・システム日付を利用して「編集済20101013．XLS」という
　名前で指定されたマイネットワーク内のフォルダに保存される

できあがった「編集済20101013．XLS」にはマクロも式もないです。

という流れです。（ここまでは完成しています。）

現在これで「編集済20101013．XLS」を開くと
Ｆ列がCSVファイルから貼り付けたままの数字５桁の表示です。
ここを、マクロ２の中で従業員名に変換しておきたいのです。

＞インポート時に別ブックの利用しないセル(たとえばG列H列)にコピー

この場合、出来上がった「編集済20101013．XLS」の最終2列に
そのデータが残ってしまいと思うのですが違っていますでしょうか？
できれば残したくないです。
マクロ２の中で変換後この2列をDELETEすればいいでしょうか？

＞Range("F1").Formula = "=VLOOKUP(E1,$G$1:$H$20,2,FALSE)"
マクロ２の記述の中の最終又は、Callでプロシージャーを呼び出しての
対応を考えてますが、そこの記述がよくわかりません。
データシートとの関係、また例えば10,000行だった場合の全行への反映
（ただし行数は毎回相違）の部分です。
サンプル文例を教えていただきたいのですが可能でしょうか？
2つのインポート記述は難しいので、
データシートはできればマクロ1,2,3が入ったBOOK1のシート２に名称を「従業員名簿」
とかにして入れておきたいのですが、無理な構想でしょうか？

勝手ですいません。

回答No.1

DIooggooID

VLOOKUP 関数を使って、対応づけする方法が簡単だと思いますが、

マクロ機能で実現したいですか？

http://allabout.co.jp/gm/gc/297725/

参考URL：

http://allabout.co.jp/gm/gc/297725/

お礼

マクロ１が組み込まれたエクセルファイルを開くと
ボタンが1個だけの状態です。
そのボタン1個をクリックすると
ＣＳＶファイルを別ブックにインポートして
そのマクロ１内のcallで呼びだしたマクロ２でいろいろ編集して
編集終了後にマクロ１にて名前をつけて保存まで行い、
メッセージボックスで「終了」と表示がされます。
作業者はエクセルを開いてボタンをクリックするだけです。
できあがったファイルはマクロも式も入っていません。
（→そうしたいのです。）

この状態においてVLOOKUP 関数を組み込むイメージがわかないのです。
従業員番号と名前のデータシートをそのマクロの入ったエクセルのシート２に置くとして
VLOOKUP 関数の式はどこに入れればいいのかわからないのです。
申し訳ありません。