Re: How to do this with macro?

oops. to find the last number in b and subtract a1

=INDEX(B:B,MATCH(9999999,B:B))-A1

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Don Guillett" <dguillett@gmail.com> wrote in message news:...
> Try this withOUT needing "xx"
> =MAX(B:B)-A1
>
> To sum col B
> =SUM(B2:OFFSET(B2,COUNTA($B:$B),0))
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "Arto K" <arto.koivisto@noadd.fi.inv> wrote in message 
> news:uz9Pn.17155$if1.13173@uutiset.elisa.fi...
>>I have several cells in one column, what include numbers (example B2-B4). 
>>After few empty cells, I have a cell (example B7), what include formula 
>>B4-A1.
>>
>> A1  B1
>> 1
>>       2
>>       3
>>       4
>> 5
>> 6
>> xx    =B4-A1
>>
>> Sometimes I add new numbers after cell B4 (example B5, B6 etc..) and 
>> sometimes I have to add new lines between last used cell, what include 
>> numbers and cells, what include the formula (example B7). When I add 
>> example three new line before B7, formula moves in cell B10. On the A7 is 
>> value xx. If B7 moves B10, also A7 moves A10.
>>
>> After changes example look like this:
>>
>> A1  B1
>> 1
>>       2
>>       3
>>       4
>>       5
>>       6
>>       7
>>       8
>>
>> xx    =B8-A1
>>
>> How to do macro, what find column A, cell what include text "xx" (example 
>> A10) and then it find last used cell in column B (example B8) and after 
>> that, the macro put formula in cell B10. The formula should be "last used 
>> cell in column B"-A1. Example in this case B8-A1.
>>
>> Ps. I use Excel 2007.
>>
>>
>>
>>
>>
> 

0
Don
6/7/2010 9:25:50 PM
excel.programming 6508 articles. 2 followers. Follow

0 Replies
1323 Views

Similar Articles

[PageSpeed] 45

Reply:

Similar Artilces:

Re-arranging table using pivot-table?
I have a worksheet with data organized somewhat like this: New York New York New York Albany Florida Miami Florida Orlando Florida Tampa Florida Jacksonville California Los Angeles California San Diego I need to convert this to a list of states and each city in the columns to the right of their corresponding state. Something like this: New York New York Albany Florida Miami Orlando Tampa Jacksonville California Los Angeles San Diego Any help is greatly appreci...

Re-install Outlook 2002
I am trying to re-install Outlook 2002 for my palm pilot after a crash. The installation will run until I get the message - "another version is already installed and must be uninstalled". The previous version was corrupt and I was unable to uninstall it. Now when I go into the control panel to add/remove the uninstall is no longer in the listed. Help! ...

macro to move from directory to directory
I am writing/recording a macro to make changes in 64 different spreadsheets in 64 different folders on a network. I am just learning vba. I don't know how to tell Excel to move through the 64 folders, test for the spreadsheet to be sure it's there, then make changes. Any suggestions? TIA Dave I would use a worksheet to help out. In A2:A65, I'd put the 64 folders to check. In B1, I'd put the filename to look for. Then something like this: Option Explicit Sub testme2() Dim myRng As Range Dim myCell As Range Dim myFileName As String Dim TestStr As String ...

