2007 Macro Relative References Works On Only One Workbook
Oct 8, 2009
In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.
Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.
I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.
How to change from absolute references to relative references.
Example : ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address This code return the absolute references---> =$H$365+$I$365 , and i want change to relative references, like this ---> =H365+I365
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
My OS is Windows 7. My version of Excel is 2007. I have read how to get a macro to move around a page in a relative way, but is there a way to move from page to page in a relative way as well?
Need to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).
Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?
All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?
I have a macro that is set to clear a range of cells. If I make a change to the worksheet (such as adding a column) that moves those cells in the worksheet, the worksheet formulas update, but the macro cell references do not. How can I make my macro update in accordance with changes in the worksheet?
I suspect there's a simple answer to this but I've looked and can't find it: In Excel 2003, when I add a hyperlink to another file and close/reopen my workbook, the link is saved as relative to the current workbook. So if the main workbook is moved or, in my case, when I use VBA to copy and email the sheet with the hyperlinks, the links are broken.
I'm having some trouble coding a macro that copies data from one sheet in a workbook to the next sheet in the workbook, and the problem seems to stem from my lack of knowledge about how VBA refers to sheets. What I'd like to do is copy data from a range of cells in the next-to-the-last sheet in a workbook to the same range in the last sheet in the workbook.
Each day, I add a blank sheet to the end of all the sheets in my workbook. I insert some data into it, then go to the previous sheet, copy some data, return to today's sheet (the one I just added) and paste the data. I can record a macro that will do this perfectly, but it only works for the two sheets in which I record the macro. I need a way to change the sheet references so it always copies from the next-to-last sheet to the last sheet.
I have a spreadsheet with very long formulas that reference a whole lot of variables in the same column. I tried naming the cells so that writing of the formula is easier to do. However, I need to copy the column across for other entities of varying nature, and these copied cells refer to the same named cell. Can I name a cell and copy it sideways so that it retains some sort of name with allusion to the column that it is in?
Andrew van Gruting"
I have tried copying a formula with a named range and, as expected, it maintains an absolute reference. Is there a way that he might be able to copy but drop the absolute reference and copy with relative references. Can I place something in front - the equivalent to the $ sign, but rather a relative sign.
I have a workbook with a single worksheet. The worksheet includes some named ranges, which are used in a dynamic chart (so that when I add data, the chart updates).
I want to duplicate worksheet#1 so that I can have worksheets 2, 3, 4 etc.
Thus I can paste new data in the new worksheets, and have all my calculations done.
Naturally, the only probelm is the graph. It is using the named ranges from worksheet #1.
Is there a solution, so that I don't have to re-create my graphs on each worksheet? (each worksheet has varying numbers of rows, AND, I will be updating each worksheet with new data from time to time, thus the need for a dynamic chart)
To simplify matters each day is exactly 10 rows (including header).
I already have code in column D that populates each D row IF it is the first unique occurance of column B for each day. The forumla in column E for rows 2-10 put a daily total by each unique occurance.
The problem: Since I am using absolute references in E2-E10 to contain the totals for that day if I copy E-2 -> E10 to E12-E20 the totals will be incorrect.
If I use relative references in the formula it changes the range for the day which is also produces incorrect totals.
Question:
How would I copy the formulas in column E to the addtional days so that the ranges follow for the specific 10 row day that they are intended?
I've read several threads about switching between relative, absolute, and mixed references across several cells however these solutions seem to result in formula with all relative or all absolute or all mixed.
I need to change the formula in lots of cells with a mix of types of reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer not to have to go though each of the cells with F4!
So have large spreadsheet with grid of elevations. Each column/row is 1 foot grid. There are blanks in it where there was no elevation data available. I need to interpolate those elevations using the surrounding known elevations. I can setup the interpolation just fine using a circular reference but what i am having trouble with is getting formula into all the cells. I need to replace blanks with formula that averages all four cells around it.
This will result in circular references that will interpolate from known points nearby. So for example if cell D4 was blank formula needs to be
=(D3+E4+D5+C4)/4. D5 would be =(D4+E5+D6+C5)/4.
I have been playing around trying to make a macro or something to do this but am not having any luck. Basically need to find/replace all blanks with formula above but so the formula references the cells around it properly.
I am setting up a spreadsheet for user data entry. I have one sheet set up as a template to enable users to copy the required data header cells to subsequent sheets and (the problem) - to different locations on the subsequent sheets. The template is using validated lists with the criteria drawn from the cell/list directly above the current list. For example, the cell in R11C2 is validated/refering to the range: =Campaign
The cell directly below this is validated/ filtered by: =Indirect(R11C2). This works great in the template, or any subsequent sheet in which the cells are all located in the same row/column. However, when the template is pasted in a higher row, the Indirect refers to R11C2 rather than referencing the cell directly above.
I'm having trouble with Color Scales within Conditional Formatting. I have a data set of commodity prices. In column A I have the name of the commodity, in column B I have the standard deviation of the price change of the commodity, and in Columns C-N I have the monthly % change in the commodity price. I want to conditionally format with Color Scales each row of price changes within Columns C-N based on each commodity's standard deviation (column B). If the price change is a one standard deviation or more decrease, I want the cell to be dark red; if the price change is less than a one standard deviation decrease, I want the cell to be a gradient of light red; if the price change is a one standard deviation or more increase, I want the cell to be dark blue; if the standard deviation is less than a one standard deviation decrease, I want the cell to be a gradient of light blue; and if the price change is 0, then I want the cell to be white.
I can achieve this perfectly by manually doing 3-Color Scale Conditional Formatting for each row, but it's very time-consuming. And Excel doesn't allow me to enter relative cell references when I'm doing the Color Scale Formatting. Is there a quick way that I can do this so that each row is color formatted differently?
I've attached an example file (there are many more rows within the original file) and formatted the first several rows manually as I want the final product to look.
eg say you call (ie insert a range name) cell A1 "firstcell", B1 "secondcell", then A2 "divisor1" and B2 "divisor2". if you put a formula in A3 which is "=A1/A2", how do you copy and paste this formula into B3 but getting the formula to reference B1/B2 rather than firstcell/divisor1, as it does by default?
It works great in xl2003, and it works properly in XL2007 until you try and change it. I changed the column F to Column D and now it gives me a #value.
The below code works fine in excel 2003 but when I run it in excel 2007 it crashes excel. (Microsoft Excel has encountered a problem and needs to shut down etc etc)
I'm working on a tracking spreadsheet that needs to be able to function on both Excel 2004 for Mac as well as Excel 2007 for PC. The code below is written to ask for a date when the status of a particular row of cells is changed to a value other than "". These cells have validation lists where the list itself is just below the cell in hidden rows. It then places the date entered in the corresponding cell to the right of the list choice in the hidden rows and the top row is simply a vlookup to show the date with the status choice.
This way, when the status cell is changed again, it retains the date in the hidden rows while setting a different one. The code below is obviously not the full code, the whole code includes other functions and is a bit long, but I've narrowed it down to this:
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
In an effort to manipulate the xml documents within an xls, you used to be able to change the extension to .zip then open. This no longer works for me, just getting the 'cannot open file: it does not appear to be a valid archive.
I have tried it with a number of different files, but no joy.
It still works with a .doc so I guess it's not that Microsoft have stopped this capability?
I am migrating a bookkeepping spreadsheet from Quattro v12 (c2001) to Excel 2007 and have hit a stone wall over relative 3-D references.
In this workbook each sheet holds data for one month, and this includes some lagging-12-month calculations -- that is, summing a range on a single cell for the preceding 12 sheets (including this one).
Unexpectedly, this formula: =ISUM('201206:201305'!M6)
gives absolute references, even though I put in no dollar signs after the sheet names. This means it gives the wrong answer when copied to the next sheet to start a new month.
I can find no reference at all to converting the above formula from absolute to relative reference.
This code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?
Sub CopyFormatMMT() 'Colour code Owners Dim r As Range Dim f As Range Dim c As Range Dim j As Range Set r = Range("ColourRangeMMT") Set f = Range("ColourIndex") Range("ColourRangeMMT").Select Selection.Interior.ColorIndex = xlNone For Each c In r.Cells For Each j In f.Cells If c = j Then c.Interior.ColorIndex = j.Interior.ColorIndex End If Next j Next c Range("C9").Select End Sub
The new table feature of Excel 2007 is IMO the best improvement over the 2003 version.
I have 3 questions about references to these tables:
1) When you reference to a cell in a table on the same row, you get the reference "#This Row". Why isn't there a "#This Column" reference?
2) Normally when you want to "fix" a reference to a cell you use the $ sign by hitting F4 (i.e. A1 -> $A$1). How can you do this with tables (Column1 will not change to $Column1 when hitting F4)?
3) When I have = sum(Column1) above Column1 and I drag this to a cell above Column2 I get =sum(Column2). However, when I have the formula {=sum(Column1)} this doesn't work. Why?
The following code was working for me and now no longer works. Also, it works when I step through but not when I run it normally (it stops at the line imeediately following this line:
OrigWB.Sheets("PP&E").Move after:=DestWB.Sheets(DestWB.Sheets.Count) ) regardless of what code it is - even if it is only a message box.
The error message is code execution has been interupted.
****************** Sub PPEMerge() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.StatusBar = "Merging..." 'retrieve source docs: 'PP&E sheets from sec packages 'system beginning and ending balances
I have a workbook setup with 4 sheets that I'm using. I need the 4 sheets to type in all caps when I enter the data. I found a script, and followed the directions. It works great, yet when I close out of the workbook, and open it back up, the script doesn't work anymore. I go to check the 'view code', and it still there. I resave it, and try it again, and it still doesn't work, what am I missing? This is all new to me. I am saving it as a Workbook Macro enabled file type.
Once I get this resolved, is there a way to make it default to use the script everytime I open the workbook?
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub On Error Resume Next If Not Intersect(Target, Range("B3:O210")) Is Nothing Then Application.EnableEvents = False Target = UCase(Target) Application.EnableEvents = True End If On Error GoTo 0 End Sub
I wrote code to update some workbooks. The code opens the workbooks and then activates the workbook to add the update.
I was tired when I wrote the code to activate the workbook and it is written:
Workbooks("Update").Activate
The updates have already been sent out and it is not working on some computers. (If I change the code to
Workbooks("Update.xls").Activate
it works fine.)
Is there some option in the VBA editor that I can have people change on their computer so the code will run? Why does it work on some computers and not others?
(Unfortunately, rewriting the code to add ".xls" and resending everything isn't an option.)
I have set up a workbook (wb2) that has external references to another workbook (wb1). All is good and works fine when you open the 2 files from windows explorer. I have then put a hyperlink to wb2 from wb1. When you click on this link the file opens but throws up errors saying some of the named ranges cannot be found?
