セルの表示形式とデータ型は同じじゃない ”(-“”-)”
VBAで転記ツールなどを作成していて、転記元も転記先も与えられたExcelファイルを利用する場合、各シート内のフォーマットは触ることができないため、この表示形式とデータ型の違いに苦しめられることがある。
と言ったら、完全解決したと言いたいけれど、現状は、まだとりあえず・・・の状況
シート内は、私が転記するセル以外はほとんど、VLOOKUP関数が入れられているし、触れない。
さらに転記する列データは英数字と記号の混合と数字のみと混じったデータで、それをVLOOKUPの検索対象として処理してある。
そのため、VLOOLUPで検索対象がないと「＃N/A」が多発・・・
本当の解決策が見つかるまで、検証して行こうと思う。いつになるかわからないが・・・
とりあえず簡単なデータで現状の確認
B列に表示形式を変えて、値に「10」を入力して、データ型を確認してみた
表示形式
セル【B2】：標準
セル【B3】：文字列
セル【B4】：数値
TypeName関数でデータ確認
セル【B2】：Double型
セル【B2】：String型
セル【B2】：Double型
セル【B2】は、エラーインジケーターが表示されている
ここのデータは元データなので触れない
これらのデータを転記してみる
セル範囲の表示形式
【E2:E4】：標準 【F2:F4】：文字列 【G2:G4】：数値に設定してある。
VLOOKUPの検索範囲
セル範囲【A8:B9】番号の表示形式は「標準」・・・①
セル範囲【A14:A15】 番号の表示形式は「文字列」・・・②
配列にて転記
セル範囲【B2:B4】を配列(Variant型)に格納して、セル範囲【E2:E4】【F2:F4】【G2:G4】に転記
転記先のセルは、表示形式通りにみえる。
ただし、セル範囲【F2:F4】は、文字列の右揃えになっているが、セル【F3】だけが、エラーインジケーターが表示されている。
VLOOKUPの結果 セル範囲【E2:G4】を検索値
①を検索範囲としての結果：セル範囲【E8:G10】
②を検索範囲としての結果：セル範囲【E13:G15】
検索範囲が標準・文字列に関係なく、セル【F3】を検索値としたものだけが「#N/A」エラーとなった
実際にセル範囲【F2:F4】の表示形式は文字列であるが、②セル範囲【A14:A15】 の検索する番号は、表示形式は「文字列」にも関わらずだ
ここで、さらに疑問が出てきた。
VLOOKUPの検索範囲は、標準でも文字列でも、「10」と言う値は、Excel自体が数値と認識していることだ
各セルに入力されたデータ型と検索対象のデータ型を確認
はやり、セル【F3】だけが「String型」
Debug.Print "セル【A8】のデータ型 ：" & TypeName(Range("A8").Value)
Debug.Print "セル【A14】のデータ型 ：" & TypeName(Range("A14").Value)
こちらも、やはり表示形式を文字列にしても検索対象は「Double型」
なので、「#N/A」エラーが発生する
と、理由は、判明した
しかし、私が持っている案件は、値「10」のような数値変換できるデータだけじゃなく、記号と英数字が混じった文字列も混在している・・・
しかも、その部分は触れない ”(-“”-)”
私が触れるところに一時転記をして、それを指定の位置に再転記する
元データセル範囲【B2:B4】を標準形式を標準で指定したセル範囲【M2:M4】に転記後、セル範囲【E2:E4】【F2:F4】【G2:G4】に再転記
セル【F3】のエラーインジケータが消えてる
以前のデータと確認してみる
VLOOKUP関数でエラーになっていたセル【F9】【F14】のエラーも無くなった
簡単なデータの表示形式違いによる「#N/A」エラーの回避
現状での回避策は、文字列表示形式のセルで「#N/A」エラーを回避するには、別シートなどを利用して一時的に転記を行い、その後、指定されているセルに転記すると大丈夫そう。
この再転記により、転記先のデータ型は、データの表示形式に関わらず、数値と認識できるデータは、「Double型」となった。
そのため、VLOOUP関数の検索対象のセル範囲が「標準」「文字列」に関わらず、また、検索値のセルが「標準」「文字列」に関わらず、一時転記を噛ませて転記をするとエラーを回避できた。
次回は、もう少し、複雑なデータの検証を行いたいと思う。
コメント