Excel上で関数を入れるコード For Next

質問

C列にExcel上で関数を上から下へ入れるコードを教えて下さい。

お世話になっています。
いつも質問ばかりですみません。

A列に項目、B列に日付が入っているリストがあります。
今回、C列にその月の第何周目かを求める関数
=WEEKNUM(B1)-WEEKNUM(DATE(YEAR(B1),MONTH(B1),1))+1
を入力したいです。
このエクセルシート上の関数のB1をB2、B3、B4と変化させて行きたいのですが

以下私のコードを書きましたが、 記載した時点で「"=WEEKNUM」から二行が赤字で表示されてしまいます。
------------------------------------------------------
Sub 私の書いた第何週目かをC列に求めるコード()

Dim i As Integer

For i = 1 To Range("a1").End(xlDown).Row

Sheets("Sheet1").Cells(i, "3").Formula = _
"=WEEKNUM(" & "(Cells(i, 2)"&")-WEEKNUM(DATE(YEAR(" & "(Cells(i, 2)" &),MONTH(" & "(Cells(i, 2)" & "),1))+1)"

Next i

End Sub

------------------------------------------------------

方法を教えていただけないでしょうか。
すみませんが、よろしくお願い致します。

回答No.5

kagakusuki · ベストアンサー

次の様なコードにすれば、For～Nextは必要ありません。

Sub QNo9234684_Excel上で関数を入れるコード_For_Next()

With Sheets("Sheet1")
.Range("C1:C" & .Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
"=WEEKNUM(RC[-1])-WEEKNUM(DATE(YEAR(RC[-1]),MONTH(RC[-1]),1))+1"
End With

End Sub

　またこの程度であれば、タイトルとEnd Subを除いて他の部分を1行で済ませてしまっても良い位です。

Sub QNo9234684_Excel上で関数を入れるコード_For_Next()

Sheets("Sheet1").Range("C1:C" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=WEEKNUM(RC[-1])-WEEKNUM(DATE(YEAR(RC[-1]),MONTH(RC[-1]),1))+1"

End Sub

お礼

RC[-1]この形式はマクロ記録で表示されますね。
Range("A" & Rows.Count).はカッコ内にカウントが入っていて画期的です。
ありがとうございます。

回答No.4

ushi2015

こんにちは
このようなケースの場合はループさせる必要も無い事を覚えた方がいいです。
Sub 私の書いた第何週目かをC列に求めるコード()
　 Dim i As Long
　 i = Range("a1").End(xlDown).Row
　 With Range("C1:C" & i)
　 　 .Formula = "=WEEKNUM(B1)-WEEKNUM(DATE(YEAR(B1),MONTH(B1),1))+1"
　 End With
End Sub

お礼

一発で入りました！
メモリを食わずにすむかもしれません。
for next と併用して、メモリを食いたくないときに使用したいです。
ありがとうございます。

回答No.3

mshr1962

"=WEEKNUM(" & Cells(i, 2) & ")-WEEKNUM(DATE(YEAR(" & Cells(i, 2) & "),MONTH(" & Cells(i, 2) & "),1))+1"

ですね。
Cels(i,2) の両端にある"は不要です。B列のi行を意味する変数自体を文字列にしてるため意味ありません。
それと(の数が意味不明に多すぎますし、最後の)も意味ありません。

お礼

動作確認できました。
ありがとうございます。
"は文字列と認識させてしまっていました。

回答No.2

f272

#1です。

さっきは適当なことを書いてしまいました。
Sheets("Sheet1").Cells(i, 3).Formula = _
"=WEEKNUM(B" & i & ")-WEEKNUM(DATE(YEAR(B" & i & "),MONTH(B" & i & "),1))+1"
これでよい。

お礼

動作確認できました。
ありがとうございます。

回答No.1

f272

"=WEEKNUM(" & "(Cells(i, 2)"&")-WEEKNUM(DATE(YEAR(" & "(Cells(i, 2)" &"),MONTH(" & "(Cells(i, 2)" & "),1))+1)"
ですね。「"」がひとつ足りません。

お礼

ありたとうございます。