Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
TREND関数を For To Next ステートメントで回したい
投稿日時: 17/10/05 15:12:30
投稿者: Dellcasio

お世話になっております。
 
 
9月に発売された インプレス
Excel できる大辞典に掲載されている
TREND関数 (512ページ)
の3ヵ月先の売上予測をVBAの
For To Next ステートメントを使い
連続計算をしたいのですが、
 
エクセル関数のTRENDを入力終了時に
Ctrl +  Shift + Enter キー を押す
箇所に当たる部分で、VBAコードの 
ActiveSheet.Range("F" & i + 5 & ":F" & i + 7).Select )の
部分になるのですが、1回目のループで
「” 配列の一部 を変更することはできません ”」 
と言う警告メッセージ が出てきて、
1回目のループで止まってしまい、次に進めません。
 
For To Nextステートメントで連続計算をしたいのですが、
ご指導を頂けないでしょうか
よろしくお願い致します。
 
 
 
Sub t()
 
For i = 1 To 10
 
    ActiveSheet.Range("D" & i + 5 & ":D" & i + 7).Select
 
    Selection.FormulaArray = _
    "= TREND(B" & i + 2 & ":B" & i + 4 & ",A" & i + 2 & ":A" & i + 4 & ",A" & i + 5 & ":A" & i + 7 & ")"
     
Next i
 
End Sub
 
 
A列   B列
XXX    YYY
2007    20080
2008    31540
2009    41440
2010    33360
2011    34560
2012    41440
2013    30560
2014    43920
2015    48560
2016    34560
2017    41440
2018    
2019    
2020    
 
 
 
 
 
 
 
 
 
 
 
 

回答
投稿日時: 17/10/05 16:57:50
投稿者: 半平太

こちらは、大辞典を持っていないし、Dellcasio さんの説明では、
「連続計算」とは、どんな計算なのか分からないです。
 
下の「?」部分が何となるべきなのか、その説明が必要ですよ。
  ↓
第1回目は、「D6:D8」の範囲に「=TREND(B3:B5,A3:A5,A6:A8)」を埋める。これは出来た。
第2回目は、「 ? 」の範囲に「=      ?     」を埋めたい。
 
もしくは、掲示サンプルの場合、D列がどうなるべきなのかを示してください。

回答
投稿日時: 17/10/05 17:13:08
投稿者: WinArrow
投稿者のウェブサイトに移動

ループ1回目の実行で、D6〜D8に数式が「入ります。
ループ2回目の実行で、D7〜D9に数式を入れようとして
 D7〜D8には、ループ1回目に数式が入っています。
だからエラー(配列の一部 を変更することはできません )になるんです。
 
理解できましたか?
 
要するに2回目以降は、前回のセル範囲と重複しないようにすることです。
 
 

投稿日時: 17/10/05 17:31:36
投稿者: Dellcasio

半平太 さんの引用:
こちらは、大辞典を持っていないし、Dellcasio さんの説明では、
「連続計算」とは、どんな計算なのか分からないです。
 
下の「?」部分が何となるべきなのか、その説明が必要ですよ。
  ↓
第1回目は、「D6:D8」の範囲に「=TREND(B3:B5,A3:A5,A6:A8)」を埋める。これは出来た。
第2回目は、「 ? 」の範囲に「=      ?     」を埋めたい。
 
もしくは、掲示サンプルの場合、D列がどうなるべきなのかを示してください。

 
半平太さん
ご回答ありがとうございます。
状況は以下の通りです。
 
>>第1回目は、「D6:D8」の範囲に「=TREND(B3:B5,A3:A5,A6:A8)」を埋める。これは出来た。
ここの部分は出来ました。
 
第2回目から出来ません
 

投稿日時: 17/10/05 17:50:18
投稿者: Dellcasio

WinArrow さんの引用:
ループ1回目の実行で、D6〜D8に数式が「入ります。
ループ2回目の実行で、D7〜D9に数式を入れようとして
 D7〜D8には、ループ1回目に数式が入っています。
だからエラー(配列の一部 を変更することはできません )になるんです。
 
理解できましたか?
 
