This array formula:

{=SUM(IF(MONTH(A4:A358)=MONTH(TODAY())-1,E4:F358))}
doesn't have to be placed into a name to be plopped into a cell.

And the formula that you have in your code doesn't look like the equivalent of
this formula.

And when you use:
...worksheets(shname).range("C2:" & rc & ")...
that rc variable is just a number.

It would be like using C2:1234

You would want some letter in that second portion of the address: C2:C1234

And when you have a number between 1-12, it already represents a valid month
number.

=month(somenumberbetween1and12)
will always return 1
1 is the same as Jan 1, 1900
2 is the same as Jan 2, 1900
....
12 is the same as Jan 12, 1900

All those dates are in January (or 1).

And I'm guessing that you don't want to subtract 1 from that month number like
in your worksheet formula sample.

With Array formulas, you don't enter the {}'s yourself--either manually or in
code. Manually, you'd use ctrl-shift-enter. In code, you'd use .formulaarray.

I'm not sure if this does what you want, but it may give you some ideas:

Option Explicit
Sub SalesTaxReport()

Dim RYear As Long
Dim RMonth As Long
Dim Shname As String
Dim StateTotal As String
Dim LastRow As Long
Dim RngWithMonths As Range
Dim RngToTotal As Range

Do
RYear = CLng(Application.InputBox _
(Prompt:="Enter a year between 2005 and 2050", _
Default:=Year(Date), Type:=1))
If RYear = 0 Then
Exit Sub 'give the user a way out??
Else
If RYear = 2005 _
And RYear <= 2050 Then
Exit Do
End If
End If
Loop

Do
RMonth = CLng(Application.InputBox(Prompt:="Enter a month (1-12)", _
Default:=Month(Date), Type:=1))
If RMonth = 0 Then
Exit Sub 'give the user a way out??
Else
If RMonth = 1 _
And RMonth <= 12 Then
Exit Do
End If
End If
Loop

Shname = "sales " & RYear
With Worksheets(Shname)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set RngWithMonths = .Range("A2:A" & LastRow)
Set RngToTotal = .Range("C2:C" & LastRow)
'or
'Set RngToTotal = .Range("E2:F" & LastRow)
'but not both!
End With

StateTotal = "=sum(if(Month(" & RngWithMonths.Address(external:=True) _
& ")=" & RMonth & "," _
& RngToTotal.Address(external:=True) & "))"

With Worksheets("Tax Report")
.Range("b3").FormulaArray = StateTotal
End With

End Sub






C Brehm wrote:

I can not get this line working, it will be assigned to a named range

Statetotal = "{=sum(if(Month(" & worksheets(shname.range("a2:" & rc & ")"
_
& "= month(" & Rmonth & ","
_& worksheets(shname.range("c2:" & rc & ")" & "}"

Also, what is the best way to assign the value that would be returned form
above formula to a cell? i.e...

With Worksheets("Tax Report")
.Range("b3").Formula = Stgross
End With

Sub SalesTaxReport()
' gets data for state and parish sales tax report
Dim Ryear As String ' holds year, each year has its own worksheet
Dim Rmonth As String 'holds month, Column A of Ryear worksheet
'contains dates formated .NumberFormat =
"m/d/yyyy;@"
Dim Shname As String ' holds sheet name to find data
Dim Statetotal As String 'holds formula for named range
Dim rc As Integer 'row count of worksheet Shname.range("a:a")
Dim MonthTotal As Currency

Ryear = ""
Rmonth = ""

While Len(Ryear) 4 Or Len(Ryear) < 4
Ryear = InputBox("Enter year for report")
'makes sure year is 4 digits

'need to add check for a valid year between 2005 and 2050

Wend
Shname = "sales " & Ryear
rc = Worksheets(Shname).Range("a:A").Row.Count

While Len(Rmonth) 2 Or Len(Rmonth) < 2
Rmonth = InputBox("Enter month of report")
'makes sure month is 2 digits

'need to add check for month between 01 and 12
' may want to make this a case statement for check of valid
'entry, then could be one or 2 digits
Wend

'{=SUM(IF(MONTH(A4:A358)=MONTH(TODAY())-1,E4:F358))}
'the above formula works in spreadsheet

' need to convert to string so it can me assinged to a named range

'the next line converts needed formula for assingment to a named
range
Statetotal = "{=sum(if(Month(" & worksheets(shname.range("a2:" & rc &
")" _
& "= month(" & Rmonth & ","
_& worksheets(shname.range("c2:" & rc & ")" & "}"

ActiveWorkbook.Names.Add Name:=Stgross, RefersTo:=Statetotal

With Worksheets("Tax Report")
.Range("b3").Formula = Stgross
End With

'more to be done here

End Sub

--

Dave Peterson