ママのスキンケアのお悩みにおすすめアイテム

Excelにて、'C43C410' のようにセルデーターから ' ' 消して、英数字だけにするマクロ(VBA)とデーターを英数字のみを取り出して、別列ヘ転記するマクロ(VBA)を作りたいのですが、どのようにしたらよいのか分からず困っております。また、
お分かりになる方がおりましたら、ご教授願います。

行いたい事
①'C43C410' のようにセルデーターから ' ' 消して、英数字だけにするマクロ(VBA)
②データーの英数字のみを取り出して、別列ヘ転記するマクロ(VBA)
<詳細>
・シート名⇒sheet1
データーは、K列にあり、日によって増減します。
'C43C410' → ' 'を消して、英数字の C43C410 のみ残したい。また、データーを英数字のみを取り出して、別列(L列)ヘ転記するマクロ(VBA)を作りたい。

 A列 B C E F G H IJ    K列     L列
1                 'C43C410'

2                 'EAE908C'

3

※VBAにて、行いたいです。

説明がうまくなくすみませんが、お分かりにある方がおりましたら教えて頂きたいです。

作ってみようと思ったのですが、進みません
Sub sample()
Selection.Replace "'", ""
Worksheets("sheet1").Range("K")
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd




End Sub

質問者からの補足コメント

  • 対応ありがとうございます。

    実際に、実行してみたのですが、動いてくれません
    <行った事>
    ①、②共に標準モジュールに貼り付けて実行したのですが、ダメでした。
    エラーは、出ません
    下記内容で、貼り付けました。

    Sub sample()
    Dim intR As Integer
    With Sheet1
    For intR = 1 To .Range("K1").SpecialCells(xlLastCell).Row
    .Cells(intR, 12).Value = Replace(.Cells(intR, 11).Value, "'", "")
    Next intR
    End With
    End Sub
    作業の仕方が、まずかったでしょうか
    引き続き、ご教授願います。

    No.1の回答に寄せられた補足コメントです。 補足日時:2020/02/05 16:26
  • ①の内容をもう一度やり直してみたところ、動いているのですが、' 'が消えてくれないようです。また、②につても同じく、L列に転記しません。

    忙しい中すみません

      補足日時:2020/02/06 05:03
  • ありがとうございます。
    ④その他
    数式バーには、「'C43C410'」と表示されていますが、セルには「C43C410'」と表示しています。

    ご教授願います。

    No.4の回答に寄せられた補足コメントです。 補足日時:2020/02/06 16:32
  • 対応して頂きありがとうございます。
    >・文字列が一致していない(文字コードが異なる、全角半角など)
    →半角に統一
    >・文字列『'』に関してデータの先頭にある場合には文字として認識されない
    まず一つ目ですが、実際のデータから『'』部分をコピーしてマクロの変換式の部分を置き換えてみて下さい。
    →行ってみましたが、変化なし。

    >次に対象のセルの書式は文字列でしょうか?標準になっているなら文字列にすることをお薦めします。Excelではセルの先頭の文字が『'』の場合には文字とは認識されずに『以降のデータが文字列です』という宣言になります。そのためシート内で検索を行っても検出されません。
    →行ってみましたが、変化なし


    次の補足に続きます

    No.3の回答に寄せられた補足コメントです。 補足日時:2020/02/06 16:38
  • >プログラムの先頭でF9を押してブレイクポイントを指定しておき、マクロを実行した後黄色い背景色が表示されますので、一行ずつ実行して実際の動きを検証してみてはいかがでしょうか。
    →ブレイクポイントをSub sample()にして、実行させたところ、黄色い背景色Next intRと.Cells(intR, 12).Value = Replace(.Cells(intR, 11).Value, "'", "")のところをいったりきたりのくりかえしでセルのデータは変化なしです。マクロが動いていないようです。

      補足日時:2020/02/06 16:48
  • ありがとうございます。
    うまくいきましたが、文字列へしないまま実行すると下のようになります
    文字列にしてから、実行すると英数字のみになります。
      前   実施後
    0126290' 126290    
    0150320' 150320
    01C0020' 01C0020
    0217030' 217030
    0227025' 227025
    02A4017' 02A4017
    0524025' 524025
    745E050' 7.45E+52
    745E050' 7.45E+52
    745E050' 7.45E+52
    745E100' 7.45E+102
    745E100' 7.45E+102
    745E100' 7.45E+102
    745E100' 7.45E+102
    745E100' 7.45E+102
    大変申し訳ないのですが、文字列にする項目を追加することが可能でしょうか。

    No.5の回答に寄せられた補足コメントです。 補足日時:2020/02/06 19:15

