Can't you use the same offset and movement code... i.e. End(xlUp) and
calc two SUMIFs functions?
SUMIF(c10:cN,"COGS",e10:eN)
SUMIF(c10:cN,"Sales",e10:eN)
Dim FirstRow As Long
Dim LastRow As Long
FirstRow = 10
LastRow = Range("A65536").End(xlUp).Offset(0, 0).Row
Range("aa1").Formula = "'COGS = "
Range("ab1").Formula = "=SUMIF(A" & FirstRow & ":A" & LastRow &
",""COGS"",C" & FirstRow & ":C" & LastRow & ")"
Range("aa2").Formula = "'Sales = "
Range("ab2").Formula = "=SUMIF(A" & FirstRow & ":A" & LastRow &
",""Sales"",C" & FirstRow & ":C" & LastRow & ")"
On Jan 18, 6:03 am, dailem <mdai...@xomox.com> wrote:
> I'm relatively new to VBA programming, but what I want to do is go
> through a range starting at a10 through the last record listed (varies
> in # of records, but always together). If the field in column a =
> "cogs", then I want to count it as +1. Eventually I'm trying to
> create to sum formulas- one to add up all of the records tagged w/
> "cogs", the other formula to add the rest of the data. Here is a
> sample of what my data looks like:
>
> COGS 43,270.56
> COGS 7,386.23
> COGS 1,386,100.52
> COGS 882,588.83
> Sales 6,458.92
> Sales 3,301.74
> Sales 244,898.99
>
> ....there probably is a better way of creating the sum formulas, but
> basically I want to go through the entire range, then create two sum
> formulas in other cells which total all items tagged as COGS & then
> all Sales items. My code is below:
>
> Dim j As Long
> Dim Count1 As Long
> Dim Count2 As Long
> Dim LastRow As Long
> Count1 = 0
> Count2 = 0
> Dim R1 As Range
>
> 'find last row #
> LastRow = Range("A65536").End(xlUp).Offset(-1, 0).Row
>
> 'set initial point for range eval
> Set R1 = Range("A10")
> R1.Select
>
> For j = 10 To LastRow
> If ActiveCell.Value = "COGS" Then
> Count1 = Count1 + 1
> End If
> Count2 = Count2 + 1
> ActiveCell.Offset(Count2, 0).Select
> Next
>
> ***after this code, I try to create my two formulas later w/ the code
> below. It intially seemed to be working correctly, but now when I run
> the code I get sum formulas which don't even resemble my code below.
> For example, the latest formula returned for the first item below is
> (Sum(c4:c10)..which I don't understand at all, since my first value
> SHOULD be c10 EVERY TIME. PLEASE HELP!!!!!!!! I'm at my wits end.
>
> 'formula for COGS import total
> Range("aa1").Formula = "=Sum(C10:C" & (Count1 - 2) & ")"
> 'formula for Sales import total
> Range("aa2").Formula = "=Sum(C" & (Count1 - 1) & ":" & "c" & (LastRow
> - 1) & ")"
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
