VBA

質問

以下の構文では、理想通りに実行できません。10行目までは大丈夫なのですが、そのあとは何が悪いのかわかりません。
on error resume nextを省略するとオブジェクトが必要ですとエラーメッセージが表示されます。
回答宜しくお願い致します。

sub macro1()
　dim a as variant
　dim h as range
　dim r as long
　dim w0 as worksheet

　set w0 = activesheet
　worksheets.add after:=w0
　r = 1
　range("A1:G1") = array("苗字","名前","住所","TEL","〒","好きなスポーツ","性別")

　on error resume next
　for each h in w.range("A1:A" & w.range("A65536").end(xlup).row)
　　if h <> "" then
　　　a = split(application.trim(replace(replace(h, "　"," "),"：",":")), " ")
　　　r = r + 1
　　　cells(r, "A") = split(a(0), ":")(1)
　　　cells(r, "B") = split(a(1), ":")(1)
　　　cells(r, "C") = split(a(2), ":")(1)
　　　cells(r, "D") = split(a(3), ":")(1)
　　　cells(r, "E") = split(a(4), ":")(1)
　　　cells(r, "F") = a(5)
　　　cells(r, "G") = a(6)
　　end if
　next
end sub

回答No.2

chie65536（@chie65535） · ベストアンサー

以下のようにしましょう。

Sub macro1()
Dim a As Variant
Dim h As Range
Dim r As Long
Dim w As Worksheet '追加
Dim w0 As Worksheet

'w0はマクロ呼び出し時のアクティブシート。元データがあるシート
Set w0 = ActiveSheet
Worksheets.Add after:=w0
'wはデータを作成する、新規作成したシート
Set w = ActiveSheet
r = 1

'見出しは、新規作成した方のシートに書き込む
w.Range("A1:G1") = Array("苗字", "名前", "住所", "TEL", "〒", "好きなスポーツ", "性別")

On Error Resume Next

'元データは、呼び出し時のアクティブシート、つまりw0のシートから参照する
For Each h In w0.Range("A1:A" & w0.Range("A65536").End(xlUp).Row)
If h <> "" Then
a = Split(Application.Trim(Replace(Replace(h, "　", " "), "：", ":")), " ")
r = r + 1
'結果は新規作成した方のシート、つまりwのシートに書き込む
w.Cells(r, "A") = Split(a(0), ":")(1)
w.Cells(r, "B") = Split(a(1), ":")(1)
w.Cells(r, "C") = Split(a(2), ":")(1)
w.Cells(r, "D") = Split(a(3), ":")(1)
w.Cells(r, "E") = Split(a(4), ":")(1)
w.Cells(r, "F") = a(5)
w.Cells(r, "G") = a(6)
End If
Next
End Sub

お礼

大変助かりました。
ありがとうございますm(_ _)m
また何かありましたら宜しくお願い致します。

回答No.5

chie65536（@chie65535）

＞このデータベースを基に、追加したシートのA1からG1に表題を作り、それぞれ分割した文字列を入力するというVBAを希望しております。

では、当方が「回答No.2」で示したVBAプログラムをそのまま使用して下さい。

貴方が想定している通りのデータで、貴方の想定した通りに動作します。

回答No.4

chie65536（@chie65535）

追記。

当方で修正したマクロを実行すると、添付画像のようになる筈です。

これで問題がある場合は、補足説明で補足して下さい。

補足

早速の回答ありがとうございます。
文字列のデータはA1セルから複数行
名字:山田 名前:太郎 住所:北海道 TEL: 011-##6-3#36 〒:0##-0##5 野球 男
という感じに個人情報が入力されています。
野球と男の前には、空白しかありません。

このデータベースを基に、追加したシートのA1からG1に表題を作り、それぞれ分割した文字列を入力するというVBAを希望しております。
宜しくお願い致します。

回答No.3

chie65536（@chie65535）

追記。

＞何が悪いのかわかりません。

悪いのは「質問者さんが、どこのシートにあるデータを読んで、どこのシートに書き込むのかを、まったく理解してない」と言う点です。

回答No.1

dogs_cats

データがどのような　文字列であるか不明なので回答のしようがありません。
ダミーデータ提示下さい。
"苗字","名前","住所","TEL","〒","好きなスポーツ","性別"のデータがどのように区切りを入れて入力されているのか。

気づいた点は

for each h in w.range("A1:A" & w.range("A65536").end(xlup).row)はA2から始めるべきでは？
よって、rは2から開始
r=r+1はend　ifの前

補足

早速の回答ありがとうございます。
文字列のデータはA1セルから複数行
名字:山田 名前:太郎 住所:北海道 TEL: 011-256-3336 〒:056-0695 野球 男
という感じに個人情報が入力されています。
このデータベースを基に、追加したシートのA1からG1に表題を作り、それぞれ分割した文字列を入力するというVBAを希望しております。
宜しくお願い致します。