Indirect function - relative reference

Hi,

I struggle to create a macro with INDIRECT function that would jump to
different cells. What cell I want to jump to depends on a currently selected
cell. This means I need to put a relative reference into the function.

Here is my function - I need to replace R1C1 expression with a realtive one
(currently selected cell address with the same behavior as R1C1 expression).
How can I do that?

Application.Goto Reference:= _

"INDIRECT(CONCATENATE(R56C8,""!"",""a"",MATCH(R56C1,INDIRECT(CONCATENATE(R56
C8,1)),false)))"

Whatever I do I get following error mesage:
Run-time error '1004'

Thanks in advance
Vladimir


0
vladimirv (5)
2/5/2004 7:26:57 PM
excel 39879 articles. 2 followers. Follow

2 Replies
758 Views

Similar Articles

[PageSpeed] 6

Your reply/second post of the same question made it look like your 
question had already answered when it hadn't.  That probably limited the 
number of responses you received.  In future be more patient.

Giving more detail in the original question would also be helpful.  For 
instance, what is contained in the referenced cells, and what to you 
expect the formula to return?  That would allow someone to test a 
solution before proposing it to you.

Also, it is not clear whether your original formula works as is (and you 
just want it converted to relative reference) or not.  I suspect that it 
does not work as is, since it requires that VBA evaluate worksheet 
functions.  VBA has its own set of functions, and does not directly 
understand worksheet function.  For VBA to evaluate a string expression 
involving worksheet functions, you would have to wrap the expression in 
the VBA Evaluate() function.

Jerry

news.microsoft.com wrote:

> Hi,
> 
> I struggle to create a macro with INDIRECT function that would jump to
> different cells. What cell I want to jump to depends on a currently selected
> cell. This means I need to put a relative reference into the function.
> 
> Here is my function - I need to replace R1C1 expression with a realtive one
> (currently selected cell address with the same behavior as R1C1 expression).
> How can I do that?
> 
> Application.Goto Reference:= _
> 
> "INDIRECT(CONCATENATE(R56C8,""!"",""a"",MATCH(R56C1,INDIRECT(CONCATENATE(R56
> C8,1)),false)))"
> 
> Whatever I do I get following error mesage:
> Run-time error '1004'
> 
> Thanks in advance
> Vladimir

0
post_a_reply (1395)
2/6/2004 9:33:22 AM
My reply was a mistake, I just couldn't have took it back.

Here is more thorough explanation of the problem.

At first - following formula does work, but it naturally does still the same
thing regardless the currently selected cell. Since I'm not a programmer
(just a little one), I have simply recorded a sequence of steps in Excel and
tried to amend it in a code.

What I need is to persuade the macro to read the current position of the
active cell.
So, if the active cell will be B2 the expression would be:
Application.Goto Reference:= _

"INDIRECT(CONCATENATE(R2C2,""!"",""a"",MATCH(R2C1,INDIRECT(CONCATENATE(R2C2,
1)),0)))"

if the active cell will be B10 the expression would be
Application.Goto Reference:= _

"INDIRECT(CONCATENATE(R10C2,""!"",""a"",MATCH(R10C1,INDIRECT(CONCATENATE(R10
C2,1)),0)))"

I need to replace R10C2 part of the expression with something like
ActiveCell.Address() - but it won't work.

What I intend to do is to double click on a cell - trigger a macro that will
read its contents (range name) and in this range find a value from the 1st
column on the same row of the active cell and jump on a cell in this range
that contains this value.

How can I manage that?

Thanks Vladimir

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> p�se v diskusn�m pr�spevku
news:40235FAC.4010404@no_e-mail.com...
> Your reply/second post of the same question made it look like your
> question had already answered when it hadn't.  That probably limited the
> number of responses you received.  In future be more patient.
>
> Giving more detail in the original question would also be helpful.  For
> instance, what is contained in the referenced cells, and what to you
> expect the formula to return?  That would allow someone to test a
> solution before proposing it to you.
>
> Also, it is not clear whether your original formula works as is (and you
> just want it converted to relative reference) or not.  I suspect that it
> does not work as is, since it requires that VBA evaluate worksheet
> functions.  VBA has its own set of functions, and does not directly
> understand worksheet function.  For VBA to evaluate a string expression
> involving worksheet functions, you would have to wrap the expression in
> the VBA Evaluate() function.
>
> Jerry
>
> news.microsoft.com wrote:
>
> > Hi,
> >
> > I struggle to create a macro with INDIRECT function that would jump to
> > different cells. What cell I want to jump to depends on a currently
selected
> > cell. This means I need to put a relative reference into the function.
> >
> > Here is my function - I need to replace R1C1 expression with a realtive
one
> > (currently selected cell address with the same behavior as R1C1
expression).
> > How can I do that?
> >
> > Application.Goto Reference:= _
> >
> >
"INDIRECT(CONCATENATE(R56C8,""!"",""a"",MATCH(R56C1,INDIRECT(CONCATENATE(R56
> > C8,1)),false)))"
> >
> > Whatever I do I get following error mesage:
> > Run-time error '1004'
> >
> > Thanks in advance
> > Vladimir
>


0
vladimirv (5)
2/6/2004 5:35:23 PM
Reply:

Similar Artilces:

datatype() function in Dexterity
I'm trying to get the datatype() function to work with an anonymous field input from a list of table fields. All I get it '4' for a string, no matter if its a Time, Date, Integer, Long, etc. field being passed. I've maneuvered around this function, and I'd really like to see if work, because it would automate an entire process that I'm working with today. The Dex help doesn't provide any insights. I believe I'm passing the string that is the field, but not getting the actual field passed. function returns integer outtype; inout anonymous field infield; l...

Reference a cell in a named range
How do you reference a single cell in a named range? I have: A B C 1 13 =A1^2 =A$1^2 2 35 =A2^2 =A$2^2 3 51 =A3^2 =A$3^2 4 79 =A4^2 =A$4^2 Define the name "Alice" for column A Then the array is A B C 1 13 =Alice^2 =A$1^2 2 35 =Alice^2 =A$2^2 3 51 =Alice^2 =A$3^2 4 79 =Alice^2 =A$4^2 In a macro I need to use Alice, rather than A because I may insert columns before A. That's no problem, but I also need to refer to specific absolute rows in the column. I need column D to be: A B C ...

Convert COUNTIFS function from 2007 to something in 2003 version
I have this function in 2007 version, but need to make it work in 2003. What function would be compatible? =COUNTIFS('Cancel Detail First 60 Days'!$B$1:$B$163,"GGL",'Cancel Detail First 60 Days'!$I$1:$I$163,"UW") Try =SUMPRODUCT(--('Cancel Detail First 60 Days'!$B$1:$B$163="GGL"),--('Cancel Detail First 60 Days'!$I$1:$I$163="UW")) You can test it by putting it into your 2007 workbook next to your SUMIFS() formula,should give the same results. "cbrannlayt" wrote: > I have this functi...

Count Function on True Statements
I wonder if I can do a count on a specific value, like if I had this: CAR1 CAR1 CAR2 CAR2 CAR2 CAR3 Is there a way I can count how many "car2" I have? Currently, the only way I can do this is to set a true/false statement then count the trues. -- krayziez ------------------------------------------------------------------------ krayziez's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34891 View this thread: http://www.excelforum.com/showthread.php?threadid=546334 =countif(a1:a10,"car2") This is a function that accepts wildcards, so if y...

A problem about the function COUNTIF
Dear all, I encountered a problem when I use the function COUNTIF. Column A (A1: A1000) is a column of numbers and B1 is another number. I would like to record how many entries in column A is bigger than B1 in another cell C1. I tried to use the formula =COUNTIF(A1:A1000,">B1") for C1 but it failed. However, the formula =COUNTIF(A1:A1000,">0") shows the number of positive entries in column A correctly. It seems that the function cannot recognize >B1 as a selection criteria. May I know how it can be solved? Thanks in advance. Best Regards, Chris ...

Newly Created Function not Working #2
I placed it in his VBA projest (personal.xls) in a new module. Wher could I find the standard module? Thank you -- nuve ----------------------------------------------------------------------- nuver's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1003 View this thread: http://www.excelforum.com/showthread.php?threadid=26961 Hi open the VBA editor and insert a new module for your file. See: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Regards Frank Kabel Frankfurt, Germany "nuver" <nuver.1e6fcn@excelforum-nospam.com> schrieb im Newsbe...

cell reference #2
I have a question about cell reference in newer editions of Excel. In older editions cell reference was denoted by a letter and a number e.g. B3, A5 etc. In a recent edition (I don't know which) I saw a format which displayed numbers on both the vertical and horizontal axes. Reference to a cell was made (I think) but subtracting the reference number from the cell to which it was refering. Does this make sense? My question is whether newer editions (2000, 2002) still use the old method (B4 etc)? or do I need to look for an older version in order to be able work in the letter/numb...

Message Map functions called twice?
I am new to MFC and I created a dialog that uses a treectrl. In the tree control I created a function to get the child data when the user expands. But for some reason when I expand the tree the function is called twice and it tries to retrieve the data twice and I don't understand enough about message maps to understand what is going on. Is the message being generated twice?? If anyone could help I would greatly appreciate it. This is how it is declared in the message map: ON_NOTIFY_REFLECT(TVN_ITEMEXPANDING, OnItemExpanding) I'm not sure about this particular function but most ...

functions in charts
Is it possible to enter a user-defined function (that returns an array) for values in a chart instead of specifying a range in the worksheet? Joe - You can specify a named range, with the formula built in. See the Dynamic Chart examples and links on my web site: http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html A good example of a formula in a named range can be found on Stephen Bullen's site: http://bmsltd.co.uk Look for the ChtFmla.xls example. Tushar mehta has a similar example on his site: http://tushar-mehta.com In VBA it's possible to assign an a...

How to replace column letter in refferences with a function using the old column letter?
Hello How to replace column letter(s) (or column numbers) in refferences with a result of a function using the old column letter(s) (or column numbers)? I think you mean =INDIRECT(A1&7) where A1 holds the letter in this instance. -- HTH Bob Phillips "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message news:%23bHpscyzFHA.1968@TK2MSFTNGP10.phx.gbl... > Hello > How to replace column letter(s) (or column numbers) in refferences with a > result of a function using the old column letter(s) (or column numbers)? > > Thanks for your reply. How to change...

Function In A Text Box
Hi, I am trying to put a concatenate function in a text box. The error message is as follows: The text you have entered is not a valid reference or defined name. I want to concatenate some text with a number from the cell, A2. Any ideas would greatly appreciated. Regards Tubbsy -- tubbsy ------------------------------------------------------------------------ tubbsy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24743 View this thread: http://www.excelforum.com/showthread.php?threadid=388332 Its not a perfect solution but you could perform the concat...

Fixing missing references automatically
When I distribute an Excel workbook with VBA code, half the time I have to call up the users and walk them through going to the references list and unchecking and rechecking the missing references, because someone always has different libraries than I do. It's frustrating and embarrasing - it makes me look unprofessional as a developer, which I guess I am. Can anyone tell me a good way to avoid this problem, either by having this task automated or by sending out some kind of update that makes everyone current with the latest vba libraries? I'm using Excel 2002, and the libraries...

Compiler Bug? (Relating to Pointer to Member)
I'm looking at what appears to be a compiler bug. I'm not at liberty to post full classes, etc. but will post the relevant pieces. Project A is a library (LIB). In that project, I define a pointer to member type like this: class CFileWin; class CFileIterator; typedef void (CFileWin::*LPITERATEFILEFN)(CFileIterator *); I then define the CFileIterator class with several methods and the following member variables: public: IDataObject *m_pDataObject; CString m_sDstPath; CString m_sSrcFile; CString m_sDstFile; CBuffer<BYTE> m_buff; LPFILEDESCRIPTOR m_pfd; ...

Error copying cell containing 'IF' function and labels
I'm running Excel 97 SR-2 on Windows XP (and have found the same problem running Excel 97 on Win95). When copying a cell containing an 'IF' function that references a column label, the reference is an absolute reference rather than a relative reference. For copying other cells with formulae containing column labels but not 'IF' functions, the column label reference is a relative reference as is the obvious intent when using labels. After pasting in the destination, the absolute reference is changed to relative if the contents of the destination cell is simply copied ...

Displaying the actual data that error message is referring to 10-12-07
Is there a way to display the actual records that are being imported from excel to access if they continue giving you an error message? ...

Autobudget not functioning as described
In an effort to establish a Budget in Money Deluxe & Business 2002, for both the Income and Expense items, Autobudget is available to me, but when I click on it and click "Select All" in the dialog box that comes up for Expenses, the right hand column indicates "No spending" in all categories and no entrys are made. Clicking on "View Transactions" reads "Past Spending 2/5/2005 Through 2/5/2006 per the "History Chart" and is blank per "Transactions." What am I doing incorrectly or not understanding here? -- Ron ...

why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? Dim sheetcnt As Integer Private Sub cmdImport_Click() sheetcnt = 1 Daniel, Dimming a variable outside the procs makes it module-level. If you'll need to use it across modules, make it project level: Public sheetcnt As Integer -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:%23ya7IdBhFHA.2916@TK2MSFTNGP14.phx.gbl... > w...

Is there a function that can ...?
Good Day. I hope someone can help me with this little problem. Source data is a list of Names (col A) with corresponding numerical values (cols B to F). Each Name can have multiple, varying occurrences within this list. I wish to summarise, in another worksheet, the sum for each individual person. I am using sumif to sum each person’s values, which is working fine. The problem is how to have the summary table automatically find and list the names from the source, with the name occurring only once, with the numerical value being the sum of all the values for that person’s multiple occurr...

Cell reference displays 0 instead of blank
In a multi-sheet workbook, if I reference a cell in another sheet (using =SheetName!Cell) that is blank, the result displays as a 0 (zero). If the cell being referenced is indeed blank, I need the value returned to be blank as well. I know I can turn off display of zero values globally for the sheet, but I'd rather not go that route. Ideas? Thanks, David Wrap it in an if statement: =IF(Sheet2!A5=0,"",Sheet2!A5) Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca David Lipetz wrote: > In a multi-sheet workbook, if I reference a cell in another sheet (using >...

CRM should have timesheet functionality to tie in with resourcing
The ability to add timesheets quickly and easily would work very well if could be integrated with the already outstanding resourcing functionality. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrows...

vlookup worksheet function for external excel files
I am using vlookup to retrieve data in an external excel file -i.e. the data I am trying to retrieve is not stored in the same workbook nor network drive as the records I am trying to find data for. Unfortunately, vlookup requires that the data source excel sheet file is open if it is to return records. Do you know if there is a way to retrieve data using vlookup while keeping the source closed. Thanks and regards, Gustavo I don't think =vlookup() has this requirement. Are you using =indirect() with =vlookup()? Gustavo wrote: > > I am using vlookup to retrieve data in an ex...

function arguments
may i know what to put inside the number, ref and order in the function arguments box thanks lee sok har Hi Lee Sok Har! Enter the function using the Insert Function dialog. In Excel 2002 onwards you can use: Tools > Options > General Check "Function Tooltips" OK And if you email direct I'll send you a Functions List file that gives source, classifications, descriptions, syntax and argument descriptions for all Excel and Analysis ToolPak functions. It includes direct links into help for each function. There's also an (incomplete) translation of the function...

Error for one user with Dir Function
Hi all, For a procedure in a database (Access 2003), one user gets an error message, but others do not. If I log on to his computer, I do not get the error message. The error is Bad File Name or Number. This occurs on the dir function below in the code sample, when the folder does not exist. Let strFolder = dir(strTemp, vbDirectory) If Len(strFolder) = 0 Then Let intResponse = MsgBox("Unable to find folder for " & strJob & "." & vbCrLf & _ "Do you want to browse for the ad file?", vbInformation + vbYesNo, "No P...

Keys and references
I have an element named "vendingMachine". This may appear in one of two places <customerSite> <vendingMachines> or <plannedVisit> <installationTask> Because of this I made an element named vendingMachine and simply referred to it when needed <xs:element ref="vendingMachine"/>. Various other parts of my XML need to refer to a vending machine by its serial number. My questions are 1) How do I define a key on vendingMachine @serialNumber so that it is unique within the whole document no matter where it appears 2) How do I define...

=combin function
Hi, I want to know the combinations of four letter that are possible from a list ( abcdefghij ) . The =combin function will tell me how many combinations are possible. =combin(10,4) I want a list of all the possible combinations of 4 letters for the list. abcd acbd addc .... etc. Is there away to have excel 2000 do this?? Thank you, Jerry Using a macro (this will populate A1:A210 of the active sheet) Sub test() Dim i As Long, j As Long Dim k As Long, l As Long Dim Count As Long Dim Data As Variant Count = 0 Data = Array("a", "b", "c", _ "d"...