A 回答 (9件)

それではこちらはいかがでしょうか?



Sub Sample()

Dim 行 As Long
Dim 幅 As Single

 With Columns("K:K")
  幅 = .ColumnWidth
  .NumberFormatLocal = "@"
  .EntireColumn.AutoFit
  For 行 = 1 To Cells(Rows.Count, 11).End(xlUp).Row
   Cells(行, 11).Value = Replace(Cells(行, 11).Text, "'", "")
  Next
  .ColumnWidth = 幅
 End With
 
End Sub
    • good
    • 0
この回答へのお礼

色々と対応して頂きありがとうございました。
思い通り、英数字のみとなりました。
感謝いたします。

お礼日時:2020/02/06 20:36

セル上では「'」が表示されないが、数式バーでは「'」が頭についても構わなければ


「Cells(行, 11).Value = Replace(Cells(行, 11).Text, "'", "")」を「Cells(行, 11).Value = "'" & Replace(Cells(行, 11).Text, "'", "")」で可能です。
    • good
    • 0

No.5 の補足


先頭の「'」は文字列だというマークとして使われるので代入し直さないと消せません。
肝は「Cells(行, 11).Value = Replace(Cells(行, 11).Text, "'", "")」の「Text」です。これで確実に表示されている状態の文字列に変わります。
「Text」の欠点は値が数値でセル幅が狭いと「##」になってしまうので事前に必ず「.Columns.AutoFit」などでセル幅を広げておかないととんでもない物になる可能性が有るので前後で対応しないといけないことです。
ちなみに行数が数万行以上とかですと配列変数に代入して処理の方が圧倒的に早くなりますが、行数はどの位でしょうか?
「Excelにて、'C43C410' のよ」の回答画像7
    • good
    • 0

こんばんは、横から失礼します。


昨日すでに回答があったのでやめたのですが、、
書式の問題かと思いますので、空セルの書式をコピペしてしまうのが早いと思います。
Copyなので処理時間がかかるかもしれませんが、、
一気にやれば少しは早いかと、、
力技ぽいですが、、、参考まで
Sub Sample1()
Dim rng As Range
  With Sheet1
    For Each rng In .Range("K1:K" & .Cells(Rows.Count, "K").End(xlUp).Row)
      rng.Offset(, 1).Value = Replace(rng.Value, "'", "")
    Next rng
    Range("XX1").Copy ’使われていないセル?
    .Range("L1:L" & .Cells(Rows.Count, "K").End(xlUp).Row).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    .Range("L1").Select
  End With
End Sub
    • good
    • 1
この回答へのお礼

ありがとうございます。
今度、勉強致します。

お礼日時:2020/02/06 20:37

それでは以下ではいかがですか?



Sub Sample()

Dim 行 As Long
Dim 幅 As Single

 幅 = Columns("K:K").ColumnWidth ' セル幅が狭いと「#####」のようになってしまうのを防止処理
 Columns("K:K").EntireColumn.AutoFit ' セル幅が狭いと「#####」のようになってしまうのを防止処理
 For 行 = 1 To Cells(Rows.Count, 11).End(xlUp).Row
  Cells(行, 11).Value = Replace(Cells(行, 11).Text, "'", "")
 Next
 Columns("K:K").ColumnWidth = 幅 ' セル幅が狭いと「#####」のようになってしまうのを防止処理
 
