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
