Named Range Formula

[C Brehm]

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]

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]

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.

I know it does not look the same, but does the same job for the last month
in another workbook. I used it to help figure out what I wanted to do.

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

Beats head on keyboard. No wonder I never could get anything to work.

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.

I did in the other workbook but not here.

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.

Thanks, did not know that, but would siplify things a lot. I am trying to
self teach myself, but sometimes my head is just to plain thick.

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

Thank you, Was a great help and pointed me in a new direction that will be a
lot simpler.

 

[Dave Peterson]

Glad you got it working.

I know it does not look the same, but does the same job for the last month
in another workbook. I used it to help figure out what I wanted to do.


Beats head on keyboard. No wonder I never could get anything to work.


I did in the other workbook but not here.


Thanks, did not know that, but would siplify things a lot. I am trying to
self teach myself, but sometimes my head is just to plain thick.


Thank you, Was a great help and pointed me in a new direction that will be a
lot simpler.