EXCEL VBA 記号の削除

質問

A列3行目からはじまる（A列2行目タイトル=FA）データより"!"や"#"などの記号を取り除いた
ものをE列に表したいと思っています。
データを半角にして、ASC関数を使って記号を取り除こうとしたのですが、半角になるだけで
記号を取り除くことができません。

If の後、ASC関数は使用せず、"!"や"#"を指定しても結果が同じだったんですが
REPLACEの使い方が間違っているのでしょうか？

Dim セル As Range
Dim TARGET As Range
Dim 変換文字 As String
Dim i As Long
Dim W As Worksheet

Set W = Sheets("DATA転記")
Set TARGET = W.Range("A3", Range("A65536").End(xlUp))

For Each セル In TARGET

変換文字 = StrConv(セル.Text, vbNarrow)

For i = 1 To Len(変換文字)

If Asc(変換文字) >= 32 And Asc(変換文字) <= 47 And _
Asc(変換文字) >= 58 And Asc(変換文字) <= 64 And _
Asc(変換文字) >= 91 And Asc(変換文字) <= 96 And _
Asc(変換文字) >= 123 And Asc(変換文字) <= 126 Then _

変換文字 = WorksheetFunction.Replace(変換文字, i, 1, "")

End If

Next i

セル.Cells(, 5).Value = StrConv(セル.Text, vbWide)

Next セル

回答No.6

Cor_moriyan · ベストアンサー

> セルの範囲指定を足せばいいのでしょうか？
> また"!##aabbc@01^AB|"の意味を教えていただけると幸いです。

そうです。一例として記載したソースは、あくまで「こんなやり方でやれば良いよ」というだけのものでして。strEXに「!##aabbc@01^AB|」の文字列を格納し、このソースを実行するとstrANSに「aabbc01AB」が入ります。という例です。
質問者さんのソースにそのまま、このやり方を当てはめると。。。こうなりますネ。

Sub ex()
　　Dim セル　　　　As Range
　　Dim TARGET　　　As Range
　　Dim 変換文字　　As String
　　Dim 変換後文字　As String
　　Dim i　　　　　 As Long
　　Dim W　　　　　 As Worksheet

　　Set W = Sheets("DATA転記")
　　Set TARGET = W.Range("A3", Range("A65536").End(xlUp))

　　For Each セル In TARGET

　　　　変換文字 = StrConv(セル.Value, vbWide)
　　　　変換後文字 = ""
　　　　For i = 1 To Len(変換文字)
　　　　　　If Mid(変換文字, i, 1) Like "[０-９,ａ-ｚ,Ａ-Ｚ,あ-ん,ア-ン]" Then
　　　　　　　　変換後文字 = 変換後文字 & Mid(変換文字, i, 1)
　　　　　　End If
　　　　Next
　　　　セル.Offset(, 4).Value = 変換後文字
　　Next

End Sub

