I need to use the last row of data in a formula

I'm writing a macro to calculate the coefficient and intercept of a log curve

the following is what the equation should look like in the cell:
=INDEX(LINEST('Expect Database Search'!$Q$3:$Q$90,LN('Expect Database 
Search'!$G$3:$G$90)),1,1)

however in VB I have to write it in this format:
Range("B2").Formula = "=INDEX(LINEST('Expect Database 
Search'!R3C17:R90C17,LN('Expect Database Search'!R3C7:R90C7)),1,1)"

My problem is that the data set is never the same so I may have 100 rows or 
1000 or anything in between.  Is there a way to write this so that the 
formula will find the last row of data and use that row number instead?

I've found examples of how to find the last row and the following is what I 
have used in my code:
  Dim LastRow As Long
  Sheets("Expect Database Search").Select
  LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

but how do I now use this is my formula?

thank you for any insight you may provide
0
Utf
6/3/2010 3:59:49 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
683 Views

Similar Articles

[PageSpeed] 12

Dim lngRow As Long
lngRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("B2").Formula = "=INDEX(LINEST('Expect Database Search'!$Q$3:$Q$" & _
lngRow & ",LN('Expect Database Search'!$G$3:$G$" & lngRow & ")),1,1)"


-- 
Jacob (MVP - Excel)


"vero" wrote:

> I'm writing a macro to calculate the coefficient and intercept of a log curve
> 
> the following is what the equation should look like in the cell:
> =INDEX(LINEST('Expect Database Search'!$Q$3:$Q$90,LN('Expect Database 
> Search'!$G$3:$G$90)),1,1)
> 
> however in VB I have to write it in this format:
> Range("B2").Formula = "=INDEX(LINEST('Expect Database 
> Search'!R3C17:R90C17,LN('Expect Database Search'!R3C7:R90C7)),1,1)"
> 
> My problem is that the data set is never the same so I may have 100 rows or 
> 1000 or anything in between.  Is there a way to write this so that the 
> formula will find the last row of data and use that row number instead?
> 
> I've found examples of how to find the last row and the following is what I 
> have used in my code:
>   Dim LastRow As Long
>   Sheets("Expect Database Search").Select
>   LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
> 
> but how do I now use this is my formula?
> 
> thank you for any insight you may provide
0
Utf
6/3/2010 4:10:49 PM
Hi,

Maybe this way

Dim LastRow As Long
Set sht = Sheets("Expect Database Search")
  
LastRow = sht.Cells(Rows.Count, "Q").End(xlUp).Row
sht.Range("B2").Formula = _
"=INDEX(LINEST('Expect Database Search'!$Q$3:$Q$" & _
LastRow & ",LN('Expect Database Search'!$G$3:$G$" & LastRow & ")),1,1)"

-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"vero" wrote:

> I'm writing a macro to calculate the coefficient and intercept of a log curve
> 
> the following is what the equation should look like in the cell:
> =INDEX(LINEST('Expect Database Search'!$Q$3:$Q$90,LN('Expect Database 
> Search'!$G$3:$G$90)),1,1)
> 
> however in VB I have to write it in this format:
> Range("B2").Formula = "=INDEX(LINEST('Expect Database 
> Search'!R3C17:R90C17,LN('Expect Database Search'!R3C7:R90C7)),1,1)"
> 
> My problem is that the data set is never the same so I may have 100 rows or 
> 1000 or anything in between.  Is there a way to write this so that the 
> formula will find the last row of data and use that row number instead?
> 
> I've found examples of how to find the last row and the following is what I 
> have used in my code:
>   Dim LastRow As Long
>   Sheets("Expect Database Search").Select
>   LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
> 
> but how do I now use this is my formula?
> 
> thank you for any insight you may provide
0
Utf
6/3/2010 4:10:59 PM
Thank you Jacob & Mike, it works perfect!

"vero" wrote:

> I'm writing a macro to calculate the coefficient and intercept of a log curve
> 
> the following is what the equation should look like in the cell:
> =INDEX(LINEST('Expect Database Search'!$Q$3:$Q$90,LN('Expect Database 
> Search'!$G$3:$G$90)),1,1)
> 
> however in VB I have to write it in this format:
> Range("B2").Formula = "=INDEX(LINEST('Expect Database 
> Search'!R3C17:R90C17,LN('Expect Database Search'!R3C7:R90C7)),1,1)"
> 
> My problem is that the data set is never the same so I may have 100 rows or 
> 1000 or anything in between.  Is there a way to write this so that the 
> formula will find the last row of data and use that row number instead?
> 
> I've found examples of how to find the last row and the following is what I 
> have used in my code:
>   Dim LastRow As Long
>   Sheets("Expect Database Search").Select
>   LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
> 
> but how do I now use this is my formula?
> 
> thank you for any insight you may provide
0
Utf
6/3/2010 4:24:19 PM
Reply:

Similar Artilces:

Help with formula #18
Hi All: I am WAY over my head here, and you all have always been great help, so hopefully someone can help with this. I had someone write this for me to use as an index at the top of a spread sheet. It works beautifully . The problem is I would like to move this index to sheet 2 of the same spreadsheet and when I copy and paste it, it does'nt work. Is there anyone who can modify this to work as an index in sheet 2 and will search sheet 1 for the results? TIA Keith =IF(#REF!="","",IF(ISNA(MATCH(#REF!,$H$31:$H$65536,0)),"",HYPERLINK("#"&C...

Help needed with Money 2003 files
Hi there, I've been using money 2003 for years even when upgrading to new PC's as I had the original disk. I've just had to rebuild my hard drive and now cannot find the disk anywhere so cannot install Money. As you cannot now buy Money, I've tried downloading the trial version of Money so that I can access all the account details/balances but it says that they're not compatible with earlier versions of money. Is there anything that I can do to get these files open or have I lost all my records for the last 6 years? I'd really appreciate your assistance. Jayne EggHead...

Using Blackberry to update CRM
I'm almost positive that this will work, but I'd like someone out there to confirm it. If a user has a Blackberry that connects to Exchange using BlackBerry's server integration software, and that user edits a record that syncs to Exchange, will that change be reflected in CRM 3, assuming that the record is tagged for tracking in CRM? I can't see why it wouldn't work, but I want to make sure. Thanks for any feedback! -Eric. Sorry this will not work. YOu might want to check out tendigits.com for a full blackberry integration to CRM ==============================...

Using Journal to track Project Correspondence ??
I've been trying to read up on the Journal feature, but I'm not sure how I can get it to "compartmentalize" activities related to a project. How would I do this? TIA, Bill Allen You can try using Categories on the journal entries "Bill Allen" <T.W.Allen@cox.net> wrote in message news:sSKef.7316$om5.3819@dukeread04... > I've been trying to read up on the Journal feature, but I'm not sure how I > can get it to "compartmentalize" activities related to a project. > > How would I do this? Hi, Vince. Thanks for your respo...

keyboard command used to select a range of nonadjacent cells?
does anyone know the keyboard command used to select a range of nonadjacent cells? 1)Use the arrow keys to select the first cell of the range 2)Hold down the [Shift] key while you extend the selection using the up, down, left, right arrow keys. 3)After the range is selected, and while still holding down the [Shift] key, press the [F8] key (they release all keys). You will see the word 'ADD' in the lower right of the status bar. Repeat above steps until done selecting non-contiguous ranges. Does that help? •••••••••• Regards, Ron "Jessizzle123" wrote: > does an...

Installing and using Office: MAC 2008 Home and Student Addition
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) My husband recently purchased a new MacBook Pro. He gave me his MacBook Air. Office Home and Student addition was installed on his old computer (MacBook Air), and was loaded on the new computer. I purchased my own copy of Office: MAC 2008 Home and Student and installed it on the MacBook Air but can't use when my husband is using his version. The product keys are obviously different, but when he's writing in Word, I cannot. The message is that two people can not use the program at once who are on the same network. What do I do so ...

Push single contact field data into prepared excel workbook
I am new to macros but ameager to get some formulated to help run my small business. I have been trying to find some code which suits my requirement of pushing signgular fields from a contact into a pre-prepared excel book which is loaded with the next stage of macro-powered automation. I recieve enquiries both via email and by phone. I use my blackberry to save phone enquiries to address book and Anagram for Blackberry to capture the required contact data from our email enquiry form. Both are then synchronised with my Outlook contacts. Either from the point of creation, or through ...

the last one to three characters of Y-axis label cannot be display
I am using Sony VAIO laptop (Model PCG-K23). The y-axis labe cannot be display fully in Excel 2003, Excel XP, and even Excel Work that came with this laptop. The Sony customer care guys attribute this problem to MS software problem. Does any one know how I can solve this problem? I have updated my Office2003, Window XP, and even graphic driver (ATI Radeon IGP). ...

Excel 2003 with SP3: border around active cell not moving when using keyboard?
When I use the arrow keys on the keyboard, the border around the active cell does not move to the next cell. Only the horizontal/ vertical scroll bars move. This started happening 2 days ago. Any ideas what options I may have accidentally toggle? Your SCROLL LOCK key is on. Press it again to turn it off. The key is typically labeled "ScrLk" and is above the Insert/Home/PageUp buttons. Most keyboards have an LED indicator lamp that is illuminated when Scroll Lock is on. Also, Excel will display "SCRL" in the lower right corner of the main Excel window when Scroll Lock ...

Clear rows where field contains a negative sign
I have a procedure called that looks for a negative sign (*-*) in the D column for all records. If it exists, the entire row is deleted. The procedure below works well for this situation, BUT when there are no records with the negative sign in the D column it deletes the first record in row 1. The Range somehow is set for the value in the D column for row 1. I have been unable to stop this so far. Does anyone have a way to not delete row one if the filter is not met? Here is the procedure: Sub CleanCancelledChks() Dim r As Range With ActiveSheet ActiveSheet.Columns(&...

Mapping Geographic Data
I want to create a data map and I need to have Microsoft Map installed. Apparently Microsoft Map was not installed when our IT Department installed Excel (I am running Excel 2003). I believe I ahve to rerun Setup to install Microsoft Map however my IT Department says they can't find Microsoft Map in the Setup file. Can any one help me tell them how to locate Microsoft Map in the Setup file? Hi, Map was dropped as a free add on to excel after xl2000, I think that's the version. Anyhow to get Maps in xl2003 you will need to buy the standard alone program. Cheers Andy Mike wro...

Need help with using a bar graph in a report
I have a report that separates information about representatives by manager. I need the graphs to give a quick summary on a few stats from each rep but only show for that manager's team and not all information. For example: Manager A has RepA, RepB, and RepC under him. Each rep has Aux1, Aux2, Aux3 information which needs to be displayed. My goal is to show Manager A with his Reps A,B, and C of their Aux1, 2, and 3. Then, under the next manager Header I want Manager B to show Rep D, E, and F with their Aux1, 2, and 3 information. Etc thru Manager G. Please help... Th...

Formula problem #9
Hi, I have entered a small formula in a cell =A1+B1 the result is also fine but if i have edit the formula i have to again type the entire thing. Is there a shortcut to this where i can easily do the editing of the formula, i guess i am missing something out here. Hi, I guess you are not able to see the formula in the formula bar if it is so then go to Tools|Options|View|check the formula bar check box|ok -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Niroo" wrote: > Hi, > > I have entered a small formula in a cell ...

Linked cells, make the data go hard manually
I'd like to convert a cell linked to a previous day to hard numbers once a day. Is that possible? By 'hard number' you mean one that is no longer linked, you could copy, then paste as, paste value. HTH, Carole O "Johnny" wrote: > I'd like to convert a cell linked to a previous day to hard numbers once a > day. Is that possible? ...

how to find used region with macro
Hi, sorry if this obvious or has been asked recently. I have a macro that goes through all rows and all columns in a selection. If a row or a column is hidden, the font of that row or column is changed. A friend wants to use the macro in a Workbook with many sheets. He asked if I could change it so that he does not need to do a selection on every sheet before he runs it. I imagine that a nested loop through all rows and all columns will take a long time? I�ve tried to figure out how to get the range defined by the last row and the last column filled with data. First I tried with Current...

Subform Data Entry Problems
I have a subform based on a query linked to the Form by "ContactID". The query works fine when run independent of the subform. I am able to enter all data fields and where necessary the autonumber function assigns properly. But when I try to use the subform all records related by my table "tblADDRESS" do not allow data entry (the fields are not locked). I imagine the ContactID Master/Child link in some way is interfering with the query in Form view, but I've tried every variation of join properties within the Query for the Subform and in the Query for the main...

Avoid pasting over hidden rows
Hi All I have a quick question in regards to pasting data over a range with hidden rows. Is it possible to paste data over a range of cells and avoid pasting data in hidden rows? Let's say sheet2 has data in range A1:B5 and row 2 is hidden. Let's say I copy range A1:B4 from Sheet1 and paste on Sheet2 starting in cell A1. I would like the data to paste over rows 1,3,4 and 5. (skip hidden row 2) I'm pretty sure the answer is no but I thought I'd ask anyways since there are some clever folks out there who might find a way. (Without using VBA) Thanks in advance. W...

Printing Autofilter Data
How do I print Autofilter data onto one sheet instead of a few lines on say 20 pages? In page Setup, select fit to print and select 1 page wide and 1 page high. -- Regards, Tom Ogilvy "Confused" <Confused@discussions.microsoft.com> wrote in message news:2C7DCB67-06B2-429C-99D9-D2B3A40FEF4A@microsoft.com... > How do I print Autofilter data onto one sheet instead of a few lines on say > 20 pages? Thank you! "Tom Ogilvy" wrote: > In page Setup, select fit to print and select 1 page wide and 1 page high. > > -- > Regards, > Tom Ogilvy >...

Prevent functions from changing when new rows are added
I have a worksheet configured as a timesheet, where each row represents a task performed. For any given day, I track the task(s) performed and the hours spent on each task, and there are many functions in the worksheet. The worksheet contains the following columns: Column A: blank column (not used for anything). Column B: series of dates (with a blank row in between each date). Column C: tasks performed, manually entered. Column D: hours worked on the task. Column E: hours worked for the week. There's a function in each cell where, if it's Sunday, a sum of the week's...

Parameter Query using date ranges
I am creating a parameter query where I want to pull data between 2 dates. In the date criteria of my query, if I write between #01/01/2008# and #01/31/2008# +1, I will get all data for January. If I use a form to enter the dates and have Between [Forms]![autoexec]![start] And [Forms]![autoexec]![end] +1 in the date criteria, I get an error. Does anyone know what I should put in the criteria to make the query work? Are you entering your dates as m/d/y? What happens if you try: DateAdd("d", 1, [Forms]![autoexec]![end]) Is the form open? -- Duane Hookom Microsoft Access...

Fill null fields from last record non-null in that field
Access 2000 -- I have a database table which is built from an import from a text file. I want to fill blank fields in records from the last previous record that has a value in that, and then when Access encounters a new record with a value in it, have this pasted into the fields below it. Now, it looks like this: 1 "0010099" 2 3 5 "0010100" 6 7 8 9 10 "0010101" 11 12 I want it to look like this: 1 "0010099" 2 "0010099 3 "0010099 5 "0010100" 6 "0010100" 7 "0010100" 8 "0010100" 9 "0...

Can't open database window using shift key...
Hi there, I have an Access database that I distribute to my client on CDROM. The start up options of the database are set so that the database window is not visible to the users. When the database is updgraded the database manager copies it from the CDROM to the relavant PC and then uses the shift key on opening in order to display the database window and link to the tables in the existing back-end. This procedure has been done many many times without a problem... BUT last week he found that holding down the shift key whilst opening the database no longer opened the database window and leave...

populating from sheets -- and PLEASE don't tell me I need VB!
Stupid newbie question, Excel 2003: I have a 2 sheet document. SOME of the info from Sheet 1 needs to appear on Sheet 2. For example, Sheet 1 Column G values need to be populated to Sheet 2 as Column B, Column H needs to be Column D, etc. Is there any way to automate this without having to write a VB script? I know a macro could kind of do it, but I don't think it could take all the parameters, since Sheet 1 is constanly being altered (i.e., new values, etc.). Oh, and all rows are relational so that G3 value from Sheet 1 when it goes to Sheet 2 B17 also has to have H3 from Shee...

Suggestion for using CStringArray with Unicode, mulit-byte, ATL &
Hi, I couldn't find the "development" group so i chose this one. I am trying to bring some code that has the CStringArray in it from a Multi-Byte project into a Unicode project and I keep getting errors I cannot resolve. Scenario: Project type: DLL Use MCF: Shared DLL Use ATL: Not using ATL Min CRT Use in ATL: No Char set: Unicode added afxcoll.h to stdafx.h as suggested elsewhere #include <afxcoll.h> #include "resource.h" #include <atlbase.h> #include <atlcom.h> I get the following error; Error 247 error LNK2005: _DllMain@12 already defined in Te...

Last value in column before blank
Hi, My data in one column A:A - Where V100 &V101 are product codes, dates run down the column and Blank is a blank cell befor each new product code. V100 1-Sep-09 2-Sep-09 Blank V101 1-Oct-09 5-Oct-09 10-Oct-09 Blank Question is how do i return the date value for the last entry (eg before the blank) for each product code I want to find. Note also the last date entry is always the max value date. Eg V100 = 2-Sep and V101 =10-Oct. Thks Craig One formulas play ... Assume your source data running in A2 down Put these in B2:D2 In B2: =IF(LEFT(A2)="V",A2,IF(A2=&...