[[20161201115133]] 『複数シートある結合されているセルの値を別シート』(たてもとか) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『複数シートある結合されているセルの値を別シートに書式を指定して転記』(たてもとか)

結合されて数値も文字列になってしまっているため計算ができないシートが複数あります。これを計算ができる状態にしたいのです。

▼元データ 1シートA4横で、縦に2分割した形でデータがあります。
列の○-○は結合されています。

   1-4列結合 5-15 16-20 21-30 |31-34 35-45 46-50 51-60
11行目 品番    品名 個数  単価 |品番  品名 個数   単価
12行目 00325   品A  999  99999 |02689  品J  99   9999
13行目 52664   品D  99  9999  |15263  品B  999   9999
・・・略
48行目まで

シートはすべて同じフォーマットなので、どのシートからも12行目から48行目までの範囲を取得したいのですが、左右にデータがわかれているので、縦につなげて転記したいのです。

全部文字列になってしまっているので、個数と単価は計算できるよう標準または数値化で転記したいのです。品番は文字列でないと0が消えてしまうので。

▼別シートにこう転記したい

1列目 2列目 3列目 4列目
00325   品A  999   99999   ←1シート目の左側
52664   品D  99  9999
・・・略
02689  品J  99   9999   ←1シート目の右側
15263  品B  999   9999
・・・略
65942   品L  999   9999   ←2シート目の左側

最期のシートが終わるまで縦長に。

書式を指定する方法がややこしければ文字列のままでも構いません。そのあとデータ区切りで数値化します。

そこから先の仕事がメインなんですけどこの転記の手作業に時間がかかってしまっていまして、調べてもみたんですけど過去ログから加工する知恵もなくて質問させていただきました。よろしくお願いいたします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


Sub main()
    Dim rg As Range, i As Long, j As Long, k As Long
    Worksheets.Add before:=Worksheets(1)
    Worksheets(1).Name = "別シート"
    Set rg = Worksheets(1).Range("A1")
    For k = 2 To Worksheets.Count
        For j = 1 To 31 Step 30
            For i = 12 To 48
                rg.Value = Worksheets(k).Cells(i, j).Value
                rg.Offset(, 1).Value = Worksheets(k).Cells(i, j + 4).Value
                rg.Offset(, 2).Value = Worksheets(k).Cells(i, j + 15).Value
                rg.Offset(, 3).Value = Worksheets(k).Cells(i, j + 20).Value
                Set rg = rg.Offset(1)
            Next i
        Next j
    Next k
End Sub
(mm) 2016/12/01(木) 14:49

すごいです!!!!ありがとうございます。

ですが、
>品番は文字列でないと0が消えてしまうので。

文字列ではなく標準?で貼りついてしまうので0が消えてしまいます。文字列ではり付けるにはどのようにしたらよいでしょうか。

(たてもとか) 2016/12/01(木) 15:58


rg.Value = Worksheets(k).Cells(i, j).Value
を、
rg.Value = Worksheets(k).Cells(i, j).text
または
rg.Value = format(Worksheets(k).Cells(i, j).value,"00000")
あたりでどうですか?
(mm) 2016/12/01(木) 16:07

rg.NumberFormatLocal = "@"
rg.Value = Worksheets(k).Cells(i, j).Value
ですか

(mm) 2016/12/01(木) 16:17


ありがとうございます。

rg.NumberFormatLocal = "@"
rg.Value = Worksheets(k).Cells(i, j).Value

で成功しました(^^)これで先に進めます。本当に助かりました。

実は、「文字列で取得する」というキーワードで調べたら.textが出てきたので、やってみたけどだめだったので質問した次第です。
rg.Value = Worksheets(k).Cells(i, j).text
rg.Value = format(Worksheets(k).Cells(i, j).value,"00000")
は通常はできるのでしょうか。だめだったということは私のexcelの設定?か何か足りないものがあるのでしょうか。
(たてもとか) 2016/12/01(木) 17:58


 >だめだったということは私のexcelの設定?か何か足りないものがあるのでしょうか。

 セルの書式を標準のままにして、データを放り込むと言う行為は、
 エクセルにどんなデータ型で処理するか「任せる」ことを意味します。

 ・・で、「数字」が入って来たので、エクセルは「数値型」で処理するのが適切であろうと判断した訳です。

 勝手に変えられたら困ると思うなら「お任せ」にしないことです。
 つまり、受け皿のセルの書式設定(型指定)が足りない、と言う結論になります。

(半平太) 2016/12/01(木) 19:28


>・・で、「数字」が入って来たので、エクセルは「数値型」で処理するのが適切であろうと判断した訳です。
ということになるだろうなと思っていたので、だから質問ではあえて「書式を指定して転記」という表現をし、「品番は0が消えないように文字列で」セットしたいことを記載しました。

文字列でセットする方法について再度ご回答いただけたので、やってみたら反応しなかったものがあったのでなぜなのかなと お聞きしたかっただけなんです。
(たてもとか) 2016/12/02(金) 14:26


 >文字列でセットする方法について再度ご回答いただけたので、
 >やってみたら反応しなかったものがあったのでなぜなのかなと 
 >お聞きしたかっただけなんです。

 それは大変失礼いたしました。

 再現できるデータとコードをアップして頂けませんか?

 ちなみに、下の例は必要な設定をしている例とは言えないです。
 右辺のデータだけが文字列であり、左辺のfgの書式が何かは不明なので。

 >rg.Value = Worksheets(k).Cells(i, j).text 
 >rg.Value = format(Worksheets(k).Cells(i, j).value,"00000") 

(半平太) 2016/12/02(金) 15:50


>>・・で、「数字」が入って来たので、エクセルは「数値型」で処理するのが適切であろうと判断した訳です。
>ということになるだろうなと思っていたので、だから質問ではあえて「書式を指定して転記」という表現をし、
>「品番は0が消えないように文字列で」セットしたいことを記載しました。

エクセルの場合、数値に変換可能な文字列は、おせっかいを焼いて数値にしてくれます。
(数式と読めれば数式として受け付けます。)
それをあえて、文字列であると明示するには(手動でも同じです)、
文字列の頭に「'」シングルクォーテーションを付けてやります。

>rg.Value = Worksheets(k).Cells(i, j).text
rg.Value = "'" & Worksheets(k).Cells(i, j).text

>rg.Value = format(Worksheets(k).Cells(i, j).value,"00000")
rg.Value = "'" & format(Worksheets(k).Cells(i, j).value,"00000")

事前にセルの書式設定を「文字列」にしておく方法もありますが、
シート上で編集している間に油断してると簡単に変わってしまうので、
シングルクォーテーションを付けるのも一案かと思います。
(それはそれで不都合な場合がまた出てくるかも知れませんが。。。)

(まっつわん) 2016/12/02(金) 17:49


半平太さん まっつわん おかげでなぜできなかったのかがわかりまして、とてもすっきりしました。ありがとうございます。
まっつわんさんのいうとおり「'」がつくことで そのあとaccessに吸い上げて処理しようとしていたのですがnullの認識がうまくいきませんでした。

excelだけで済ませるのならこの方法で問題の無いこともわかりました。

最初に教えてくださった(mm)さん、半平太さん、まっつわん ご親切にありがとうございました。

(たてもとか) 2016/12/07(水) 15:49


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.