How to do this with macro?

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
Arto
6/7/2010 4:59:05 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
3334 Views

Similar Articles

[PageSpeed] 38

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.
>
>
>
>
> 

1
Don
6/7/2010 9:21:25 PM
>> =MAX(B:B)-A1

This formula solve my problem. It's nice solution, thank you very much!



"Don Guillett" <dguillett@gmail.com> wrote in message 
news:%23wUxjdoBLHA.5848@TK2MSFTNGP06.phx.gbl...
> 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
Arto
6/7/2010 10:04:12 PM
Reply:

Similar Artilces:

Using A Macro/VBA code to re-set formulas
Hi can any one help me with a Macro/VBA code to re-set formulas in pre-defined set of cells i.e. in my case C14:C40 At present I have a copy of the Formulas in another part of th worksheet and when I want to reset the worksheet (as the user can ove right the formulas as the formula is based on either the sum of anothe two cells or the users input) I use a Macro to copy and paste (special the formulas. What I require is a Macro/VBA Code which already has the formula store in the actual Macro/VBA code i.e. so they are not stored as copy in m worksheet Ay help would be greatly appreciated T...

Need to do housecleaning on my macros
Having been migrated to a new system, it's time for a bit of housecleaning. I've got macros I wrote for one use and never used again, and I can't find some I've come to rely on. So my idea is to export all the modules from all my template projects and import them into one new template to use as an add-in. Along the way, I can evaluate whether the macro is something I still need or can do without. Is there an easy and automated way to export all modules from a project? Is there an easy and automated way to print these modules? What is the practical size limit of ...

A Tricky Question! Macro & Chart
Hi All I have a very tricky problem here. I have a column chart which has sa 4 columns - namely, A, B, C and D. First Issue - I would like to check is it possible to assign a specifi macro for A, B, C, and D to their respective columns? Second Issue - And as each macro has its own codes, would it b possible for the macro to be so smart that if I click on the column fo A, A macro will run... Please note that the order of the columns will change over time as will sort the values and put the one with the highest value as th first column. Kindly advice me whether the above are possible.....

how do i change or make this macro??? pictures and code included
i need to either make this from scratch or change it to suit my needs. Firstly a link to the template it is from http://www.exinfm.com/excel%20files/FY2000WhatIf.xls the code is Sub projection() Dim revgrowth As Single, costpercent As Single, smgrowth As Single devgrowth As Single, gagrowth As Single Dim intincome As Integer, noncontitems As Integer, otherexps A Integer Dim taxrate As Single, avgshares As Integer 'dialog box gets displayed With DialogSheets("dialog3") If .Show = False Then Exit Sub End If revgrowth = .EditBoxes(1).Text costpercent = .EditBoxes(2).Text smgrow...

Macro Buttons #2
i know how to create a macro button, my question is, how do i get that macro button to display on someone elses PC? i created the macro button in a worksheet then i sent that worksheet to a friend. the macro exists, but the button doesn't. any ideas? Thanks in Advance James You may want to attach the toolbar to the workbook. But if you do, read this first from Jan Karel Pieterse: http://google.com/groups?threadm=083701c3559b%246ce75e60%24a301280a%40phx.gbl James wrote: > > i know how to create a macro button, my question is, how do i get that macro > button to display on...

Macro to create pivotchart problems
Put together a quick macro to assemble a pivotchart, nothing fancy just ran through it recording and tried to run it on the same data afterwards, and have come upon this error: Run-time error '1004': Unable to get the PivotFields property of the pivottable class When I go to debug, the error is occurring at the asterisked line: Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "'Check results'!C1:C5").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", Def...

Insert a row based on a macro
Is there a way to insert a row based on a formula. For example, If A5=25, I then want to duplicate the existing row and insert it in the line below. See answers in your other post. Pls don't multi post -- Don Guillett SalesAid Software donaldb@281.com "Mindie" <Mindie@discussions.microsoft.com> wrote in message news:75A1DDD5-6334-410D-8646-091B43D1B198@microsoft.com... > Is there a way to insert a row based on a formula. For example, If A5=25, I > then want to duplicate the existing row and insert it in the line below. ...

2000 macro problem running with 2003
Is there any reason a macro written for Excel 2000 will not run with Excel 2003? John, Code written in Excel 2000 should work fine in 2003. Can you post an example of your code that is not working? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "John Leonard - Sage" <sagegrp@adelphia.net> wrote in message news:%23c7QWziUEHA.3476@tk2msftngp13.phx.gbl... > Is there any reason a macro written for Excel 2000 will not run with Excel > 2003? > > I will get the code and post it. Thanks "Chip Pearson"...

in a macro how to make a part of the formula a variable
I am trying to copy a cell from one file into another file in a macro. There are hundreds of files all linking to the same cell so I thought I would set the formula up so that is has a variable for the filename but I can't figure out the syntax. The formula should link to cell $r$16 in worksheet "monthly" in the changing file name and the variable I used for the changing file name is budget_file. The following didn't work. Can someone tell me how to do this? ActiveCell.FormulaR1C1 = "=" & budget_file & 'monthly'!$r$16&qu...

Inconsistent macro problem in excel
XP was rolled out at my work yesterday. Most of the excel file I work with have Visual Basic macros coded into them. (I use Excel 2002) Sometimes, I'll open a file, and it will give me the "Enable Macros?" prompt. This is normal, and what I want it to do. Sometimes, I'll open the same file, and it will give me no prompt. And if I try to run one of the macros, it tells me that the macros are disabled because the security is set to high. So, I go the macro security area to check it out...it's on medium, like it always is. I've found that the only consistent way ...

How to save macro changes in Read-Only documents?
I have over 100 documents that need the same sentence changed in each one. The documents have the read-only recommended box checked. I ran a batch to remove the password to modify but it didn't remove the read-only recommend. So when I run a macro to change the text I get an error. When I ran Debug it highlighted "myDoc.Close Savechanges:=wdSaveChanges" but I don't know what to do with that. (I only copy the macros from this great site!) I am looking for a way to remove the read-only recommended from multiple documents in a file. Please help. Many th...

Formula in Macros
I am trying to do a average for a ongoing twelve week period in my macro. When the macro updates it adds the last column but I can't get it to drop the first column so it goes from averaging 12wks then 13wks,14 weeks and so on. Is there a command I'm missing. Thanks in advance for any help you can give me.I'm on Excel 2002. SH This code averages the 12 columns in the above row. ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[-11]:R[-1]C)" By its nature, copy this formula across next week and it will pick up just 12 columns. What does your code look like? -- HTH ...

macro to run automatically when a field is updated
I have two form fields on an employee appraisal form. One calculates a grand average score from four different scoring areas on the form. The second field evaluates this score (using a macro) to determine the increase percentage based on the grand score. This macro is assigned to the field to run on entry. My problem is that if an evaluator goes back through the form and changes a score, they must click in this percentage field to get it to update to the correct value. My question is: how could I make this field update automatically anytime a change is made anywhere on t...

Calculations not keeping up with macro
Hello, I have a spreadsheet with 51 tabs. Tab_1 is a summary of data contained on the remaining 50 sheets (such as averages and sums). A macro iterates through values of 1 - 100. During each iteration a variable with dependents changes on each of the 50 sheets and resulting data for each iteration is output on the summary tab (output such as the averages and sums for each iteration). The macro works great when I step through it, the problems occur when I run it at full speed. The output data at each iteration does not keep up with the macro, thus the outputs are inaccurate. I...

Exxporting Macros
All, Is there any way or exporting Macros or the 'Personal Macro Workbook' in Excel so you can use Macros you have designed on other machines on a network? Any help would be great. Thanks in advance Jason B -- nosaj_enryb ------------------------------------------------------------------------ nosaj_enryb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28715 View this thread: http://www.excelforum.com/showthread.php?threadid=484521 Jason You are best to set them up as add-ins and load them via Tools>Add-ins... You may need some slight code a...

Macro to copy a VLOOKUP formula
Hello, I was wondering if somebody could help me with this macro… I have some data with a filter on, so that only the rows with blank cells are showing. I want to enter a VLOOKUP in the cell, and copy it down to all the blank cells. I tried just recording a macro, but when I run the macro it doesn’t work. Any ideas of how to do it? Example: 1) Before the filter: Colums A -B - C A x x - 1st Row A - 2nd Row A x x - 3rd Row A - 4th row A x x - 5th row 2) After the fil...

