Excel 2007 マクロ 文字列の抽出について

質問

Excel 2007 マクロ 文字列の抽出について

元データのB列（番号）に5桁、3桁、7桁の数字が入っています。
その番号をSheet2にコピーして貼り付けます。
貼り付ける際に、5桁、3桁、7桁をそれぞれの列に入れます。

Sheet2のB列（番号2）で400番台はF列（種別）に国産
同じくB列（番号2）で900番台はF列（種別）に外国産
を反映するようにしたいです。

どのようなマクロになりますでしょうか。
サンプルがございましたら、お教えください。

回答No.3

keithin · ベストアンサー

以下のマクロの書きぶり全体で理解してください。
for each nextやwith end withについて理解していないと，offsetだけを見ていても理解は進みません。

for each h in worksheets("Sheet1").range("B2:B" & r)
　　with worksheets("Sheet2").range("A65536").end(xlup).offset(1)

　　.offset(0, 3) = h.offset(0, -1)
　　.offset(0, 4) = h.offset(0, 1)

　　end with
next

ご質問の直接の回答として
＞offset(行, 列)

Offsetを使う練習：
range("A1").offset(2,3) = 123
A1セルの，２列右で，３列下のセルに，123という値を記入する

復習：
　セル.offset(0, 3)　セルの３列右で同じ行のセル
　セル.offset(0, 4)　セルの４列右のセル
　セル.offset(0, -1)　セルの１列左のセル
　セル.offset(0, 1)　セルの１列右のセル
　セル.offset(1)　　セルの１行下のセル

あるセル.offset(0, 3) = 別のセル.offset(0, -1)
＝＞あるセルの３列右隣のセルに，別のセルの１列左隣のセルの値を入れる

あるセル.offset(0,4) = 別のセル.offset(0, 1)
＝＞あるセルの４列右隣のセルに，別のセルの１列右隣のセルの値を入れる

お礼

ご回答ありがとうございました。Offsetについて詳細に記載頂きまして非常に感謝しております。Offsetだけを見ていたため理解できていませんでした。どのセルを基点にして考えるのかが抜けていました。

回答No.2

keithin

sub macro1()
　dim h as range
　dim r as long

　r = worksheets("Sheet1").range("B65536").end(xlup).row

　for each h in worksheets("Sheet1").range("B2:B" & r)
　　with worksheets("Sheet2").range("A65536").end(xlup).offset(1)
　　.resize(1, 3) = split(h, "-")
　　.offset(0, 3) = h.offset(0, -1)
　　.offset(0, 4) = h.offset(0, 1)
　　.offset(0, 5).formular1c1 = "=IF(INT(RC2/100)=4,""国産"",IF(INT(RC2/100)=9,""外国産"",""""))"
　　end with
　next
end sub

＃ヤリタイ事のご説明と参照図に若干ミスマッチがあります
＃実際のデータの詳細が不明なので，アタマゼロなども保全するように番号を文字列で展開しています
＃マクロのご質問でも実は関数の方が良かったんですとかあとで言われる方も多いですね。

補足

ご回答ありがとうございました。
情報が足りないところがあり、大変申し訳ございませんでした。
希望したマクロの内容でございました。

Offsetプロパティについて、調べました。
ご紹介頂きましたOffsetプロパティの使い方が、Webサイトで
調べたのですが似通った情報がなく理解ができませんでした。

Offsetプロパティは「Rangeなどで指定した範囲からOffset(行, 列)で指定した先の部分を操作対象とする」
というプロパティであることは分かりました。

大変申し訳ありません。下記2つの箇所について
どのような動きをしているのかお教え頂けませんでしょうか。

.Offset(0, 3) = h.Offset(0, -1)
.Offset(0, 4) = h.Offset(0, 1)

回答No.1

tom04

こんばんは！
VBAでないので参考にならなかったら無視してください。

関数による一例です。
データの「区切り位置」を使えば簡単に出来ると思いますが、
敢えて他の方法です。

↓の画像でSheet2のA2セルに
=IF(Sheet1!B2="","",LEFT(Sheet1!B2,5))
B2セルに
=IF(A2="","",MID(Sheet1!B2,FIND("-",Sheet1!B2,1)+1,3))
C2セルに
=IF(A2="","",RIGHT(Sheet1!B2,7))
D2セルに
=IF(A2="","",Sheet1!A2)
E2セルに
=IF(A2="","",Sheet1!C2)
F2セルに
=IF(A2="","",IF(INT(B2/100)=4,"国産","外国産"))

という数式を入れ、A2～F2セルを範囲指定し、F2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、参考になれば良いのですが
最初に書いたように希望の方法と違った場合は
読み流してくださいね。m(__)m

お礼

ご回答ありがとうございます。マクロ以外での解法も知りたいと思っていました。幅広く勉強したいと思います。