Hello dailey,
Can you share a sample workbook with your exact requirement. I went through
your code.. But was unable to get what exactly you are looking for. If you
share a workbook and explain the result you want..probably it would be easy
for this group members to respond.
Thank you!!
Regards,
Praveen
On Mon, Jan 18, 2010 at 7:33 PM, 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
>
>
--
----------------------------------------------------------------------------------
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