Modify Recorded Macro
I recorded the following macro, which basically Merges 2 cells and Alignes the text to the left. However, when recording it always records the cell address(Range("D***:D***").Select. How can i adapt the cell address to something like: ActiveCell - ActiveCell.Offset(0,1).select instead of the particular cell address. I need to run this macro about 1000 times ************************* Sub MergeCells() ' ' MergeCells Macro ' ' Range("D156:E156").Select With Selection .HorizontalAlignment = xlCenter .Vertic...

Excel 2003
Hi all Excel Gurus, I need help. I have an excel workbook with a sheet called template and i want to create a button to copy a selection of data which is B9:B34 and i need it to paste the information to another excel workbook with a sheet called data which will be A:AA and add each row when pressed. When the data has been copied over i need it to close the data sheet. Can someone help me please? When you copy a range of cells you only have to specify in the destination l;ocation the 1st cell of the range like I did below. Private Sub CommandButton1_Click() FName ...

macro to search and replace with offset
Hi, I need to create a macro which will search all occurrences of "item1" in column D and copy the values to the next cells in column B. The worksheet event below is exactly what I need Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub On Error GoTo errHandler: With Target If IsNumeric(.Value) Then If .Value > 5 Then Application.EnableEvents = False .Offset(0,...

File Save As Use in Macro
Does anyone know of a macro that will reference the contents of a cell and the save the file with the contents of that cell? For instance, Cell A1 contains the name John When the macro is run it will save the file as John.xls. Any help would be appreciated. Thanks, James Hardeman James Try this one Sub SaveCellName() Dim FileName As String FileName = Range("A1").Value ActiveWorkbook.SaveAs FileName:=FileName, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False...

Sorting macro
I have a database with 4 columns and 900-2000 rows depending on the number of items that we have. The first column is for the name of the product while columns B, C and D are derived from column A via formulas. I have created a button that will sort the rows. However, I still have to manually designate the number of rows to sort because the number of rows changes. If I sort ascending then any rows that are blank come out at the top. I would like to only sort those rows that are not blank. Any help? Try something like this: Sub test() Range("a1").CurrentRegion.Sort Key1:=Cells(1...

Problem executing a macro from different workbook where it is
Hi everibody, I have a macro in a workbook called, for example, WB1.xls. The macro takes differentes ranges and copy them in another location. ex. : Public Sub ZeroizaModeloSteve() Set wbModelo = Application.Workbooks(Mid(cteLibModelo, 2)) Set wsI = wbModelo.Sheets("I") Set wsT = wbModelo.Sheets("T") Set wsmodelo = wbModelo.Sheets(cteHojaModelo) Set wsZeroing = wbModelo.Sheets(cteHojaZeoring) wsI.Range("F50:IS50").Copy wsI.Range("F51").PasteSpecial xlPasteValues end sub 'i have omitted declaration variables and desct...

Excel crashes while opening excel file imbeddied with macros
Help! This is the similar problem as mentioned by Delbert on 12/12/2005 that whenever they open a particular Excel file, it gives them a "Send Report Error." Weird thing about it is it doesnt happen in all other Excel files, only happens to this file when saving as usual and trying to reopen it shortly after that. It happens on my machine and my friend working on the same file also. Details: OS: Winxp Office 2003 SP2 Files are saved in local drive Files are around 12MB size (varies) Files have Macros imbedded Files have links While opening, the dialogue box appears twice, asking...

Macro to find a row and delete it and the 9 rows above it.
I have searched but cant seem to work out how to do this proparly. I need a marco that look in Column F for ------- and then it needs to delete that row and the 9 rows above it. And kee doing that untill all ------ cells are gone. Anyone an idea -- Rusty_n ----------------------------------------------------------------------- Rusty_nl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=671 View this thread: http://www.excelforum.com/showthread.php?threadid=31376 Rusty, See the macro below. HTH, Bernie MS Excel MVP Sub FindValues2() Dim c As Range Dim d As Ra...

How to obtain Macro recorder to Outlook 2003 Message window
Hello, Why Office 2003 Basic Edition Outlook has a Macro Recorder and Office 2003 Outlook message windows doesn't? I installed Office 2003 Basic today to another PC and to my surprise there was a macro recorder in message Tools menu. My own 2003 Pro doesn't have it. How to obtain Office 2003 Outlook Macro recorder capability, is it distributed as an MS Office update? Both PC op system is the same XP Pro. Thanks! Outlook does not have a macro recorder. Word does and if you use word as your email editor, you'll see the macro recorder when you are composing ...