End Sub
この回答への補足あり
    • good
    • 0

念のために確認させてください。


K1セルを選択した状態で、数式バーにはなんと表示されていますか?次の内から選んでください。
①「''C43C410'」(「'」が頭に2つある)
②「 'C43C410'」(半角の空欄が頭にある)
③「'C43C410'」(セルに表示されている物と同じ)
④ その他(具体的に説明して下さい)
この回答への補足あり
    • good
    • 0

No1です


気になった点が少し...
当方で検証した際には①②共に正常に動きましたので、問題はそれ以外の部分にあると考えます。パッと思いつくところで
・文字列が一致していない(文字コードが異なる、全角半角など)
・文字列『'』に関してデータの先頭にある場合には文字として認識されない
まず一つ目ですが、実際のデータから『'』部分をコピーしてマクロの変換式の部分を置き換えてみて下さい。
次に対象のセルの書式は文字列でしょうか?標準になっているなら文字列にすることをお薦めします。Excelではセルの先頭の文字が『'』の場合には文字とは認識されずに『以降のデータが文字列です』という宣言になります。そのためシート内で検索を行っても検出されません。
そのため恐らくですが、対象のデータはCSVか何かをxlsxとして開いているのではと想定します。
プログラムの先頭でF9を押してブレイクポイントを指定しておき、マクロを実行した後黄色い背景色が表示されますので、一行ずつ実行して実際の動きを検証してみてはいかがでしょうか。その際には対象のセルが正確に指定されているかを見るために、シートとコードを画面に並べるように配置して、対象セルに対してSelectを指定して動きを見るのが好ましいと思います。一行ずつ実行するのはF8で進みます。(これをデバッグと言います:ご存知ならスルーしてください(笑))
この回答への補足あり
    • good
    • 0
この回答へのお礼

ありがとうございました。
忙しい中対応して頂き感謝いたします
これから色々勉強していきたいと思います。

お礼日時:2020/02/06 20:41

添付図参照



マクロに固執しなければ関数でも行けそうですが・・・
B1: =SUBSTITUTE(A1,"'","")
「Excelにて、'C43C410' のよ」の回答画像2
    • good
    • 0
この回答へのお礼

ありがとうございました。
勉強になりました。

お礼日時:2020/02/06 20:43

気になったのはまず記載のコードの1行目です


Selection.Replace "'", ""とありますが
選択されていませんよね
次にK列のレンジ指定がありますが、こちらも選択されておらず
さらにその下のPasteSpecialが記載されていますがこちらも
Selection.PasteSpecial~となっており何も選択されないまま処理を行おうとしています

簡単にシート全体の『'(アポストロフィ,シングルクォーテーション)』を消したいのであれば
Cells.Replace What:="'", Replacement:=""
この様に簡単にできます

ただご要望①はK列のみ表示を変えるということなので例えば
 Dim intR As Integer
 With Sheet1
  For intR = 1 To .Range("K1").SpecialCells(xlLastCell).Row
   .Cells(intR, 11).Replace What:="'", Replacement:=""
  Next intR
 End With
といった形でK列を上から順番に判定していって変換する方法などいかがでしょうか
同様に②は
 Dim intR As Integer
 With Sheet1
  For intR = 1 To .Range("K1").SpecialCells(xlLastCell).Row
   .Cells(intR, 12).Value = Replace(.Cells(intR, 11).Value, "'", "")
  Next intR
 End With
これでいかがでしょう

ちなみにVBAはあまり詳しくないようなので補足すると、Rangeの場合には"K1"のような指定が出来ますが、ループ処理を行うのには適していません。そのためCellsを使用しています。こちらのパラメータ(カッコ内)は最初が縦座標で後ろが横座標となっています。なので11がK列で12がL列となります。
また、SpecialCells(xlLastCell)はデータの終端の座標を自動的に取ってきてくれるもので、その列までループさせているということになります

ご参考までに
この回答への補足あり
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング