Relative Reference Not Working When Using Macros?

Jun 16, 2014

I'm trying to do is delete a row of chosen cells in my form (form is locked with a password) using a macro then return back to the first cell below the deleted row. When I run the macro what's happening is that it's returning back to the cell chosen while writing the macro (which is at the very top A7). So if I'm several rows below A7, it deletes the row and then returns all the way back to the top of the form causing the user to have to scroll back down to where they were

View 3 Replies


ADVERTISEMENT

Using Relative Reference In Macros

Feb 4, 2010

a user i'm assisting wants to calculate totals by recording a macro that sums all the rows in a column using relative cell references. this is because the user runs a report that pulls in data that varies. one week the report may contain 40 rows that need to be totaled, and the next week there may be 60 rows that need to be totaled.

here's what i did:place cursor in cell where i want total to appear
go to VIEW menu, MACROS tab, and click USE RELATIVE REFERENCES
click RECORD MACRO
enter name, description, assign shortcut key
go to FORMULAS menu and click AUTOSUM
press RETURN

when i test the macro out on columns containing the same amount of rows, it works fine. but when i run the macro on a column with more rows, it only totals the amount of rows contained in the original macro. see attached.

View 3 Replies View Related

Excel 2007 :: Convert Absolute Reference Into Relative Reference

Feb 4, 2014

I have encountered some difficulty in modifying a macro I wrote into what I need. I created a macro that searches a column (Column C) for a cell value of, "stop", and then it copies everything above that cell and pastes it onto another sheet. In the sample data set that I was using, "stop" first occurred in cell C541, so the macro copies C1:C540 and pastes it onto another sheet. The problem is that the macro created an absolute reference to C540. What I desire is for the macro to use the 'Find' function to locate the first occurrence of, "stop", offset one cell above that cell, and then reference the active cell (which was positioned by these last two steps) in the range that should be copied. Basically, I'm hoping to have cells C1 through the active cell copied and then pasted onto another sheet.

Code below.

