If you are not using Dynamic Range then use simple Sum formula, Just
change range n column number
Sub SUM()
ActiveSheet.Select
ActiveCell.FormulaR1C1 = "=sum(R[-1]C:R[-22]C)"
Range("B23").Select
End Sub
On Jun 24, 7:58 am, Markkim <mark....@gmail.com> wrote:
> Hi
>
> I have a set of data which I need to sum up between unknown range..
>
> The following is the code I am using
>
> Range("C:C").Find("Description", LookIn:=xlValues).Select
> FBTFirstrow = ActiveCell.Offset(1, 0).Row
> FBTlastrow = ActiveCell.End(xlDown).Row
>
> ActiveCell.Value = "Total FBT Transfer to Fund"
> ActiveCell.Offset(0, 1).Formula = "=sum(" & Format(FBTFirstrow, "H#")
> & ":" & Format(FBTlastrow, "H#") & ")"
>
> The first paragraph - I assigned beginning of the row to FBTFirstrow
> and end of the row to FBTlastrow
>
> I can not use ActiveCell.End(xlDown) for sum range because there are
> blank cells in the columns which contains actual data. that's why I am
> getting FBTlatrow.
>
> Now Let's say FBTFirstrow is 50 and FBTlastrow is 80
>
> then, I like to insert a formula sum(H50:H80)
>
> And the code above I got it from somewhere in the internet, which I
> don't have full understanding..
>
> what's very odd is the code above works perfect for sum(F50:F80) or
> sum(G50:G80) - I simply change H# to F# or G#. But it doesn't work
> with H#. I don't understand. Why is it not working.
>
> one other question I'd like to ask is
>
> is there any better way of doing this?
>
> I have first row and last row. with this information, I'd like to sum
> up different columns.
>
> Cheers
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 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