要するに2回目以降は、前回のセル範囲と重複しないようにすることです。
 
 

 
 
WinArrowさん
ご回答ありがとうございます。
アドバイスありがとうございます。
ヒントを頂いた所を検討してみます。

投稿日時: 17/10/06 09:33:43
投稿者: Dellcasio

ActiveSheet.Range("D" & i + 5 & ":D" & i + 7).Select
ここのコードを下方向の行ではなく、
列方向の右に移動させたいのですが、
Rangeプロパティで列移動のコードの書き方が
調べても、分かりませんでした。
Cellsだと範囲指定が出来ないと思うのですが。。。。?
ご指導を頂けないでしょうか
よろしくお願い致します。

回答
投稿日時: 17/10/06 09:47:39
投稿者: ピンク

>ここのコードを下方向の行ではなく、
>列方向の右に移動させたいのですが、
ActiveSheet.Range("D" & i + 5 & ":D" & i + 7).Select
            ↓
ActiveSheet.Range(Cells(6, i + 3), Cells(8, i + 3)).Select

投稿日時: 17/10/06 10:06:16
投稿者: Dellcasio

ピンク さんの引用:
>ここのコードを下方向の行ではなく、
>列方向の右に移動させたいのですが、
ActiveSheet.Range("D" & i + 5 & ":D" & i + 7).Select
            ↓
ActiveSheet.Range(Cells(6, i + 3), Cells(8, i + 3)).Select

 
ピンクさん
ご回答ありがとうございました。
うまく出来ました。
ありがとうございます。
 

投稿日時: 17/10/06 10:13:47
投稿者: Dellcasio

あと別の案ですが、3ヵ月先の予測値だけを隣のF列に移して行く様にして
D列は消去して行こうと考えていますが、
思うようにうまく出来ません
試行錯誤中なのですが、
こちらもご指導を頂けないでしょうか
 
Sub t()
 
For i = 1 To 10
   ActiveSheet.Range("D" & i + 5 & ":D" & i + 7).Select
    Selection.FormulaArray = _
    "= TREND(B" & i + 2 & ":B" & i + 4 & ",A" & i + 2 & ":A" & i + 4 & ",A" & i + 5 & ":A" & i + 7 & ")"
     Range("D" & i + 5 & ":D" & i + 7).ClearFormats
     Range("F" & i + 7).Value = Range("D" & i + 7).Value
Next i
End Sub
 
 

投稿日時: 17/10/06 10:35:28
投稿者: Dellcasio

Sub t()
 
For i = 1 To 10
 
   ActiveSheet.Range("D" & i + 5 & ":D" & i + 7).Select
   
    Selection.FormulaArray = _
    "= TREND(B" & i + 2 & ":B" & i + 4 & ",A" & i + 2 & ":A" & i + 4 & ",A" & i + 5 & ":A" & i + 7 & ")"
     
    Range("F" & i + 7).Value = Range("D" & i + 7).Value
      
    Range("D" & i + 5 & ":D" & i + 7).Delete
          
Next i
 
End Sub
 
 
3ヵ月先の予測値だけを隣のF列に移して行く様にして
D列は消去して行こうと考えていますが、
このコードを実行すると、F列のデータもD列のデータもすべて
消えてしまいます。
F列のデータは残るはずなのですが、データが消去されてしまいます。
なぜこうなるのか、ご指導を頂けないでしょうか
よろしくお願い致します。
 
 
 

回答
投稿日時: 17/10/06 11:53:26
投稿者: ピンク

Range("D" & i + 5 & ":D" & i + 7).Delete
   ↓
Range("D" & i + 5 & ":D" & i + 7).ClearContents
 
Delete と ClearContents の違い、学んでくださいね
 

投稿日時: 17/10/06 12:15:27
投稿者: Dellcasio

ピンク さんの引用:
Range("D" & i + 5 & ":D" & i + 7).Delete
   ↓
Range("D" & i + 5 & ":D" & i + 7).ClearContents
 
Delete と ClearContents の違い、学んでくださいね
 

 
ピンクさん
ご回答ありがとうございました。
出来ました。
もっともっと勉強して参ります。
ありがとうございました。