Formula, quotes, and variable
 

Can you help me with this code. If I type it in the cell, it works. But I
can't get the VBA code to work. I get 1004 error. It has to do with the
"Month" variable. It won't accept the text "Mo 1*" in the formula in VBA

Sub FillEarnedvalue()
Dim MonthNo As Integer
Dim Month As String


MonthNo = 1
Month = "Mo " & MonthNo & "*"

Sheets("Status").Range("k8").Select
Do While MonthNo <= Sheets("Estimate").Range("O2")
Month = "Mo " & MonthNo & "*"

ActiveCell.FormulaR1C1 = _
"=(SUMIFS(Estimate!R[31]C23:R[31]C76,Estimate!R5C23:R5C76,""*Est
Hrs"",Estimate!R5C23:R5C76," & Month & "))+RC[-1]"
ActiveCell.Offset(0, 1).Select

MonthNo = MonthNo + 1
Loop
End Sub

Formula, quotes, and variable
 

Month is a name used by the system, you cannot adapt it, only use it, use
strMonth or MonthName or something like that instead.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Pam" wrote:

Can you help me with this code. If I type it in the cell, it works. But I
can't get the VBA code to work. I get 1004 error. It has to do with the
"Month" variable. It won't accept the text "Mo 1*" in the formula in VBA

Sub FillEarnedvalue()
Dim MonthNo As Integer
Dim Month As String


MonthNo = 1
Month = "Mo " & MonthNo & "*"

Sheets("Status").Range("k8").Select
Do While MonthNo <= Sheets("Estimate").Range("O2")
Month = "Mo " & MonthNo & "*"

ActiveCell.FormulaR1C1 = _
"=(SUMIFS(Estimate!R[31]C23:R[31]C76,Estimate!R5C23:R5C76,""*Est
Hrs"",Estimate!R5C23:R5C76," & Month & "))+RC[-1]"
ActiveCell.Offset(0, 1).Select

MonthNo = MonthNo + 1
Loop
End Sub

Formula, quotes, and variable
 

Thank you. That is very good to know; however, I still get the same error.

"John Bundy" wrote:

Month is a name used by the system, you cannot adapt it, only use it, use
strMonth or MonthName or something like that instead.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Pam" wrote:

Can you help me with this code. If I type it in the cell, it works. But I
can't get the VBA code to work. I get 1004 error. It has to do with the
"Month" variable. It won't accept the text "Mo 1*" in the formula in VBA

Sub FillEarnedvalue()
Dim MonthNo As Integer
Dim Month As String


MonthNo = 1
Month = "Mo " & MonthNo & "*"

Sheets("Status").Range("k8").Select
Do While MonthNo <= Sheets("Estimate").Range("O2")
Month = "Mo " & MonthNo & "*"

ActiveCell.FormulaR1C1 = _
"=(SUMIFS(Estimate!R[31]C23:R[31]C76,Estimate!R5C23:R5C76,""*Est
Hrs"",Estimate!R5C23:R5C76," & Month & "))+RC[-1]"
ActiveCell.Offset(0, 1).Select

MonthNo = MonthNo + 1
Loop
End Sub

Formula, quotes, and variable
 

I finally found some help at
http://www.microsoft.com/technet/scr...6/hey0713.mspx. I had to modify my formula to:

ActiveCell.FormulaR1C1 = _
"=(SUMIFS(Estimate!R[31]C23:R[31]C76,Estimate!R5C23:R5C76,""*Est
Hrs"",Estimate!R5C23:R5C76," & Chr(34) & "Mo " & MonthNo & "*" & Chr(34) &
"))+RC[-1]"

"Pam" wrote:

Thank you. That is very good to know; however, I still get the same error.

"John Bundy" wrote:

Month is a name used by the system, you cannot adapt it, only use it, use
strMonth or MonthName or something like that instead.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Pam" wrote:

Can you help me with this code. If I type it in the cell, it works. But I
can't get the VBA code to work. I get 1004 error. It has to do with the
"Month" variable. It won't accept the text "Mo 1*" in the formula in VBA

Sub FillEarnedvalue()
Dim MonthNo As Integer
Dim Month As String


MonthNo = 1
Month = "Mo " & MonthNo & "*"

Sheets("Status").Range("k8").Select
Do While MonthNo <= Sheets("Estimate").Range("O2")
Month = "Mo " & MonthNo & "*"

ActiveCell.FormulaR1C1 = _
"=(SUMIFS(Estimate!R[31]C23:R[31]C76,Estimate!R5C23:R5C76,""*Est
Hrs"",Estimate!R5C23:R5C76," & Month & "))+RC[-1]"
ActiveCell.Offset(0, 1).Select

MonthNo = MonthNo + 1
Loop
End Sub