Sub FAIL()
'
' FAIL Macro
'
'
Sheets("Reformatted").Select
Columns("C:C").Select
Selection.Find(What:="stop", After:=ActiveCell, LookIn:=xlValues, LookAt _

[Code] .......

View 4 Replies View Related

Macros That Were Working In Excel 2003 Are Not Working In 2007

Jan 25, 2010

I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.

Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i

View 9 Replies View Related

Relative Referencing Not Working

Feb 16, 2010

I have this LONG formula in a cell (J895). When I copy and then paste it to J937 I would expect it to act RELATIVE in respect to the rows on the same sheet. But instead, it pastes the same formula, like an ABSOLUTE....

View 9 Replies View Related

Absolute Column And Relative Row Not Working In Formula?

Mar 15, 2014

I have a below formula which extracts the v and number attached to it I would like to copy this across all columns and change row for each column. So for example 1st column reference will be B4, the second column would have a reference B5 and so on, it's probably worth to mention that cells I am referencing to are in other sheet hence why there's Ref! before B4 is this why absolute column and relative row not working?

View 5 Replies View Related

Excel 2010 :: How To Assign RELATIVE Macros To Custom Ribbon

Aug 19, 2014

I currently have an excel workbook (2010) in which I created a custom ribbon and assigned several different macros to the buttons.

Long story short, I will be saving several variations of this workbook every so often when I get new data.

The problem is that if I change the name of the file (Save as or Rename), the custom ribbon buttons no longer work. When I assigned the macros to the ribbon buttons, the macros were absolutely assigned.

Is it possible to assign relative macros to custom ribbons?

From research I have done, it appears that custom ribbons cannot be created using VBA. Is this still true?

View 6 Replies View Related

Relative Reference NAME Ranges

Jun 19, 2008

Creating a named range relative to the active cell? So that I can click on any cell and a named range is created in the adjacent column for 5 rows.

e.g. click cell c5, and a named range from cells d5:d10 is created when i run the macro ...

View 9 Replies View Related

Relative Sheet Reference Name

Aug 21, 2008

From a formula, how can I reference other sheets in my workbook via a relative reference as opposed to needing to know the sheet name? I know I can write a macro, but looking for a non-VBA solution.

View 9 Replies View Related

Msg Box Based On Relative Vba Reference

Jul 22, 2009

Im trying to get a message box to pop up if the result of a count formula is greater than one; ie. in a list of data to warn of repeats of a certain key reference.

Ive never had a drama with putting a message box in before but ive only based it off a constant reference whereas in this case, it will be based on a relative reference; for example, when inputted a value in A2, that has the same value in A1, the count formula in B2 will be 2, so i want a message box to come up. Same goes if the same value was put in A3, and B3 was greater than

View 9 Replies View Related

How To Do Absolute / Relative / Mixed Reference

Apr 14, 2007

This is what I need to be doing:

Write cell K5 as a relative reference, absolute reference, mixed reference with the row varying, and mixed reference with the column varying.

How do I do that?

View 8 Replies View Related

Relative Cell Reference In Macro

May 8, 2009

I have recorded macros in the attached file to highlight cells that contain matching initials by using conditional formatting on the first cell and copying it to the remaining cells. The problem is that if rows are inserted above that change the first cell reference then the macro no longer works properly. Is there a way to use a relative cell reference within the macro? The macro buttons are in cells A4 to A13. I have no VB experience and created the macros by recording keystrokes and using tips found in this forum.

View 14 Replies View Related

Relative Cell Reference Unstable

Jul 8, 2008

I seem to be having problems with using relative RC in vba within an automated reporting spreadsheet..

in my 'left lookup' below.. the R seems to point to row 1 no matter what row i am writing to..

View 11 Replies View Related

Drag A Cell In Vba Using Relative Reference

Aug 26, 2009

I need to drag a cell using a macro and using relative reference.
i.e. drag activecell to activecell.offset(1,-1)

View 3 Replies View Related

Change Relative Reference To Absolute

Apr 4, 2014

I have an Excel workbook with multiple worksheets. One of the worksheets has a live data feed for stock updates. I extract data from the live feed (it's in multiple sentences) and pull it into 6 columns. In another worksheet, I pull those 6 columns into the sheet through the use of Index / Match. That second sheet has additional formulas that essentially analyze the data. As the data refreshes, the existing data moves down the column.

What I didn't think about was the fact that at times, a stock may be listed several times because different brokers are offering updates. Because I used Index / Match, it will look for the first match from the top and display that data in the relevant columns. Unfortunately, some of the data needs to stay with the matching row (and stock ticker) but because of the way I wrote the formulas in Index / Match, it just finds the first matching target and displays that data.

I can correct this by using an absolute reference ($) but I can't find a way to "copy down" formulas using absolute references. I also have one column of cells that contain 1 absolute reference and one relative reference.

I'll re-write this manually if I have to but I have 1,800 cells to change.

View 2 Replies View Related

Lost Relative Reference Icon

Aug 11, 2006

I went to record a macro but I can't seem to get the relative reference button anywhere. I am using Excel 97 - does anyone know why this might be or how I can get it back?

View 9 Replies View Related

Relative Reference To Work Sheet

Nov 27, 2006

I am dealing with a workbook that will hold an undetermined amount of worksheets. Each of these worksheets will have have a series of numbers that are summed to a total within one cell. Lets say cell A10. For every spreadsheet, no matter what, this number will be stored in cell A10.

My first sheet will then be a master listing of all the other sheets in the workbook. In column A I will have the names of each sheet and in column B I want excel to list the value of A10 for the corresponding sheet listed in column A

So normally it would look like this on the master sheet

A | B
Sheet 2 | =Sheet2!A10
Sheet 3 | =Sheet3!A10
Sheet 4 | =Sheet4!A10

Where the formula would give me the value of A10 in the respective sheet.

What I would like to do is, within column B, I want to replace the sheet reference with the cell that contains the name of the sheet. So it would then be a formula similar to the below setup(Although this does not work because I have tried it)

A | B
Sheet 2 | =A1!A10
Sheet 3 | =A2!A10
Sheet 4 | =A3!A10

The hope is that excel would substitute in Sheet 2 for A1 and there by give me the sheet reference that I need. This way I can continuosly add sheets to the workbook and as long as I have the correct Sheet names in column A, all I need to do is drag the formula down column B and I will pull all of the necessary information without having to retype the formula each time.

View 9 Replies View Related

Relative Cell Reference For Summing

Dec 17, 2008

This is one of about 25 sheets in one excel file, and I will be receiving about 5 different files a week, so a macro is necessary to save time. I tried to create a macro just by recording, and it was not able to adjust to the variances in the number of lines between the sheets.

I need it to do the following, and more or less in this order:
-Delete Column A
-Create text and format A1:C1
-Sort rows 2 through the last row based on column C values (from largest to smallest, although its not shown in the picture above)
-Create a sum at the bottom
-Apply grid lines to all the data

I don't have a problem with the first two, but I cannot figure out how to adjust for the number of rows for sorting, offset 1 cell down to create the sum formula below the data, or how to properly write the macro for the sum formula itself. When I recorded the macro, it used cell references like Range("C5").Select and others which do not adapt to the varying number of rows.

I tried using the offset command in this thread without much luck.

This is some of the macro I have:

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveCell.FormulaR1C1 = "Customer ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Customer Name"........

View 9 Replies View Related

Selecting A Worksheet By Relative Reference

Mar 7, 2007

I have a workbook with 50 worksheets, each worksheet has an Alpha name (ie names of people). Rather than write some code to go through each worksheet to create a report, is it possible to write a formula in a "Reports" worksheet that reads something like

=worksheet10!f2

where worksheet10! is the number of the worksheet (as seen in the VBA project window) while the name is "Billy".

View 9 Replies View Related

Relative Reference Button Disappeared

Apr 10, 2007

When I click Tools Macros Record New Macro I usually get a box which allows me to chose relative reference and is displayed until I click stop.

View 4 Replies View Related

How To Count Range Relative To Dynamic Reference

Apr 1, 2014

I am trying to create a formula to count a range relative to a dynamic reference (at least I think that's you would phrase it).

I thought I was on to a winner with this:

Formula:

[Code] .....

But it doesn't seem to accept the : as a legal operator.

I suspect the answer is to use SUMPRODUCT somehow.

View 6 Replies View Related

Named Formulas To Reference Relative Rows?

Jun 17, 2009

Formula: B2+C2

In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.

As it stands I can only get the formula to calculate within the same row.

View 12 Replies View Related

Run Macro Function To Create A Relative Reference

Oct 1, 2008

I'm a definite newbie when it comes to macros but can figure out formulas ok.

So I wrote a formula to break out a sort string and it got pretty complicated so I’m trying to create a macro so I don't have to write out the formula all the time. However the sort tag can be in different columns when I need this formula so I tried the "run macro" function to create a relative reference.

So it changed my formula from this:

PHP
=IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)),"",IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2)+1)&")",EM2)),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,100),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,FIND("("&(COLUMN()-COLUMN(EM2))+1&")",EM2)-FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)-3))) 

