Val関数をVBAで使うには？

質問

助けてください　VBA初心者です

Excel VBAでA列の4からj行目までの文字列（例："12345678” 8桁固定）、k行目までの数字以外の文字列（例：AB12345678　9桁以上ある）のうち、j行目までの文字列をVal関数で数値に変換したいのですが、どうやっていいのか分かりません　j+1行目からk行目までは数字以外の文字列が入っています

1セルだけの変換はできます（例：A4セルに"12345678”がある場合、Val(range("A4"))で12345678が出ます)

※仮にj＝98と固定しました

Sub Value_num()
J = 98
'A列を値→数字に変換
Columns("A").Select
'Range("A4").Value = val(Range("A4")) これは動く
Range(Cells(4, 1), Cells(J , 1)).Value = val(Range(Cells(4, 1), Cells(J, 1))) 'これが動かない

End Sub

回答No.4

MarcoRossiItaly · ベストアンサー

No.2 です。No.3さんは NumberFormatLocal プロパティにおいて、セルの書式を「G/標準」に設定していらっしゃいます。もしも、これさえも必要ないという場合は、コードは更に短く書けますね。

Sub Value_num()
Range("A:A").Value = Range("A:A").Value
End Sub

お礼

回答ありがとうございます
このコードでも無事変換できました！
一番この方法が簡単なので、これで行きたいと思います
本当にありがとうございました

回答No.5

DOUGLAS_

[Val 関数] は、文字列中に数字以外の文字が見つかると、読み込みを中止します。

　つまり、
Val("12345678AB") = 12345678
ですが、
Val("AB12345678") = 0
ということになります。

　数字の前の文字列も消してしまって、セル内を数字のみにする、つまり「"AB1234CD5678EF"」を「12345678」に変えたいというような場合は、正規表現で、数字以外を消してしまうのがよいかと存じます。

Sub Value_num()
　　Dim objRE As Object
　　Dim j As Integer
　　Set objRE = CreateObject("VBScript.RegExp")
　　With objRE
　　　　.Global = True
　　　　.Pattern = "[^0-9]"
　　　　For j = 4 To 98
　　　　　　Range("A" & j).Value = .Replace(Range("A" & j).Value, "")
　　　　　　Next
　　End With
End Sub

　ちなみに、ｊ行目までの文字列には「数字以外」は含まれない、つまり、ｊ行目までの文字列は「数字のみ」でできている、ということでしたら、ＶＢＡを使わなくても
１）ワークシート上の何も入力されていないセルを選択し、コピー（[Ctrl] + [C]）
２）Ａ列のｊ行目までを選択
３）[編集(E)] - [形式を選択して貼り付け(S)...] - [加算(D)] - [OK]
で、一発で数値化できます。

お礼

回答ありがとうございます
数値12345678のみがJ列まであり、数値を含むAB12345678がK列まであるという場合で、文字列になっている数値のみJ列まで数値に変換したいという希望でした（J+1からK列までは文字列のまま）

EXCEL上で変換するのは確認しました　こんな方法もあるんですね
でもVBAでどうしてもやりたかったので、回答No.4のMarcoRossiItalyさんの回答をベストアンサーとしました

回答No.3

tom04

こんにちは！

Val関数を使用する場合
仮にあるセル（A4セル）に　1201AB　というデータがあるとすると
Val(Range("A4"))
とすれば
1201
という値を返しますが、
今回の場合4～ｊ行目まではすべて8桁固定の数値で、
それ以降はアルファベットを含んだ数値だというコトですので、

とありますので、
単純に

Sub Sample1()
With Range("A:A")
.NumberFormatLocal = "G/標準"
.Value = .Value
End With
End Sub

ではダメですか？m(_ _)m

お礼

回答ありがとうございます　このコードで無事変換できました！
NumberFormatLocalって初めて見たので、調べました
便利なプロパティがあるのですね

回答No.2

MarcoRossiItaly

No.1 です。「No.1 のコードは使わないでください」。申し訳ありません、ミスしました。

A 列には数値の他に、文字列のセルも含まれるのでしたね。No.1 のコードでは、文字列をゼロで上書きしてしまいます。If の行だけ次のとおり修正しました。失礼しました。

Option Explicit

Sub Value_num()

Dim J As Integer

For J = 4 To 98
　If Val(Cells(J, 1)) > 0 Then
　Cells(J, 1).Value = Val(Cells(J, 1))
　End If
Next J

End Sub

または、If の行は No.1 のままにしておいても、その 2 行下の「Val(Cells(J, 1))」を「Cells(J, 1).Value」に書き換え、Val を使わなければ、文字列をゼロで上書きしません。お手元のシートの状況がこちらの想定どおりなら、それでも数値文字列を数値にしてくれると思います。

参考 URL は、ただの No.1 の再掲（繰り返しの構文）です。

参考URL：

http://www.eurus.dti.ne.jp/yoneyama/Excel/vba/vba_for_next.html

お礼

このコードで無事変換できました！　ありがとうございます
あとはJが98固定ではなくて、J>4なので、
98より大きめの数値をいれてFor～Next文を回してみたいと思います

回答No.1

MarcoRossiItaly

「Val(string)」という書式なのに、Range で複数セルを入れてしまっているからでは。

J の値を動かして、逐次、セルに値を入れていけば。繰り返しのステートメントは好きなの選べばいいでしょうけど、例えば For ～ Next。参考 URL などご覧ください。

あと、できれば Dim も入れときましょう。As Integer が難しいという場合は、Dim J のみでもいいです。

Option Explicit

Sub Value_num()

Dim J As Integer

For J = 4 To 98
　If Cells(J, 1) <> "" Then
　Cells(J, 1).Value = Val(Cells(J, 1))
　End If
Next J

End Sub

参考URL：

http://www.eurus.dti.ne.jp/yoneyama/Excel/vba/vba_for_next.html

お礼

回答ありがとうございます　分かりやすいFor～Next例文も参考になりました　ただこのコードは使用しないで下さいとあるので、実行してません