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