PHP
 "=IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])),"""",IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1])+1)&"")"",RC[-1])),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,100),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,FIND(""(""&(COLUMN()-COLUMN(RC[-1]))+1&"")"",RC[-1])-FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])-3)))" 

Now my problem is in the first formula I need "$EM2" not "EM2".

I tried the obvious to me fix of R$C[-1] which did not work... And when trying to re-run the macro it puts RC143 which will not work because the sort string is not always in column EM.

View 8 Replies View Related

Use Range Of Selected Cells As Relative Reference

Mar 8, 2013

I have a data that is split into multiple cells and needs to concatenated. Unfortunately, the number of columns wherein lies the data varies throughout the workbook. As such, I wanted to select a range of cells, define this range as the reference point for the macro, run the macro, then move on (selecting a different number of columns on the next try).

For example, I have this:

A
B
C
D
E

1

No
Not Very Far

[Code] .....

I want to select cells A1:B1, run a macro concatenated the two columns, then select cells C1:E and run the same macro to get this:

A
B

1
No
Not Very Far

[Code] ........

So far, I have this:

Code:
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(5, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[2])" '

View 9 Replies View Related

Relative Cell Reference In Hyperlink Formula

Sep 30, 2013

I have a list of University courses listed in Sheet1. Column A contains a unique course code. If there is a problem with a course, a second worksheet (called Anomalies) contains the same unique course code with description of the problem(s).

What I'm trying to do is: in Sheet 1, create a hyperlink to the relative cell in Sheet 2 that shows the course code and description. I've gotten close, but am not sure how I can do this without having to update two formulas. In short, I can't find a way to combine the two formulas.

What I've done is the following: in Sheet 1, column B, created a HYPERLINK formula, thus: =HYPERLINK("#"&CELL("address",INDIRECT(C54)),A54)

Column C contains a formula to lookup the relative position in the Anomalies worksheet, which can be referenced back to the HYPERLINK formula, thus:
=("Anomalies!A"&MATCH(A54,Anomalies!A:A,0))

Sheet1 is fairly static, but Sheet2 (Anomalies) is being updated all the time. I could just hide column C and then column B will update based on that, but I'd prefer a cleaner solution.

I've tried joining the two formulas, by replacing the INDIRECT part with the second formula, as per the following: HYPERLINK("#"&CELL("address","Anomalies!A"&MATCH(A54,Anomalies!A:A,0)),A54)
but I get an error.

View 5 Replies View Related

Selecting Relative Reference Range For Fill Down?

Dec 11, 2013

I'm trying to use relative references and am have having trouble with selecting a range for the fill down command.

I'd like to copy a formula down in the last column from row 1 to the last row with data. Column D is static so I can always use it to find the last row. The last column will vary; sometimes it might be G another time it might be X. I'm able to get the formula entered into the proper cell but I can't figure out how to select the range for a fill down.

If I was not using relative references and cell X1 always had the formula then Id code it like this:

'*** Find last row with data
Range("D1").Select
LastRow = Range("D65536").End(xlUp).Row
'*** Calculate length of cell in previous column

[Code]....

It's the last range statement that stops me. I haven't come up with a variation that allows me to select a range from the cell on the first row of the last column and select the last row with data for the same column.

View 3 Replies View Related

Syntax Error In VBA Relative Reference Formula

Jan 7, 2014

I was able to get VBA to post this relative reference, which sticks the formula

=DATE(YEAR($D2),MONTH($D2)+6, DAY($D2)+1) into a cell in Col J.

All fine and dandy, works as it should with this formula.

Code:
.Cells(Row, "J").FormulaR1C1 = "=DATE(YEAR(RC4),MONTH(RC4)+6, DAY(RC4)+1)"

However, when I try this with a different formula =IF(ISNUMBER(SEARCH(C2,H2)),"REPEAT","SAFE"), I'm getting a compile error: Expected end of statement with this code:

Code:
.Cells(Row, "I").FormulaR1C1 = "=IF(ISNUMBER(SEARCH(RC3,RC8)),"REPEAT","SAFE")"

The error message highlights the word REPEAT. I suppose it's something with all the quotes throwing off where compiler thinks the end of the formula should be.

View 3 Replies View Related

Macro To Run Serial# 1 To 10 Based On Relative Reference

Apr 22, 2008

I need a macro that can run a serial# 1 t0 10 (or more or less) with A1 being starting point. Here is my illustration data.

Coloumn:A Coloumn:B
1 IrfanAneeza
2 Scooby Dooby Do
3 Dooby Dooby Do
4 Yaba Daba Doo

However, there is a little kicker in it. The range of coloum:B varies all the time i.e. B:B could be 1 to 10, it could be 1 to 3 etc. Thus, macro should list serial#'s based on the coloumn B:B range of data.

View 9 Replies View Related

Reference Cells Relative To Activecell In A Range

Nov 4, 2008

I've been trying to get the sum of a range of cells based on their relation to the active cell. Here is the Pseudo
If the SUM of (the cell 2 rows up THROUGH the cell 2 rows up and 2 columns to the left) = 0 then .......

*****end pseudo

I think that activecell.offset is the best way to do it, but I can't figure out how to work that in a range....

Here is my code so far:

Worksheets("Generic SPC").Activate
Worksheets("Generic SPC").Range("c2").Select

'Do for all cells in the row
Do

ActiveCell.Offset(0, 1).Select
If Application.WorksheetFunction.Sum(Range( _
ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2))) = 0 Then
ActiveCell.Value = 0
ElseIf Application.WorksheetFunction.Sum(Range _
("ActiveCell.Offset(-2, 1), ActiveCell.Offset(-2, 3)")) = 0 Then
ActiveCell.Value = ActiveCell.Offset(-2, 0).Value
Else: ActiveCell.Value = 0
End If


Loop Until IsEmpty(ActiveCell.Offset(0, 1)) = True

******End Code

Range doesn't like activecell.offset. Does anyone know how to do this?

View 9 Replies View Related

Relative Reference Formula Not Changing Until Re-Entered

Dec 28, 2006

When I copy the formula to another cell, It doesn't show the result. But when I double click on it, it works. For example

there r values in A1 as "john" & A2 as "Jack". I write formula =A1 in cell B1. It shows "John". Now I copy this formula to B2, it shows "john" instead of "Jack" . When I double click on B2 & enter, it shows "Jack".

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved