[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『別ファイルに値を入れる』（ぺんの助）
はじめまして。どなたか教えて頂けませんか。
毎月出来上がるファイルＡに対し、その内容を本社から送られてくるファイルＢに転記・別ファイルに保存し本社に送り返す作業を現在手作業にて行っております。
目視にて作業を行っている為、入力場所や数量の入力間違いが多発して困っております。
やりたい事としては、ファイルＡに「実行ボタン」を作成し、実行する事でファイルＢに値が入り別ファイルに保存される流れにしたいのですがＶＢＡでプログラミングは可能でしょうか。
是非ご指導をお願いします。
ファイルＡ
※商品コードと数量が並んでいます。（同じ商品コードが複数存在します。）
A B
1 ｺｰﾄﾞ 数量
2 1001 5
3 1002 1
4 1003 3
5 2001 2
6 2002 4
7 2003 5
8 3001 1
9 3002 4
10 3003 5
11 1002 6
12 2002 7
13 3003 1
14
ファイルＢ
下記のような表があります。
※数量を入力するセル以外は保護がかけられています。（コードのセルに関数が入っているみたいです）
※数量を入力するセルには条件付書式が設定されています。（数値が入るとセルが網掛けになります。）
※商品コードの位置が変わる可能性があります。
A B C D E F G H I J
1
2 ｺｰﾄﾞ 数量 ｺｰﾄﾞ 数量 ｺｰﾄﾞ 数量 ｺｰﾄﾞ 数量 ｺｰﾄﾞ 数量
3 1001 2005 3004 5014 6037
4 1002 2006 3005 5015 6038
5 1003 2007 3006 5016 6039
6 1004 2008 3007 5017 6040
7 1005 2009 3008 5018 6041
8 1006 2010 3009 5019 6042
9 1007 2011 3010 5020 6043
10 1008 2012 3011 5021 6044
:
:
:
60 2001 3000 5010 6033 7022
61 2002 3001 5011 6034 7033
62 2003 3002 5012 6035 7034
63 2004 3003 5013 6036 7035
結果
A B C D E F G H I J
1
2 ｺｰﾄﾞ 数量 ｺｰﾄﾞ 数量 ｺｰﾄﾞ 数量 ｺｰﾄﾞ 数量 ｺｰﾄﾞ 数量
3 1001 5 2005 3004 5014 6037
4 1002 7 2006 3005 5015 6038
5 1003 3 2007 3006 5016 6039
6 1004 2008 3007 5017 6040
7 1005 2009 3008 5018 6041
8 1006 2010 3009 5019 6042
9 1007 2011 3010 5020 6043
10 1008 2012 3011 5021 6044
:
:
:
60 2001 2 3000 5010 6033 7022
61 2002 11 3001 2 5011 6034 7033
62 2003 5 3002 4 5012 6035 7034
63 2004 3003 5 5013 6036 7035
こんにちは。かみちゃん です。
> ファイルＡに「実行ボタン」を作成し、実行する事でファイルＢに値が入り
3001 の集計結果が 2、3003 の集計結果が 5 になぜなるのか、わかりませんが、 （3001 の集計結果が 1、3003 の集計結果が 6 ではないのでしょうか） ファイルＡがSheet1 ファイルＢがSheet2 とした場合、以下のような感じでできると思います。
Sub Sample() Dim WS1 As Worksheet Dim WS2 As Worksheet Dim vntData As Variant ' Dim dic As Scripting.Dictionary Dim dic As Object Dim lngRow As Long Dim lngColumn As Long
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
vntData = WS1.Range("A1").CurrentRegion.Value
' Set dic = New Scripting.Dictionary
Set dic = CreateObject("Scripting.Dictionary")
For lngRow = 2 To UBound(vntData, 1)
If dic.Exists(vntData(lngRow, 1)) Then
dic(vntData(lngRow, 1)) = dic(vntData(lngRow, 1)) + vntData(lngRow, 2)
Else
dic.Add vntData(lngRow, 1), vntData(lngRow, 2)
End If
Next
With WS2.Range("A2").CurrentRegion
vntData = .Value
For lngColumn = 1 To UBound(vntData, 2) Step 2
For lngRow = 2 To UBound(vntData, 1)
vntData(lngRow, lngColumn + 1) = dic(vntData(lngRow, lngColumn))
Next
Next
.Value = vntData
End With
Set dic = Nothing
MsgBox "集計しました" End Sub
集計結果が確認できれば、
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
の部分を
Set WS1 = Workbooks("ファイルＡ.xls").Worksheets("Sheet1")
Set WS2 = Workbooks("ファイルＢ.xls").Worksheets("Sheet1")
などとすれば、いいです。 この場合、ファイルＡ.xls、ファイルＢ.xls は、開いておく必要があります。
今回、Dictionary を使っていますが、もちろん、二次元配列を使った集計でもできると思います。
(かみちゃん) 2009/08/23 14:29
単純に
Sub test()
Dim i As Long
With Workbooks("ファイルＢ.xls")
With .Sheets("SheetNameHere").Range("a2").CurrentRegion
With .Resize(.Rows.Count - 1).Offset(1)
For i = 2 To .Columns.Count Step 2
With .Columns(i)
.Formula = _
"=sumif('[ファイルＡ.xls]Sheet1'!c1:c12,rc[-1],'[ファイルＡ.xls]Sheet1'!c2:c2)"
.Value = Evaluate("if(" & .Address(external:=True) & "=0,""""," & _
.Address(external:=true) & ")")
End With
Next
End With
End With
.SaveAs Replace(.FullName, ".xls", "Updated.xls")
End With
End Sub
- SheetNameHereを該当シート名に変更(ファイルB) - 数式の Sheet1を該当シート名に変更(ファイルA) (seiya)
＞※数量を入力するセル以外は保護がかけられています。 この保護は、解除できるのですか？
例えば、パスワードはかかっていない とか パスワードを知っている とか。
出来ないなら、マクロでの書き込みは無理なんじゃないでしょうか。
毎回操作が必要ですが SUMIF関数を埋め込んで、値貼り付けして保存 でも良さそうに思います。
数式の範囲を間違えなければ ＞目視にて作業を行っている為、入力場所や数量の入力間違いが多発 って事は無くなるんじゃないかと思います。
(HANA)
こんにちは。 わたしもSumif関数で充分だと思います。 集計元のファイルにでも Sumif関数の式を書き込んでおいて 集計先のファイルにコピペして使えば手間もかからずミスもありません。
あるいは本社のかたと相談して、最初からSumif関数と ダミーの集計元シートの仕込んである ファイルを送っていただくのもいいかもしれません。
−佳−
ぺんの助
どの様に使用して行かれるのか分かりませんが
＞※数量を入力するセルには条件付書式が設定されています。 と言う事なので、この書式を消してしまわないように 注意してください。
セルのコピー＆貼り付けでは 書式も貼り付いてしまいます。
・別の場所で計算させて、値貼り付けをする ・数式のみを貼り付けて、値貼り付けをする ・同じ書式を数式が入ったセルにも設定して於いて そのセルを貼り付け、値貼り付けする 等の工夫を考えてみてください。
(HANA)
だったら、やっぱりVBAでしてしまえば？ 最初の質問も > ＶＢＡでプログラミングは可能でしょうか だったし、それに対して、かみちゃんさんとseiyaさんがコードを提供しているのだから、試してみた結果くらい 伝えてもいいのでは？ 値貼り付けのミスなど防げるし、数式を設定したことによるブックのサイズが大きくなることを防げるし、 かみちゃんさんのコードは、瞬時に集計できるみたいですよ。
(VBA勉強中)
正直なところ私自身、ＶＢＡにて作成した経験が無く、いつも「マクロの記録」に頼ってばかりいました。
かみちゃんさんのコードを試したのですが、うまくいかず私の定義ミスなのではと思い諦めておりました。ごめんなさい。
こんな私でもまだ教えて頂けるのなら是非ご指導をお願いしたいのですが．．．。
かみちゃんさんのコードを実行した際、「集計しました」とメッセージは出るのですが、ｓｈｅｅｔ２には値が入っていませんでした。
ウオッチ式を使ってvntDataを見たところ数の集計もされており、値も思っている位置に入っているところまでは確認出来たのですが、その後がわかりません。
もしよろしければコードの意味を教えて頂けると助かります。
自分なりに勉強・理解したいと思っております。
（ぺんの助）
今回の件に使用することを考えて居られるのでしたら 保護が解除出来る必要が有りますが その点は大丈夫ですか？
今後の為に と言う事であれば、シートの保護は 問題にしなくても良いと思いますが。
(HANA)
こんにちは。かみちゃん です。
> コードを実行した際、「集計しました」とメッセージは出るのですが、ｓｈｅｅｔ２には値が入っていませんでした。 > > ウオッチ式を使ってvntDataを見たところ数の集計もされており、値も思っている位置に入っているところまでは確認出来た
ちょっと状況がよくわかりません。 私が提案させていただいたコードは、便宜上、ファイルＡとファイルＢを同一ブックにしています。 こちらで作成したサンプルファイルを勝手ながら下記へアップさせていただきますので、ご確認いただければと思います。 http://kamicha1.web.fc2.com/Excel/pennosuke20090823.html Sample20090823Pennosuke.zip をダウンロードしていただき解凍していただき、 Sampleマクロを実行してみてください。
> コードの意味を教えて頂けると
申し訳ありませんが、以下を参考にまずは、ご自身で調べていただけませんか？ http://officetanaka.net/excel/vba/tips/tips80.htm http://www.officetanaka.net/excel/vba/tips/tips52.htm http://www.niji.or.jp/home/toru/notes/17.html http://www.geocities.jp/cbc_vbnet/Scripting/dictionary.html
(かみちゃん) 2009/08/24 5:15
現象だけ見てパッと思ったのは、
・シートに保護がかかっていてon error resume next が効いているのではないかと。 かみちゃんさんのコードに on error resume next はありませんが。
・ぺんの助さんのおもっているシートと、実際に貼り付けされているシートが違っているかも コードに関係のある全シートのWorksheet_Changeイベントに MsgBox Target.Parent.Name & "に貼り付け" とでも入れて様子をみるとか
−佳−
−佳−さん この失礼な発言にけりつけたら？ [[20090819104249]] (seiya)
私の単純なミスでした。すみません。
（ペンの助）
復活ありがとうございます。 (seiya)
kazuさんではありませんが、差分で見たら情報が残っていたので、復活しておきました〜 (ROUGE)
ROUGEさん、 ありがとうございます。 ...困ったもんですね... (seiya)
ペンの助さん、すみません、お借りします。
seiya さん、こんにちは。佳です。
ご指摘ありがとうございます。 じつは、あの時点で、「あ、間違えたかな、じゃ、謝らなきゃ」とは思ったのです。 「じゃあ ご本人のご発言を待って。確認したいこともあるし。」と。 ところがご本人曰く「こうなったら意地でも最後までがんばる」。 それで、ここでへたに謝罪とかするより だまっているほうがご本人のためになるだろうと 考えてあえてそのままにしています。
そうするのがふさわしいときがきたら、しかるべくケリをつけます。 お気遣い頂きましてありがとうございました。
−佳−
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.