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
1988 Views

Similar Articles

[PageSpeed] 12

Reply:

Similar Artilces:

re:
Does anyone know if this is possible and if so, how it's done? I have to explain. Recently at work we stopped receiving some emails from one person at a company. All of her emails say almost exactly the same thing and in the past we have not had a problem but last week we did not receive three, and this week we did not receive four so far. Other people are copied in on the email and only people at my company are not recieving it. We have checked the server and with our service provider and the emails are nowhere to be found. The person later copies and pastes the original email sent and...

Re: Money 2003 Deluxe Budgetting Problem
I've setup a standard bill 3 times due to differing amounts and ended up deleting the monthly bill and transactions, only problem is that it still includes the budget amount and continually adds it, even though the re- occurring bill and associated transactiosn have been deleted. Any ideas would be much appreciated as my income budget category is growing by the day. I have removed and re-setup the category without any luck. Cheers Mark ...

Macro warning with no macros
I have a workbook that when I try to open it I get the warning tellin me it contains macros and asking whether I want to Enable or Disabl them. I put no macros, no VB scripts, nothing at all in the workboo other than standard Excel formulas and some charts. I developed it i Excel 2002, SP-2, but have moved it back and forth between tw computers, the other which has Excel 97. Regardless of whether Enable or Disable or change the security settings so as not to get th warning, once the workbook is open even if I go t Tools-Macro-Macros... it lists no macros. How can I find out what's cau...

USB Mass Storage Medium change and re-enumeration
Hi, I have a MFC mass storage application, it provides the login/logout function which switch between partitions, the function works well in Win2000/XP; Ex: the jump-to partition pop-up after login, but I found it doesn't work in Win98/ME, after login, the OS NOT try to do medium-change as in XP, because 98 doesn't issue TestUnitReady periodically as in XP, so the device have no chance to inform OS the medium-change event(switch partition), thus may cause blue screen by partition information mismatch sometimes. How to inform OS the medime-change event so the jump-to partition ...

Programmatically sign Excel VBA macro
My goal is to programmatically sign an Excel VBA macro. I have a certificate. I have code to programmatically create an Excel macro. Word 2002 has access to the Office SignatureSet collection (ActiveDocument.Signatures which prompts the user to select a certificate via the Add method of the SignatureSet collection, which is an interface--not a class--in the Office Interop PIA). Excel does not have a get_Signatures. Why not?! Who cares, I don't want to prompt the user to identify a certificate from the store anyway. Now, I'm thinking, if I had something like SignCode.exe fro...

Question RE: Publisher
Several years ago my father purchaced (legally) publisher 98, and has had it on our family computer. Recently the computer needed to be replaced because it was to slow to function comfortably. We still have the origional publisher disc, booklet, but lack the box and serial number. Is there anyway in which we can retrieve this? or get a new #? Curtis, if you still have the old computer, try running BeLarc Advisor. http://www.belarc.com/free_download.html It's free to individual users. On my system it tell me the serial number and keys of most of the software packages I have install...

what is the code to close a file for a macro
I need to close a file in a macro but I am not sure how to do it. I could use the following but when I use this a pop up screen says do you want to save the changes to the file. I don't want to save the changes. Is there any code to use that will eliminate the pop up asking me to save the file. ActiveWindow.Close ---- Celia Worbooks("Book.xls").Close SaveChanges:=False -- Regards Dave Hawley www.ozgrid.com "Celia" <Celia@discussions.microsoft.com> wrote in message news:99DDAB63-FAFE-4276-9EE7-0288AE465B4E@microsoft.com... >...

Macro is Excel to populate a word document #2
Guys, I appreciate if you could help me do the following: I have a table of data in excel and need to be able to click a button in excel that will run a macro and send that data to a word document (In the form of a table) Thats it Thanks a lot Andrew ...

Autofilter using macro
I'm trying to create a macro that will select a range of rows fitting a certain criterion, then insert today's date into an empty cell in each row that matches the criterion. I set up the macro to insert the formula into the top cell of the range and then copy the formula down the range. My problem is that I have to run this macro daily, and the range of rows changes every day. I set up the macro using today's report, so it contains today's ranges, but I need to alter the macro so that it selects the active range for each day. Here is the relevant portion of the macro: Select...

Macro-calling shape
Hello, can the actual macro-calling shape be identified when there are 2 shapes assigned to the same VBA-procedure ? Thank you in advance. Regards, hglamy Try: msgbox application.caller You'll see the name of the shape that's calling the macro... You can use: dim myShape as shape set myshape = activesheet.shapes(application.caller) then use all the properties for that shape msgbox myshape.name & vblf & myshape.topleftcell.address & ... "H.G. Lamy" wrote: > > Hello, > > can the actual macro-calling shape...

Macros #6
Please can someone give me the definition of a macro? Thanks. >-----Original Message----- >Please can someone give me the definition of a macro? >Thanks. >. I hope this helps. >If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Visual Basic module and can be run whenever you need to perform the task. You record a macro just as you record music with a tape recorder. You then run the macro to repeat, or "play back," the commands. Regards, Wayne >----...

Macro to affect all sheets
I want to select all sheets in a workbook, insert a column, and put a formula in that column. It works fine when doing it manually, but if I try to have a macro do this, it only affects one sheet. Is there any way to do this? The general principal is shown in the following code. Pls note: the code places the formula: =2+2 in cell F1 of every sheet. I'm sure it will b no problem 4 u yo change it 4 your needs. ----------------------- Sub All_Sheets() For Each SH In ActiveWorkbook.Sheets SH.[F1].Formula = "=2+2" Next End Sub ------------- Micky &...

Outlook vs. Lotus Notes in macros
One problem... we use Lotus Notes can I just replace LotusNotes where it says Outlook? "Gary Brown" wrote: > Use Windows Scheduled Tasks to run your macro at specific times/days. > The basics of the macro would be something like this.... > > '/========================================/ > ' Sub Purpose: example of how to send a file attachment > ' Requires reference to Microsoft Outlook > '/========================================/ > ' > Public Sub SendTheEmail() > Dim olApp As Outlook.Application ...

Macro that unhides a sheet and takes the user to that sheet
Hello, I have a macro on sheet 'inputs' that unhides another sheet called 'calculations' and takes the user there from when they click on the button. The codes is as follows: Sub Reveal() With ThisWorkbook If Worksheets("Calculations").Visible = False Then Worksheets("Calculations").Visible = True Worksheets("Calculations").Activate End If End With End Sub This works well but I would now like to add a feature. I would like the same button to take the user from the 'inputs' sheet to the 'calculations' sheet ev...

Visual basic code with macros
I want to put a named variable into a Header or footer and be able to format the variable to get larger font. I can get the variable into the footer through macro code but not add the formatting code. Is this possible? Can anyone help? Thanks. I recorded a macro when I did it manually. This was the bit I kept: With ActiveSheet.PageSetup .CenterFooter = "&20asdfasdf" End With I could modify that to look like: Dim myStr As String myStr = "This is a test!" With ActiveSheet.PageSetup .CenterFooter = "&20" &...

Template with macro in Excel 2007
Hello, I have an Excel 2007 template with macro. When a user opens this document and wants to save it, the default format is an Excel document without macro! That's pitty. However, when a user use a Sent document in a email, the format is correct. That seems a bit schizophrenic for me. I have SP1 for Office 2007 installed. I don't want to change an Excel global settins to default format with macro. So, how to enforce Excel 2007 to have default format document with macros when my teplate with macro is opened. Thanks Hi Jirka I report this to MS a few months ago. I hope they ch...

Macros to Browse for Files
I am trying to summarise information held in various excel files. I want to browse for a file by clicking an object 'button' - th various pieces of information held in the file I've selected are to b summarised in the sheet. Ultimately, I am trying to collate information from various excel file and summarise them in a new file. Can this be done? E.G I have twenty different excel files. Each of the files is setu the same way so that the "Bill total" is in the same cell. So, th twenty files all have $10 as a total - the summary file (which I a trying to create) would...

