Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
処理列の追加
投稿日時: 18/10/18 11:30:19
投稿者: hikari12101210

VBA初心者です。度々のご相談失礼いたします。
sheet1の「P6〜P100」にある値を改行位置で分割し、sheet2にコピーをしたいです。(分割した値は横並び)
以下のコードでやりたいことは実現できたのですが、
分割する列を追加したい場合、どのように書き換えれば良いでしょうか?
-------------------------------------------------------------------------
Sub 分割()
    Dim i As Long, j As Integer, x, data, tbl
    Sheets("sheet1").Range("P6:P100").Copy Sheets("sheet2").Range("c2")
    tbl = Sheets("sheet2").Range("c2:c100") 'sheet2のc2からc100まで変数tblに格納
    ReDim x(1 To UBound(tbl, 1), 1 To 30)
    For i = 1 To UBound(tbl, 1)
        On Error Resume Next
        data = Split(tbl(i, 1), Chr(10), -1)
        m = data(1)
        If Err.Number = 29 Then
            x(i, 1) = Empty
        Else
            For j = 0 To UBound(data)
                x(i, j + 1) = data(j)
            Next j
        End If
    Next i
    Sheets("sheet2").Cells(2, 2).Resize(UBound(tbl, 1), UBound(x, 2)) = x
    On Error GoTo 0
 
End Sub
-------------------------------------------------------------------------
 
■追加したい処理
sheet1の「Q6〜Q100」にある値を改行位置で分割し、「sheet3」にコピー

回答
投稿日時: 18/10/18 15:41:56
投稿者: WinArrow
投稿者のウェブサイトに移動

>■追加したい処理
>sheet1の「Q6〜Q100」にある値を改行位置で分割し、「sheet3」にコピー
 
方法1
現在のプロシジャを複写し、プロシジャ名を変更
"P6〜P100"を"Q6〜Q100"に変更
Sheet2をSheet3変更
 
方法2
現在のプロシジャの中身の2行目から、End SUBの前までを
End SUBの行に前に複写
"P6〜P100"を"Q6〜Q100"に変更
Sheet2をSheet3変更
 
 
疑問点@
Sheet1の[P6〜P100]をSheet2の[C2]に複写すると[C2〜C96]になりませんか?
 
疑問点A
分割後のデータをSheet2のセルB2に代入していますが、意図は?
 
 
 
代案
For ループでセルの値を分割、格納するよりは、
「区切り位置」というコマンドを使うと、コードもすっきり、処理も早くなります。
 
例示
 Sheet2に複写後
    Sheets("Sheet2").Range("C2:C100").TextToColumns _
        Destination:=Sheets("Sheet2").Range("E2"), _
        DataType:=xlDelimited, _
        Other:=True, _
        OtherChar:="" & Chr(10) & ""
 

回答
投稿日時: 18/10/18 15:58:34
投稿者: WinArrow
投稿者のウェブサイトに移動

注意事項
 
>On Error Resume Next
 
エラートラップは、デバッグ〜テストが終了してから、つけた方がよいです。
最初からエラートラップを付けておくと、本当につぶさなければいけないエラーを
見逃してしまいます。
 
今回の場合、改行コードが存在しないセルがあった場合のエラー回避と思われますが、
強制的に改行コードを付加することで、エラートラップなしにできます。
 
>data = Split(tbl(i, 1), Chr(10), -1)

data = Split(tbl(i, 1) & Chr(10), Chr(10), -1)
 
最後の引数「-1」は、省略可です。

投稿日時: 18/10/22 17:58:41
投稿者: hikari12101210

早々にアドバイスをいただいたにも関わらず、御礼が遅くなり誠に申し訳ございません。
お教えいただいた方法Aで希望通りの処理ができました。
また、エラートラップの部分も書きかえました。
 

引用:
疑問点@
Sheet1の[P6〜P100]をSheet2の[C2]に複写すると[C2〜C96]になりませんか?

→いえ、B2〜B96に表示されます。ご質問の意図と違ったらすみません、、
 
引用:
疑問点A
分割後のデータをSheet2のセルB2に代入していますが、意図は?

→分割した横並びのデータを更に縦並びに変更する処理をしており
 わかりやすいようにシートを分けております。
 
■やりたい事
マスターシートから必要なデータ2つのみ、フォーマットに落としこみをしたいと考えております。
マスターシートのデータで、一つのセルに複数行ある場合は改行位置でセルを分けたいです。
※セル内の行数は可変で、以下3つの処理で対応しようと思っております。
 
@データの分割(改行位置で)
Aデータの並び替え(@で横並びにしたものを縦に並び替え)
Bフォーマットの行追加(分割した数にあわせてフォーマットの行を追加)
 
非常にわかりにくご説明となり恐縮ですが、全体の意図としてはこちらになります。

回答
投稿日時: 18/10/22 22:36:12
投稿者: WinArrow
投稿者のウェブサイトに移動

hikari12101210 さんの引用:

 
引用:
疑問点@
Sheet1の[P6〜P100]をSheet2の[C2]に複写すると[C2〜C96]になりませんか?

→いえ、B2〜B96に表示されます。ご質問の意図と違ったらすみません、、

↓のコードで「Range("c2:c100")」って書いてありますよね?
> tbl = Sheets("sheet2").Range("c2:c100") 'sheet2のc2からc100まで変数tblに格納
回答は、B2〜
>B96に表示されます。
これって、何?
 
hikari12101210 さんの引用:
 
引用:
疑問点A
分割後のデータをSheet2のセルB2に代入していますが、意図は?

→分割した横並びのデータを更に縦並びに変更する処理をしており
 わかりやすいようにシートを分けております。

疑問@にも関係するが、
tblには、C列のセル範囲を格納しているが、
tblからセルに書き出すときは、B列でよいのか?
ってことです。
 
 
やりたいこと
この説明は、最初の質問よりわかりにくいです。

トピックに返信