Macro referencing data validation doesn't work
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hi - New to VBA &amp; the forum. <br><br>When I create a macro that references a data validation cell, the code does not acknowledge the value selected from the pull down menu. But, if the value is manually typed into the data validation cell, the code works fine. Any ideas? I've had PC users try the code with no problems. <br><br>I'm using the following code: <br> [Code]Private Sub Worksheet_Change(ByVal Target As Range) <br> If Not Intersect(Target, Range(&qu...

Re-Enable CRM User
Hello Everyone, I am trying to enable a disabled CRM user and I received the following error message. Any thoughts or suggestions would be greatly appreciated. [COMException (0x80040204)] Microsoft.Crm.Platform.ComProxy.CBizUserClass.Enable(CUserAuth& Caller, String UserId) +0 Microsoft.Crm.Application.Platform.SystemUser.Activate(String id) +38 Microsoft.Crm.Web.BusinessManagement.SystemUserDetailPage.changeState(Object sender, DataEventArgs e) +127 Microsoft.Crm.Application.Forms.DataEventProcessor.Raise(FormEventId eventId, FormState state, User user, String objectId,...

Macro #48
I would like to know I can I record a macro to automate a task without using Visual Basic. I also would like to know how to save this macro and what way this macro that I have saved is recorded. Thanks a lot for all your help Nadine: Macros ARE Visual Basic code. When you record a macro, it actually "writes" the VBA for you. Many simpler tasks can be recorded, but we'd really have to know what you want the macro to do before we could tell you whether it can be recorded or not. Please see: http://www.officearticles.com/excel/record_a_macro_in_microsoft_excel.htm ************...

RE:Excel will not open from a web link
When opening a link on a web page that has an Excel Spreadsheet embedded in it, the spreadsheet will not open up Excel but opens within another browser as an Excel file. Programming on webpage is correct, opens up on other machines just fine. ...

I need a macro to find cut and paste data to new cell
I have data that I have exported to excel that I need to reformat to be able to create a pivot table to check for duplicate entries. On importing the data which is in the form of journal entries the memo line is stting above the journal numbers in column D. I need to find all of the comments and cut and paste them to column C. The comments are not all the same but do contain the same word "To" in the comment. The journals are not all the same size and so the comment line does not appear at regular intervals. Any help appreciated as I am very new to VBA ...

Thanks Ken Wright but one more question RE saving data from circular references
Thanks heaps for that macro Ken Wright . I have run it and it works. However it loops forever. Can I set it up so that it could save dat from a certain number of iterations? Thanks again Michae -- Message posted from http://www.ExcelForum.com Hi Michael, If you stayed within your thread and did not change the subject Ken would see your reply as a reply to his reply and would see it a lot faster than when you start a new thread. Ken would probably see your post highlighted in RED. Also someone other than Ken might have been able to help you. (not me I avoid anything that hints of circul...

Re: exmerge error
I am getting the following error when trying to use exmerg can anyone please help *************************************************************** Microsoft Exchange Mailbox Merge Program, v4.00.02 Start Logging:February 27, 2004 20:04:3 *************************************************************** [20:04:31] Logging Level: Non [20:04:31] Reading settings from file 'C:\Program Files\Exchsrvr\BIN\EXMERGE.INI' [20:04:32] Error 8007203a opening an LDAP connection. ('LDAP://EXCHANGE/rootDSE') (CADRoutines::GetNamingContextData [20:04:32] Accessing Domain Controller 'PROFI...

Ron Bovey re: "Trim Selection" in ExcelUtilities
Cells in my Excel spreadsheet appear to be empty. However, when I use Edit/Go To/Special/Blanks, I get "No cells were found". In the past, Ron Bovey suggested going to the WWW.APPROS.COM website, download ExcelUtilities and then use the "Trim Selection" utility to remove the characters that are making the cells not empty. Since then, I've gone to the WWW.APPROS.COM website and downloaded the ASAP Utilities. However, I can seem to find the "Trim Selection" nor any utility that will do the same thing. Help! Try going to Rob (not Ron) Bovey's site: ...

Formula or Macro needed?
Hi, I need some help desperately, for reasons which are too complicated to go into right here and now, I need a formula or a macro to produce an excel spreadsheet of 16 columns and 16 rows where each row and each column contains the numbers 1 through to 16 without the same number repeated on any row or any column - I know it may sound a little tricky :eek: but I'm hoping somebody is up for a challenge like this and can help rather quickly - I'm not interested in the number of permutations or anything daft like that - I just need a solution. Thanks to anybody who knows and can post...

HELP... Macro challenge (not coding)
I'm working on a big project file for our whole department to use. I set up a macro that will make some steps easier for my teammates. But then i just realized (when i was testing something on one of their machines) that the macro is tied to the software on my machine, not on the Project file itself. Is there a way to associate the macro to the file show it shows up on any department computer? I've looked through a couple of books and can't find anything. The thought of rebuilding the macro on everyone's machine makes me a bit nauseous. Thanks! Hi, In ...

How do I get invisible lines & borders to re-appear?
In Publisher 2003, table grid lines, text box borders and autoshape borders have become invisible on-screen, although they do print out. Any ideas on how to remedy this? Recently installed Adobe PDF Maker which seems to have precipitated this, although it could be entirely co-incidental. Try updating your video drivers. -- JoAnn Paules MVP Microsoft [Publisher] "Ackerman & Co" <Ackerman & Co@discussions.microsoft.com> wrote in message news:0FFE40C2-D6A8-43AE-89E4-061EBB6501DB@microsoft.com... > In Publisher 2003, table grid lines, text box borders and auto...

re re re ????
nefertiti ??? ...

Re:
...

Re: Where should be the best place to stored Applcation Data Files?
"Giovanni Dicanio" <gdicanio@_NOSPAM_email_DOT_it> ha scritto nel messaggio news:... > BTW: this post by John Robbins is very interesting reading: > http://www.wintellect.com/CS/blogs/jrobbins/default.aspx copy-and-pasto - the correct link is this: http://www.wintellect.com/CS/blogs/jrobbins/archive/2008/07/10/in-defense-of-vista-and-the-challenges-facing-windows-7.aspx Giovanni And then there's this Vista security discussion from last Friday (cool date, 8/8/8): http://it.slashdot.org/article.pl?sid=08/08/08/1155208 -GB On Fri, 8 Aug 2008 18:50:23 +0200, ...

Re-enter payee information
I have been using MS Money to pay bills and frequent annoyance is when I type in the payee, the selection list never shows the payees. I type it in and have to re- enter the account number and everything. Doesn't make sense that i have to keep putting this in. Anyone know what I am doing wrong? I've checked these boards and found help with my passport problem but no entries on this issue. Money has two kinds of Payees. Explicit--those on the explicit list that shows in Accounts & Bills|Categories & Payees|Payees--and implicit--those that are recorded somewhere in a tra...

Macro Help #7
Hi, I would like to create a macro to find certain information in a large file. Ie I would like to click a marco button that would then use a pop up screen for you to type in your data you want to search and then click ok and the macro finds the information and returns it to you. Something like a filter. I know how to run macros but not sure how the get the pop up screen, any suggestions would be appreciated? xl2002 added an option to find in workbook. in xl2k, you could group your worksheets and do edit|find. But in all versions, you can use Jan Karel Pieterse's FlexFind: htt...

Excel macro 06-03-10
Hi, i need a macro to copy from one cell the text below and to paste only the contenet of the parenthesis. Invoice currency (USD): I need to paste only USD. The currency can contain "USD, EUR' CHF,...etc" Can this be done? Thanks! One way...to return the text Dim strData As String strData = "Invoice currency (USD):(ind)" MsgBox Split(Split(strData, "(")(1), ")")(0) -- Jacob (MVP - Excel) "puiuluipui" wrote: > Hi, i need a macro to copy from one cell the text below and to paste only the > contenet of th...

Re: Outlook locks up with a officelifeboathang
"Andy Lay" <58746@gmail.com> wrote in message news:... > Message-ID: <a7b660e6180d48ddae1e1df6c5775272@newspe.com> > X-Mailer: http://www.umailcampaign.com, ip log:72.24.97.26 > Newsgroups: microsoft.public.outlook.general > NNTP-Posting-Host: 22.bb.5446.static.theplanet.com 70.84.187.34 > Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl!newspe.com > Lines: 1 > Xref: TK2MSFTNGP01.phx.gbl microsoft.public.outlook.general:737044 > > Hi Swinglower- > > I have luck as you do :) A Chief Exec machine with this very issue....

Re: Share sub-folder in Inbox
I already grant the permission to right person and add secondary mailbox to the exchange account, in his outlook folder list, he can see the shared mailbox, but only mailbox not the sub-folder, if we share those sub-folders individually, then we can view under shared mailbox, but this shouldn't be the way. we want it sub-folder can be viewed and not be set one by one. any idea? thanks Henry "Sue Mosher [MVP-Outlook]" <suemvp@outlookcode.com> wrote in message news:%237hESO3PFHA.508@TK2MSFTNGP12.phx.gbl... Either you haven't completed the process of granting permission...

RE: Apply this critical update from M$
--wmhrdizzoa Content-Type: multipart/related; boundary="axyeonzy"; type="multipart/alternative" --axyeonzy Content-Type: multipart/alternative; boundary="innmlsikefhcqjc" --innmlsikefhcqjc Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to continue keep...

Re: How to delay ToolTip display time in CListCtrl Control
"Smallfrogs" <super_smallfrogs@hotmail.com> д���ʼ� news:... > Thanks > "Ali R." <nospam@nospam.com> д���ʼ� > news:snPYb.436$1V5.123111568@newssvr11.news.prodigy.com... > > ((CListCtrl > > *)GetDlgItem(IDC_MYLIST))->GetToolTips()->SetDelayTime(TTDT_AUTOPOP,5000); > > > > or if you have variable for your list > > m_MyList.GetToolTips()->SetDelayTime(TTDT_AUTOPOP,5000); > > > > that will set how long it stays up after it is displayed. > > > > Change TTDT_AUTOPOP with TTDT_INITIAL to change the...

Make a Macro on a Toolbar
Hi, I created a Macro but I want to have it show up on a toolbar. I know that you can create a custom toolbar but how can i add a macro (which I would need an icon) onto that toolbar or any toolbar? Thanks, Sean Sean Tools>Customize>Commands. Scroll down to "Macros" and select the smi;ey-face button. Drag it up to an existing toolbar. Right-click on it and "assign macro". Choose your macro from the dialog box. Note: you can edit the the smiley-face button. There are many other ways to do what you want, the above is the basics. Gord Dibben Excel MVP On M...