using hyperlinks or macro buttons to time stamp
I am currently using this code to time stamp one cell, Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("A2:CL2"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy h:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Can I do this to two cells, using a hyperlink, as in: a1=start timer a2=end timer It is being used to track call center transact...

Re: How can I search for a ? in Publisher?
I just sent my suggestion to the shredder. <G> -- Don - Vancouver, USA "May your shadow be found in happy places." - Native North American "Don Schmidt" <Don Engineer@PNB.Retired_1987> wrote in message news:... >I see you problem; don't know if your search can be done in Publisher but >here's a way of finding the "?s" in your file. > > Save the file as a PDF and do the search in your PDF viewer program. This > works for me using PDF-XChange to create the file and PDF-XChange Viewer > to do the searching....

Re: Saving CSV in Unicode?
I'm using Excel to write a CSV in UNICODE. If I simple save this file then all the unicode text is converted to ASCII '???' things. So I specify the file name like "test.csv" and specify "UNICODE Text" in FileType. Now when I open the file in Notepad, I find no comma but TABS as separator. If I open the file in Excel, it prompts me to select delimiter with which the file was saved. If I select anything except "Tab" then all the data is shown in single column that I've to break using "Text to Column" Feature. And you know my clie...

Re: Changing domain password via RPC over HTTP #3
Hi Mark, Do you mean that enabling changing password in OWA (on the RPC proxy server) is equivalent to enabling changing password via RPC/HTTP? Thanks! "Mark Arnold [MVP]" <mark@mvps.org> wrote in message news:s2lsm013co202g0tjifohnfa6ibvuick6t@4ax.com... > On Thu, 14 Oct 2004 16:25:09 +0800, "Shel" <shel@msnews.forum.com> > wrote: > > >Hi, > > > >Having a FE RPC/HTTP proxy server, and users connected via HTTP. > >The Exchange servers are in a Resource domain, and user accounts are in > >Accounts domain. > >These a...

"create mscrm.sql" error on 3rd (re-)install
I have installed CRM 1.2 on SBS2K3 twice as "advetureworks cycle" and "msdn subscriber" with only 90% success because of conflicts on the web site. I have removed the conflicts but now CRM installer errors trying to create the new database as (default) local service. My login has SQLServer db_creator permissions and I can create a DB, so I'm totally confused. Can I workaround this somehow ? What login needs to be given db_creator rights so Install can run te SQL scripts ? Hi Marcus, have you deleate the complete the old date from AWC? Have you delete the ...

How do I update Excel 2000 macros to work in Excel 2002?
Working in Excel 2002/XP on an Excel worksheet which was created in Excel 2000, I get an error message "Subscript out of range". How do I update the macro to work in Excel 2002? Hi normally the macro should work without a problem. Best to post the part of your code which generates this error -- Regards Frank Kabel Frankfurt, Germany "BobPetrich" <BobPetrich@discussions.microsoft.com> schrieb im Newsbeitrag news:9CF34907-0A45-4392-8F04-556EFB1F89E4@microsoft.com... > Working in Excel 2002/XP on an Excel worksheet which was created in Excel > 2000, I get a...

Macro to Print labels in Word
Does anybody know of an Outlook (2003) macro that has been written to print labels in Word (2003)? If there is one, it might be listed at http://www.slipstick.com/contacts/printlabel.htm . You can also just use a mail merge, you know. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Bob S" <drbobsing@nospam.com> wrote in message news:ehiHZJeXEHA.2952@TK2MSFTNGP12.phx.gbl... > Does anybody know of an Outlook (2003) macro that has been ...

Visual Basic macro run time error(13) type mismatch
Hi, I am having trouble to run the following VB macro. It basiclly fills one row of sheet with data extracted from Lotus Notes. Looks like VB can only make around 10 assignment statements. I have tried to comment out assignment statements at various places, the run time error always happened at around 9 or 10th statements. I don't know Excel and VB very much though. I inherited this application from someone I don't know. And this application runs fine on my old windows 2000 machine. But it failed on my newly upgrate windows XP pro machine. Anyone know what's going on here?...