テストしたわけじゃありませんので(^^; あとはうまくアレンジしてください。

お礼

とても分かりやすく教えていただいて大変勉強になり、大変助かりました！
ありがとうございました　m(_ _)m

回答No.5

imogasi

なにを難しいことをやっているのかな。
簡単に
Sub test02()
d = Range("A65536").End(xlUp).Row
For i = 2 To d
x = Cells(i, "A")
y = Replace(x, "!", "")
z = Replace(y, "#", "")
Cells(i, "E") = z
Next i
End Sub
で良いのでは？
半角全角などあるのなら、Replaceを増やせば仕舞い。
＞For i = 1 To Len(変換文字)
If Asc(変換文字) >= 32 And Asc(変換文字) <= 47 And _
など、全くおかしい。変換文字全体が、そういう文字コードの範囲内になるわけではないだろうし。

回答No.4

Cor_moriyan

補足です。

一例として示したソースは、「英数字以外を削除する」という考え方ではなく
「英数字だけを抜き出す」という考え方になっています。
文字列を左から順に１文字ずつ判定し、英数字なら抜き出して別の変数に文字を繋げていく方法です。

回答No.3

Cor_moriyan

Asc(変換文字) >= 32

ですが「変換文字」変数に入っている“何文字目”を判定していますか？(^^;
上記のままでは「変換文字」変数に入っている“文字列”の１文字目を、延々と判定している事になりますよ。
やりたい事は、もしかして

Asc(Mid(変換文字,i,1)) >= 32

じゃありませんか？(^^)
それと「0～9,a～z,A～Z 以外を削除したい」という事ですよね？であれば、IF文に Like 演算子を使用した方がシンプルかつ分かりやすくて良いです。

If Not Mid(変換文字,i,1) Like "[0-9,a-z,A-Z]" Then

ただ、If文後に Replace 文で「""」に置換してしまっていますよね？そうすると文字を削除した分、前にドンドン詰まって行くので「いま何文字目？」と聞いてもズレてしまいます。
なので、そこも注意が必要です。

で。一例です。
Sub ex()
　　Dim strEX　 As String
　　Dim strANS　As String
　　Dim intCNT　As Integer

　　strEX = "!##aabbc@01^AB|"

　　strANS = ""
　　For intCNT = 1 To Len(strEX)
　　　　If Mid(strEX, intCNT, 1) Like "[0-9,a-z,A-Z]" Then
　　　　　　strANS = strANS & Mid(strEX, intCNT, 1)
　　　　End If
　　Next
End Sub

補足

ご投稿いただき、ありがとうございます。
非常にわかりやすくご回答いただき、大変勉強になります。

ひらがなやカタカナもあるので変換文字をvbwideで全角に修正し
If Not Mid(変換文字, i, 1) Like "[０-９,ａ-ｚ,Ａ-Ｚ,あ-ん,ア-ン]" Thenにし
取り急ぎreplaceは""を"　"にし実行してみました。まさしく、希望通りの変換ができあがりました。

一例として教えていただいた方もやってみましたが、こちらだと変化なく（E列が全て空欄のまま）
実行が終わってしまいました。セルの範囲指定を足せばいいのでしょうか？
また"!##aabbc@01^AB|"の意味を教えていただけると幸いです。
お忙しいところお手数ではございますがよろしくお願いいたします。

回答No.2

mu2011

NO1です。
追記です。
セル.Cells(, 5).Value = StrConv(セル.Text, vbWide)
↓
セル.Cells(, 5).Value = StrConv(変換文字, vbWide)

回答No.1

mu2011

コード範囲毎にand条件ではなくOr条件にしないと成立しませんよ。
IfよりSwitch～Caseはお勧めします。

補足

早速のご投稿ありがとうございます。
or条件にしてもやっぱり記号が消えませんでした。
switch~caseは使用したことがないのですが、下記のように書き直してみましたが
（宣言部分省略）やはりうまくいきません。構文の組み立てがおかしいのでしょうか？

Dim セル As Range
Dim TARGET As Range
Dim 変換文字 As String
Dim 半角 As String
Dim i As Long
Dim W As Worksheet

Set W = Sheets("DATA転記")
Set TARGET = W.Range("A3", Range("A65536").End(xlUp))

For Each セル In TARGET

変換文字 = StrConv(セル.Text, vbNarrow)

For i = 1 To Len(変換文字)

Select Case 変換文字

Case Asc(変換文字) >= 32 And Asc(変換文字) <= 47
変換文字 = WorksheetFunction.Replace(変換文字, i, 1, "")
Case Asc(変換文字) >= 58 And Asc(変換文字) <= 64
変換文字 = WorksheetFunction.Replace(変換文字, i, 1, "")
Case Asc(変換文字) >= 91 And Asc(変換文字) <= 96
変換文字 = WorksheetFunction.Replace(変換文字, i, 1, "")
Case Asc(変換文字) >= 123 And Asc(変換文字) <= 126
変換文字 = WorksheetFunction.Replace(変換文字, i, 1, "")

End Select

Next i

セル.Cells(, 5).Value = StrConv(セル.Text, vbWide)

Next セル