Thanks Paul!!!
Well, I wrote the whole code except for the last line
ActiveCell.Offset(0, 1).Formula = "=sum(" & Format(FBTFirstrow, "H#") &
":" & Format(FBTlastrow, "H#") & ")"
All I want is the following one you explained. Thanks a lot for that.
ActiveCell.Offset(0, 1).Formula = "=sum(H" & FBTFirstrow & ":H" & FBTlastrow
& ")"
For the other parts, it works exactly the way I want. I just didn't know how
to use sum with FBTLastrow and FBTfirstrow. I'm home now.. I will try your
suggestion when I get to work on monday.
You said I could simply define the range extremely large. In fact, I can get
the result with Sumif and selection of the whole column. But I just wanted
to do this way.
And you are right about the following code: it will look strange..
ActiveCell.Value = "Total FBT Transfer to Fund"
It's because the codes I provide is not the whole but part. There are other
codes which come with the code.
All I want was the last code.. how to write code for sum function with a
variable starting row and a variable ending row.
Thanks paul.. I will try on Monday and let you know how it went.
On Fri, Jun 25, 2010 at 2:43 AM, Paul Schreiner <schreiner_p...@att.net>wrote:
> Is there other data in that column that you're NOT using?
> If there's not, you can simply define the range extremely large.
>
>
> Or... are you trying to find the last row with ANY data?
>
> that is to say:
> FBTlastrow = ActiveCell.SpecialCells(xlLastCell).Row
> will give you the row number of the last row of the entire spreadsheet
> that contains data.
>
> You said that you don't fully understand your code, so let's step through
> it:
>
>
> > Range("C:C").Find("Description", LookIn:=xlValues).Select
> this selects column "C" and searches for the string "Description",
> then selects this cell.
>
> > FBTFirstrow = ActiveCell.Offset(1, 0).Row
> this returns the row number of the cell immediately below currently
> selected cell
> (that has the word "description")
>
> > FBTlastrow = ActiveCell.End(xlDown).Row
> this has the effect of hitting the "end" key and the "down" arrow.
> as you observed, it will take you to the last entry before the first
> blank
> space.
> Instead, I would use:
> FBTlastrow = ActiveCell.SpecialCells(xlLastCell).Row
>
> >
> > ActiveCell.Value = "Total FBT Transfer to Fund"
>
> This is curious. Unless you've left out something, the currently
> selected cell
> contains the word "Description", and you're changing it to:
> "Total FBT Transfer to Fund"
>
> > ActiveCell.Offset(0, 1).Formula = "=sum(" & Format(FBTFirstrow, "H#")
> > & ":" & Format(FBTlastrow, "H#") & ")"
> This offset statement identifies the cell 0 rows down and one column to
> the right
> and inserts a formula.
> The problem is that this is an improper use of the "format" function.
> In the formatting expression, "H" means to display the Hour without
> leading "0"'s.
> since this isn't a TIME, I suspect you were trying to use this format
> statement
> as if it were similar to C++. It's not.
> How it may have worked properly for F and G is beyond me ..
>
> what I would use is:
> ActiveCell.Offset(0, 1).Formula = "=sum(H" & FBTFirstrow & ":H" &
> FBTlastrow & ")"
>
> but this whole thing looks suspicous.
>
> Because if you ran it for column "F", then the sum would be placed in the
> column "G" header.
>
> I suspect something more is going on.
>
> I think we need to know more about what you're trying to do
> and what you want it to look like when you're done...
>
> Paul
>
> ------------------------------
> *From:* Mark Kim <mark....@gmail.com>
>
> *To:* excel-macros@googlegroups.com
> *Sent:* Thu, June 24, 2010 7:15:11 AM
> *Subject:* Re: $$Excel-Macros$$ Re: Using sum function for Unknown range
>
> Can not use like that.
>
> All I know is which column i need to use. I do not know how long is the sum
> range and where to start.
>
> That's why I have FBTFirstrow and FBTlastrow . I need to have sum formular
> in the cell so i can verify the formula
>
> On Thu, Jun 24, 2010 at 3:22 PM, saggi <realsa...@gmail.com> wrote:
>
>> 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
>>
>
> --
>
> ----------------------------------------------------------------------------------
> 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
>
> --
>
> ----------------------------------------------------------------------------------
> 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
>
--
----------------------------------------------------------------------------------
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
