[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBA 各シートから情報を取得して比較表を作成したい』（楓）
お世話になります。
ご教授頂けると助かります。
【設定】シートに事前に設定
・セルC2に2016年5月
・セルC3に2015年5月
設定シートのC3にあたるデータを【1】からそれぞれ取得して【2】の対象セルに記入
設定シートのC2にあたるデータを【1】からそれぞれ取得して【2】の対象セルに記入
【1】シート名：店名1〜店名19（エクセルファイルの4つめから全てこのシート）
・各シートのレイアウトは統一
・2〜35行目に2015年のデータ
・39〜72行目に2016年のデータ
・B〜L列に1月、M〜W列に2月と11列ずつ1〜12月までデータがある
※2017年になったら76〜109行目に記載していきます※
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] |[AA]|[AB]|[AC] |[AD]|[AE]|[AF] |[AG]|[AH]|[AI] |[AJ] |[AK] |[AL]|[AM]|[AN] |[AO]|[AP]|[AQ] |[AR]|[AS]|[AT] |[AU] |[AV] |[AW]|[AX]|[AY] |[AZ]|[BA]|[BB] |[BC]|[BD]
[1] | | 01|店名1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
[2] |2015|2015年1月| | | | | | | | | | |2015年2月| | | | | | | | | | |2015年3月| | | | | | | | | | |2015年4月| | | | | | | | | | |2015年5月| | | | | | | | | |
[3] | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| |
[4] | | | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上| | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上| | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上| | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上| | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上
[5] | 1|1/1 | | | | | | | | | | |2/1 | | | | | | | | | | |3/1 | | | | | | | | | | |4/1 | | | | | | | | | | |5/1 | | | | | | | | | |
[6] | 2|1/2 | | | | | | | | | | |2/2 | | | | | | | | | | |3/2 | | | | | | | | | | |4/2 | | | | | | | | | | |5/2 | | | | | | | | | |
省略
[34]| 30|1/30 | | | | | | | | | | | | | | | | | | | | | |3/30 | | | | | | | | | | |4/30 | | | | | | | | | | |5/30 | | | | | | | | | |
[35]| 31|1/31 | | | | | | | | | | | | | | | | | | | | | |3/31 | | | | | | | | | | | | | | | | | | | | | |5/31 | | | | | | | | | |
[36]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
[37]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
[38]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
[39]|2016|2016年1月| | | | | | | | | | |2016年2月| | | | | | | | | | |2016年3月| | | | | | | | | | |2016年4月| | | | | | | | | | |2016年5月| | | | | | | | | |
[40]| |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| | |日付 |店舗売上|広告売上| | |媒体1| | |媒体2| |
[41]| | | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上| | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上| | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上| | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上| | |組数 |人数|売上|組数 |人数|売上|組数 |人数|売上
[42]| 1|1/1 | | | | | | | | | | |2/1 | | | | | | | | | | |3/1 | | | | | | | | | | |4/1 | | | | | | | | | | |5/1 | | | | | | | | | |
[43]| 2|1/2 | | | | | | | | | | |2/2 | | | | | | | | | | |3/2 | | | | | | | | | | |4/2 | | | | | | | | | | |5/2 | | | | | | | | | |
省略
[71]| 30|1/30 | | | | | | | | | | | | | | | | | | | | | |3/30 | | | | | | | | | | |4/30 | | | | | | | | | | |5/30 | | | | | | | | | |
[72]| 31|1/31 | | | | | | | | | | | | | | | | | | | | | |3/31 | | | | | | | | | | | | | | | | | | | | | |5/31 | | | | | | | | | |
【2】シート名：比較表
・A1〜Y35まで店名1、AA1〜AY35まで店名2・・・右は5店舗迄
・A38〜Y72まで店名6、AA38〜AY72まで店名7・・・下はシート数に合わせて作成
・追加していく分は表ごと作成できると助かります。
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z]|[AA] |[AB] |[AC] |[AD]|[AE] |[AF]|[AG]|[AH] |[AI]|[AJ]|[AK]|[AL] |[AM]|[AN]|[AO] |[AP]|[AQ]|[AR]|[AS] |[AT]|[AU]|[AV] |[AW]|[AX]|[AY]
[1] |店名1| | | | | | | | | | | | | | | | | | | | | | | | | |店名2| | | | | | | | | | | | | | | | | | | | | | | |
[2] |日付 |店舗売上| | |広告売上| | | | | | |媒体1 | | | | | | |媒体2 | | | | | | | |日付 |店舗売上| | |広告売上| | | | | | |媒体1 | | | | | | |媒体2 | | | | | |
[3] | | | | |組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率| | | | | |組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率
[4] | |15年5月 |16年5月|比率|15年5月 | | |16年5月| | | |15年5月| | |16年5月| | | |15年5月| | |16年5月| | | | | |15年5月 |16年5月|比率|15年5月 | | |16年5月| | | |15年5月| | |16年5月| | | |15年5月| | |16年5月| | |
[5] | 1| | | | | | | | | | | | | | | | | | | | | | | | | | 1| | | | | | | | | | | | | | | | | | | | | | | |
[6] | 2| | | | | | | | | | | | | | | | | | | | | | | | | | 2| | | | | | | | | | | | | | | | | | | | | | | |
省略
[34]| 30| | | | | | | | | | | | | | | | | | | | | | | | | | 30| | | | | | | | | | | | | | | | | | | | | | | |
[35]| 31| | | | | | | | | | | | | | | | | | | | | | | | | | 31| | | | | | | | | | | | | | | | | | | | | | | |
[36]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
[37]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
[38]|店名6| | | | | | | | | | | | | | | | | | | | | | | | | |店名7| | | | | | | | | | | | | | | | | | | | | | | |
[39]|日付 |店舗売上| | |広告売上| | | | | | |媒体1 | | | | | | |媒体2 | | | | | | | |日付 |店舗売上| | |広告売上| | | | | | |媒体1 | | | | | | |媒体2 | | | | | |
[40]| | | | |組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率| | | | | |組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率
[41]| |15年5月 |16年5月|比率|15年5月 | | |16年5月| | | |15年5月| | |16年5月| | | |15年5月| | |16年5月| | | | | |15年5月 |16年5月|比率|15年5月 | | |16年5月| | | |15年5月| | |16年5月| | | |15年5月| | |16年5月| | |
[42]| 1| | | | | | | | | | | | | | | | | | | | | | | | | | 1| | | | | | | | | | | | | | | | | | | | | | | |
[43]| 2| | | | | | | | | | | | | | | | | | | | | | | | | | 2| | | | | | | | | | | | | | | | | | | | | | | |
省略
[71]| 30| | | | | | | | | | | | | | | | | | | | | | | | | | 30| | | | | | | | | | | | | | | | | | | | | | | |
[72]| 31| | | | | | | | | | | | | | | | | | | | | | | | | | 31| | | | | | | | | | | | | | | | | | | | | | | |
わかりずらく申し訳ありませんが、宜しくお願い致します。
＜ 使用 Excel：Excel2007、使用 OS：WindowsVista ＞
こんばんわ。
今回のレイアウトは、大体わかりました。 比較表のA1が店舗名になってますが、A1が店舗CD・B1が店舗名の間違いでしょうか？
楓さんなら、前回のご自身で作成されたマクロなどと比べたら、これくらいご自身で出来るだけのスキルを充分お持ちだと思うんですが、 どう言った事が分からないのでしょうか？
（sy） 2016/05/23(月) 21:25
おはようございます。いつもありがとうございます。
＞比較表のA1が店舗名になってますが、A1が店舗CD・B1が店舗名の間違いでしょうか？
今回は【1】B1にある店舗CDは【2】にいれないことにしています。
過去ログで作って頂いたマクロを参考に作成はしているのですが、【2】の表がある状態でないと書き込みができないコードになっています。
配列があまり理解できておらず、for〜nextばかりのコードになってしまっており、ちょっと書き換えると違う形になってしまい困惑している状態です・・・
可能な限り、店舗1の表レイアウト書式をコピーしてそこに記載できればと考えています。
また、現状作成しているコードだと縦に進んで行くため、横5店舗記載したら下の段に記載という形をとりたいです。
Sub 比較表作成()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'■宣言■
Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim i As Long, c As Long, ro As Long, ro2 As Long
Dim r As Long, rowT As Long, rowT2 As Long, rowT3 As Long, colT As Long, colT2 As Long
'■設定■
'実行中のマクロが記述されているブックのフォルダへの絶対パス
thisbook_path = ThisWorkbook.Path
'設定シートを指定
Set ws = ThisWorkbook.Sheets("設定")
'書き込むシートを指定
Set ws2 = ThisWorkbook.Sheets("比較表")
'前年比取得で検索する日付を指定
y = ws.Range("C2")
'比較対象取得で検索する日付を指定
x = ws.Range("C3")
y = DateSerial(Year(y), Month(y), 1)
x = DateSerial(Year(x), Month(x), 1)
colT = 2 + (Month(y) - 1) * 11 '1月ならB列を指定 2月ならM列を指定
colT2 = 2 + (Month(x) - 1) * 11 '1月ならB列を指定 2月ならM列を指定
'■データを取得する■
With ws2
i = 4 'シートを変更していく
For c = 1 To .Cells(2, .Columns.Count).End(xlToLeft).Column Step 26 '比較表を1列目から26列飛ばしで繰り返す
rowT2 = 5
rowT3 = 5
For r = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row Step 37 '比較表を1行目から37行飛ばしで繰り返す
'■セル内容をクリアする■
.Range(.Cells(r, c), .Cells(r, c + 24)).ClearContents '店舗名クリア
.Range(.Cells(r + 3, c + 1), .Cells(r + 3, c + 2)).ClearContents '年月クリア
.Range(.Cells(r + 4, c + 1), .Cells(r + 34, c + 24)).ClearContents '年月クリア
'■基本情報を書き込む■
.Cells(r, c) = Sheets(i).Range("C1") '店舗名
.Cells(r + 3, c + 1) = y '昨年月
.Cells(r + 3, c + 2) = x '今年月
For ro = 5 To Sheets(i).Cells(Sheets(i).Rows.Count, colT).End(xlUp).Row Step 37 '元になるシートを5行目から35行飛ばしで繰り返す
If Sheets(i).Cells(ro, colT).Value = y Then 'yが年月日一致するなら
Application.StatusBar = "処理実行中．．．．(現在 " & i - 3 & "/" & Worksheets.Count - 3 & "件)" & _
String(i - 3, "■") & String(Worksheets.Count - i, "□")
For rowT = ro To ro + 31
'■比較表に書き込む■
.Cells(rowT2, c + 1) = Sheets(i).Cells(rowT, colT + 1) '店舗売上
.Cells(rowT2, c + 4) = Sheets(i).Cells(rowT, colT + 2) '広告組数
.Cells(rowT2, c + 5) = Sheets(i).Cells(rowT, colT + 3) '広告人数
.Cells(rowT2, c + 6) = Sheets(i).Cells(rowT, colT + 4) '広告売上
.Cells(rowT2, c + 11) = Sheets(i).Cells(rowT, colT + 5) '媒体1組数
.Cells(rowT2, c + 12) = Sheets(i).Cells(rowT, colT + 6) '媒体1人数
.Cells(rowT2, c + 13) = Sheets(i).Cells(rowT, colT + 7) '媒体1売上
.Cells(rowT2, c + 18) = Sheets(i).Cells(rowT, colT + 8) '媒体2組数
.Cells(rowT2, c + 19) = Sheets(i).Cells(rowT, colT + 9) '媒体2人数
.Cells(rowT2, c + 20) = Sheets(i).Cells(rowT, colT + 10) '媒体2売上
rowT2 = rowT2 + 1
'31回回したら、次の店舗へ
If rowT2 = 36 Then
rowT2 = 41
ElseIf rowT2 = 73 Then
rowT2 = 78
ElseIf rowT2 = 110 Then
rowT2 = 115
End If
Next
End If
Next
For ro = 5 To Sheets(i).Cells(Sheets(i).Rows.Count, colT2).End(xlUp).Row Step 37 '元になるシートを5行目から35行飛ばしで繰り返す
If Sheets(i).Cells(ro, colT2).Value = x Then 'xが年月日一致するなら
For rowT = ro To ro + 31
'■比較表に書き込む■
.Cells(rowT3, c + 2) = Sheets(i).Cells(rowT, colT2 + 1) '店舗売上
.Cells(rowT3, c + 7) = Sheets(i).Cells(rowT, colT2 + 2) '広告組数
.Cells(rowT3, c + 8) = Sheets(i).Cells(rowT, colT2 + 3) '広告人数
.Cells(rowT3, c + 9) = Sheets(i).Cells(rowT, colT2 + 4) '広告売上
.Cells(rowT3, c + 14) = Sheets(i).Cells(rowT, colT2 + 5) '媒体1組数
.Cells(rowT3, c + 15) = Sheets(i).Cells(rowT, colT2 + 6) '媒体1人数
.Cells(rowT3, c + 16) = Sheets(i).Cells(rowT, colT2 + 7) '媒体1売上
.Cells(rowT3, c + 21) = Sheets(i).Cells(rowT, colT2 + 8) '媒体2組数
.Cells(rowT3, c + 22) = Sheets(i).Cells(rowT, colT2 + 9) '媒体2人数
.Cells(rowT3, c + 23) = Sheets(i).Cells(rowT, colT2 + 10) '媒体2売上
If .Cells(rowT3, c + 1) <> "" And .Cells(rowT3, c + 2) <> "" Then
.Cells(rowT3, c + 3) = .Cells(rowT3, c + 2) / .Cells(rowT3, c + 1)
.Cells(rowT3, c + 10) = .Cells(rowT3, c + 9) / .Cells(rowT3, c + 6)
.Cells(rowT3, c + 17) = .Cells(rowT3, c + 16) / .Cells(rowT3, c + 13)
.Cells(rowT3, c + 24) = .Cells(rowT3, c + 23) / .Cells(rowT3, c + 20)
End If
rowT3 = rowT3 + 1
'31回回したら、次の店舗へ
If rowT3 = 36 Then
rowT3 = 41
ElseIf rowT3 = 73 Then
rowT3 = 78
ElseIf rowT3 = 110 Then
rowT3 = 115
End If
Next
End If
Next
i = i + 1 'シートを変更していく
Next
Next
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
（楓） 2016/05/24(火) 09:38
こんにちわ。
帰ったら良く見てみますけど、ヒントだけ、 今はスマホなのでコードとか見辛いので、良く見てませんが、 1、の表の2015年か2016年のデータを5店舗づつ、 丸ごとコピペして列の挿入でレイアウト分広げて、 不足の項目名を記入して、片割れのデータを コピペすれば良いんじゃないでしょうか。
日付や比率は数式を書き込んだら良いと思います。
（sy） 2016/05/24(火) 15:14
こんばんわ。
初め見た時もっと簡単かと思ってましたが、実際にコード書いてみると結構めんどくさいですね。 安易な発言申し訳ありませんでした。
楓さんのコードでは2015年と2016年がレイアウトと逆になってますけど、どっちが正解ですか？
一応レイアウトの方に合わせて書いてみました。
それと気になったのが、変数の宣言が無いものがあるので、Option Explicitを記述させるようにして、 変数の宣言は強制した方が、変数間違いによるトラブルを未然に防げるので、強くお勧めします。
Option Explicitは、VBEのツール→オプション→変数の宣言を強制する、にチェックを入れて下さい。
以降新しくモジュールを追加したら、Option Explicitが自動で記述されます。
以下はコードです。 あまり簡潔に書けなかったので、分かりにくかったら、項目と日付の部分がレイアウト作成なので、プラス書式設定の部分だけ流用して下さい。
Sub 比較表作成2()
Dim i As Long, j As Integer, s As Integer
Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim d1 As Date, d2 As Date
Dim x As Integer, y1 As Long, y2 As Long
Application.ScreenUpdating = False
'設定シートを指定
Set ws = ThisWorkbook.Sheets("設定")
'書き込むシートを指定
Set ws2 = ThisWorkbook.Sheets("比較表")
'日付検索
s = 4
Set ws3 = Sheets(s)
d1 = ws.Range("C2").Value
d2 = ws.Range("C3").Value
If WorksheetFunction.CountIf(ws3.Range("A:A"), Year(d1)) = 0 Then
MsgBox "今年度実績がありません！"
Exit Sub
End If
If Year(d2) < 2015 Then
MsgBox "昨年度実績がありません！"
Exit Sub
End If
x = (Month(ws.Range("C2").Value) - 1) * 11 + 2
y1 = (Year(d1) - 2015) * 37 + 5
y2 = (Year(d2) - 2015) * 37 + 5
'データ転記
ws2.Cells.Clear
For i = 1 To 1000 Step 37
For j = 1 To 105 Step 26
Set ws3 = Sheets(s)
'項目
ws2.Cells(i, j).Value = ws3.Range("C1").Value
ws2.Range("A1:S1").Offset(i, j - 1).Value = Split("日付,店舗売上,,,広告売上,,,,,,,媒体1,,,,,,,媒体2", ",")
ws2.Range("A1:Y1").Offset(i + 1, j - 1).Value = _
Split(",,,,組数,人数,売上,組数,人数,売上,比率,組数,人数,売上,組数,人数,売上,比率,組数,人数,売上,組数,人数,売上,比率", ",")
ws2.Range("A1,D1,K1,R1").Offset(i + 2, j).Value = d2
ws2.Range("A1,F1,M1,T1").Offset(i + 2, j + 1).Value = d1
ws2.Range("C3").Offset(i, j).Value = "比率"
'日付
ws2.Cells(i + 4, j).Resize(31).Value = ws3.Cells(y1, x).Resize(31).Value
ws2.Range("A:A").Offset(, j - 1).NumberFormatLocal = "d"
'店舗売上
ws2.Cells(i + 4, j + 1).Resize(31).Value = ws3.Cells(y2, x + 1).Resize(31).Value
ws2.Cells(i + 4, j + 2).Resize(31).Value = ws3.Cells(y1, x + 1).Resize(31).Value
ws2.Cells(i + 4, j + 3).Resize(31).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-2],"""")"
ws2.Cells(i + 4, j + 3).Resize(31).NumberFormatLocal = "0.00%"
'広告売上
ws2.Cells(i + 4, j + 4).Resize(31, 3).Value = ws3.Cells(y2, x + 2).Resize(31, 3).Value
ws2.Cells(i + 4, j + 7).Resize(31, 3).Value = ws3.Cells(y1, x + 2).Resize(31, 3).Value
ws2.Cells(i + 4, j + 10).Resize(31).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-4],"""")"
ws2.Cells(i + 4, j + 10).Resize(31).NumberFormatLocal = "0.00%"
'媒体1
ws2.Cells(i + 4, j + 11).Resize(31, 3).Value = ws3.Cells(y2, x + 5).Resize(31, 3).Value
ws2.Cells(i + 4, j + 14).Resize(31, 3).Value = ws3.Cells(y1, x + 5).Resize(31, 3).Value
ws2.Cells(i + 4, j + 17).Resize(31).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-4],"""")"
ws2.Cells(i + 4, j + 17).Resize(31).NumberFormatLocal = "0.00%"
'媒体2
ws2.Cells(i + 4, j + 18).Resize(31, 3).Value = ws3.Cells(y2, x + 8).Resize(31, 3).Value
ws2.Cells(i + 4, j + 21).Resize(31, 3).Value = ws3.Cells(y2, x + 8).Resize(31, 3).Value
ws2.Cells(i + 4, j + 24).Resize(31).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-4],"""")"
ws2.Cells(i + 4, j + 24).Resize(31).NumberFormatLocal = "0.00%"
s = s + 1
If s > Sheets.Count Then Exit For
Next j
ws2.Range("A" & i + 3).EntireRow.NumberFormatLocal = "yy年m月"
If s > Sheets.Count Then Exit For
Next i
Application.ScreenUpdating = True
End Sub
（sy） 2016/05/25(水) 02:02
おはようございます。
媒体2の2行目の店名シート参照元の行が間違ってました！
誤）y2 → 正）y1
でした、すいません。
（sy） 2016/05/25(水) 08:11
おはようございます。
＞楓さんのコードでは2015年と2016年がレイアウトと逆になってますけど、どっちが正解ですか？
⇒レイアウト優先で問題ありません。起動させてみて逆になっていることに気付きました。。。
＞それと気になったのが、変数の宣言が無いものがあるので、Option Explicitを記述させるようにして、
変数の宣言は強制した方が、変数間違いによるトラブルを未然に防げるので、強くお勧めします。
Option Explicitは、VBEのツール→オプション→変数の宣言を強制する、にチェックを入れて下さい。
以降新しくモジュールを追加したら、Option Explicitが自動で記述されます。
⇒お恥ずかしい話・・・初めて知りました。と同時にググってみて理解致しました。
さっそくチェックをいれて間違いがないようにしていきます！
コードの方は今から確認させて頂きます。
正直自分のでは、縦に増えたときに 「'31回回したら、次の店舗へ」の部分を追記しないといけないと思っていたので、このまま使わせて頂くと思います。
（楓） 2016/05/25(水) 10:50
ありがとうございました！
データ転記、Resizeでできるんですね・・・前にやったとき出来なかったので・・・
なんとなく、「.value」をはずしてみたら空白になったので、この部分かなぁとか・・・
書かない事が多いので・・・省略しないよう気をつけていきます！
起動させてみたら、30秒くらいかかってた所が2秒とかで終わりました！！！
コードの簡略及び希望通りのレイアウトでとても助かりました！
あとは罫線などのコードを追加で作成致します。
（楓） 2016/05/25(水) 12:18
条件付き書式において、
ルール（数式）⇒ =$A5=TODAY()-1
適用先⇒ =$A$5:$Y$35
をマクロで設定したいと思っているんですが
Dim fc2 As FormatCondition
Set fc2 = ws2.Cells(i + 4, j).Resize(31, 25).FormatConditions.Add(Type:=xlExpression, Formula1:="=RC=DAY(TODAY()-1)")
で設定すると
ルール（数式）⇒ =A5=TODAY()-1
適用先⇒ =$A$5:$Y$35
となってしまいました。
上記にするにはどうしたらよろしいでしょうか？
（楓） 2016/05/25(水) 17:31
こんばんわ。
R1C1形式で絶対参照にしたい時は、 RやCの右に行番号や列番号を入れます。
上記の場合でしたらRC1として下さい。
（sy） 2016/05/25(水) 18:49
おはようございます。
RC1だと「=RC1048572=TODAY()-1」などになってしまいました。
そこでいろいろ試して、R1C⇒A$5、RC[0]⇒A5、などができたので、
R[0]C1にしたみたところ「=$A5=TODAY()-1」とすることができました！
（楓） 2016/05/26(木) 09:52
上記で取得した比較表から比較一覧表として指定した日付で全店舗の一覧を作成しています。
VLOOKUPを使用しているのですが、
最後の最後に「実行時エラー '1004': WorksheetFunction クラスの VLookupプロパティを取得できません。」となってしまいます。
このエラーを回避する方法などありましたら教えて下さい。
「On Error Resume Next」で上記エラーとなっていても続けることができるのはわかっているのですが、前からVLOOKUPを使用してでてくるため、今回追加で質問させていただきました。
比較一覧表のレイアウト
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y]
[1]|25日時点| | | | | | | | | | | | | | | | | | | | | | | |
[2]|店舗名 |店舗売上| | |広告売上| | | | | | |媒体1 | | | | | | |媒体2 | | | | | |
[3]| | | | |組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率|組数 |人数|売上|組数 |人数|売上|比率
[4]| |15年5月 |16年5月|比率|15年5月 | | |16年5月| | | |15年5月| | |16年5月| | | |15年5月| | |16年5月| | |
[5]|店名1 | | | | | | | | | | | | | | | | | | | | | | | |
[6]|店名2 | | | | | | | | | | | | | | | | | | | | | | | |
Sub 比較一覧表()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'■宣言■
Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim i As Long, c As Long, ro As Long, ro2 As Long
Dim r As Long, rowT As Long, rowT2 As Long, rowT3 As Long, colT As Long, colT2 As Long
Dim thisbook_path As String
Dim y As Long, x As Long
Dim fc As FormatCondition
'■設定■
'実行中のマクロが記述されているブックのフォルダへの絶対パス
thisbook_path = ThisWorkbook.Path
'設定シートを指定
Set ws = ThisWorkbook.Sheets("設定")
'書き込むシートを指定
Set ws2 = ThisWorkbook.Sheets("比較表")
Set ws3 = ThisWorkbook.Sheets("比較一覧表")
'前年比取得で検索する日付を指定
y = ws.Range("C2")
'比較対象取得で検索する日付を指定
x = ws.Range("C3")
UserForm2.Show vbModeless
UserForm2.Repaint
With ws3
.Range("A1") = Day(ws.Range("C5")) & "日時点"
.Range("B4") = y
.Range("C4") = x
.Range("A5:Y5").ClearContents
.Range("A6:Y50").Delete
Cells.FormatConditions.Delete
Set fc = Range("D5,K5,R5,Y5").FormatConditions.Add(xlCellValue, xlLess, "1")
fc.Font.ColorIndex = 3
fc.Font.Bold = True
i = 4
For rowT = 1 To ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row Step 37 '比較表を1行目から37行飛ばしで繰り返す
For colT = 1 To 105 Step 26 '比較表を1列目から26列飛ばしで繰り返す
For c = 2 To 25
.Cells(i + 1, 1).Value = ws2.Cells(rowT, colT)
.Cells(i + 1, c).Value = Application.WorksheetFunction.VLookup(ws.Range("C5"), ws2.Range(ws2.Cells(rowT + 4, colT), ws2.Cells(rowT + 34, colT + 24)), c, False) ※エラー箇所※
Next
i = i + 1
Next
Next
For c = 2 To 25
.Cells(i, 1) = "合計"
.Range(.Cells(i, c), .Cells(i, c)).Value = Application.WorksheetFunction.Sum(.Range(.Cells(5, c), .Cells(i - 1, c)))
Next
.Cells(i, 4).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-2],"""")" '比率計算
.Cells(i, 11).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-4],"""")" '比率計算
.Cells(i, 18).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-4],"""")" '比率計算
.Cells(i, 25).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-4],"""")" '比率計算
.Rows(5).Copy
.Range(.Cells(6, 1), .Cells(i, 25)).PasteSpecial Paste:=xlPasteFormats
.Range(.Cells(i, 1), .Cells(i, 25)).Borders(xlEdgeTop).LineStyle = xlDouble
End With
Unload UserForm2
Application.StatusBar = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
（楓） 2016/05/26(木) 11:21
こんばんわ。
VLOOKUPでエラーになる時は、店舗数が 5 で割り切れない時ですね。
このような時、関数内で処理したければ、WorksheetFunctionはエラーそのものを返し、Applicationのみの時はエラー値を返すので、 Applicationのみの記述にして、 下のように、IFERROR関数をネストするか、
.Cells(i + 1, c).Value = Application.IfError(Application.VLookup(ws.Range("C5"), ws2.Range(ws2.Cells(rowT + 4, colT), ws2.Cells(rowT + 34, colT + 24)), c, False), """")
IF文でエラーにならない時だけ実行すると言う方法になります。
If Not IsError(数式の記述) Then
数式の記述
End If
でもエラーをトラップするより、私ならエラーの原因である、20店舗目が無いと言う事をIF文で判断させます。
後もっと極端に言えば、今回のケースでは日付と行は相関関係にあるので(3日なら5行目や44行目)、日付が決まると行も決まるので、 下記コードのように位置関係だけの記述の方が良いと思います。 下記のコードではフォームが何を意味するのか分からなかったので、フォームの部分は省略しています。
Sub 比較一覧表2()
Dim i As Long, j As Integer, i1 As Long, i2 As Long
Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim d1 As Date
Dim cnt As Integer, c As Integer
'設定シートを指定
Set ws = ThisWorkbook.Sheets("設定")
'書き込むシートを指定
Set ws2 = ThisWorkbook.Sheets("比較表")
Set ws3 = ThisWorkbook.Sheets("比較一覧表")
'読込む日付を指定
d1 = ws.Range("C5").Value
'読込日の存在チェック
If WorksheetFunction.CountIf(ws2.Range("A:A"), d1) = 0 Then
MsgBox "表示したい日付データがありません！"
Exit Sub
End If
i1 = Day(d1) + 3
'シート初期化
ws3.Cells.Clear
'レイアウト及び項目欄をコピー
cnt = Sheets.Count - 3
ws2.Range("B2:Y4").Copy ws3.Range("B2")
ws2.Range("A1:Y1").Resize(cnt + 5).Copy
ws3.Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
ws3.Range("A1").Rows(cnt + 5).Value = "合計"
ws3.Range("A1:Y1").Rows(cnt + 5).Borders(xlEdgeTop).LineStyle = xlDouble
ws3.Range("A1").Value = d1
ws3.Range("A1").NumberFormatLocal = "d日時点"
ws3.Range("A2").Value = "店舗名"
'集計セット
ws3.Range("B1:Y1").Rows(cnt + 5).Formula = "=SUM(" & ws3.Range("B5").Resize(cnt).Address(0, 0) & ")"
ws3.Range("D1").Rows(cnt + 5).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-2],"""")"
ws3.Range("K1,R1,Y1").Offset(cnt + 4).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-4],"""")"
'データ取得
For i = 1 To 1000 Step 37
For j = 2 To 106 Step 26
c = c + 1
i2 = Int(i / 37) * 5 + Int(j / 26) + 5
ws3.Cells(i2, "A").Value = ws2.Cells(i, j - 1).Value
ws3.Cells(i2, "B").Resize(, 24).Value = ws2.Cells(i + i1, j).Resize(, 24).Value
If c >= cnt Then Exit Sub
Next j
Next i
End Sub
（sy） 2016/05/27(金) 00:26
おはようございます。
今回質問してよかったです。
IFERROR関数をネストする場合と記載頂いた全コードで試してみました！
記載時間的にもご提示頂いた全コードの方が明らかに早かったため使わせて頂きたいと思います。
ただ確認してみて、1点・・・
ws3.Range("K1,R1,Y1").Offset(cnt + 4).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-4],"""")"
の部分ですが、
K列は合計の行にきちんと記載されているのですが、
R列とY列はその上の行に記載されていました。
Offset(cnt + 4)をOffset(cnt + 5)にすると、K列は合計の行のひとつ下になり、R列とY列は合計の行に記載となりました。
ws3.Range("A1")の結合を解除したら、Offset(cnt + 4)で全て記載できました・・・
（A1は結合なしでいいと思って単純に行っただけだったのですが・・・）
（楓） 2016/05/27(金) 10:39
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.