C
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
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