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"

-- 
Celia
0
Utf
1/1/2010 4:31:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
786 Views

Similar Articles

[PageSpeed] 23

If I understand what you want correctly, you are missing a quote mark in 
front of the first apostrophe...

ActiveCell.FormulaR1C1 = "=" & budget_file & "'monthly'!$r$16"

-- 
Rick (MVP - Excel)


"Celia" <Celia@discussions.microsoft.com> wrote in message 
news:5113110C-D13A-4B0E-B33B-A81DDA563EDD@microsoft.com...
>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"
>
> -- 
> Celia 

0
Rick
1/1/2010 4:40:32 PM
Hi,

and this,

'-----------------------------------
Dim Path As String, File As String, Sh As String

Path = "c:\"
File = "Test.xlam"
Sh = "Sheet1"
ref = "A2"

ActiveCell.Formula = "='" & Path & "[" & File & "]" & Sh & "'!" & _
    Range(ref).Address(, , A1)
'-----------------------------------



"Celia" <Celia@discussions.microsoft.com> a écrit dans le message de groupe de discussion 
: 5113110C-D13A-4B0E-B33B-A81DDA563EDD@microsoft.com...
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"

-- 
Celia 

0
michdenis
1/1/2010 4:46:58 PM
Hi Celia

assuming your variable budget_file has been set up as something like
"'[Accounts2009.xls]"
(Note the single quote before the opening bracket '[ )
then the line should read

ActiveCell.FormulaR1C1 = "=" & budget_file & "monthly'!$r$16"
(note the change from your single quote to a double quote)
-- 
Regards
Roger Govier

"Celia" <Celia@discussions.microsoft.com> wrote in message 
news:5113110C-D13A-4B0E-B33B-A81DDA563EDD@microsoft.com...
> 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"
>
> -- 
> Celia
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4735 (20100101) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4735 (20100101) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
1/1/2010 5:07:15 PM
Reply:

Similar Artilces:

Macro Only working on my PC
Hi, I am very new to Macro's and have no experience. I have created a VERY basic macro on a spreadsheet, where you click a button named "complete" and it automaticly turns the spreadsheet into a attachment on an email. It works perfectly fine on my PC, but it doesn't work for anyone else?? Can anyone help please? PS I am using excel 2007 How about posting the code so we can see what you are doing? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "J Wheatley" <J Wheatley@discussions.microsoft.com&...

Hiding Macro Buttons when right-clicking>Hiding Columns
I've recorded a macro on a spreadsheet and assigned it to a button. I'd like to hide that button along with several columns using the right-click>Hide command. When I hide the columns, the button is still there. Is there anyway to hide the botton with those columns, maybe by embedding the button in a cell? Thanks -- Brooks For the button properties, check to move and size with cells. Then it will hide when you hid the underlying column or row. Richard Choate "Brooks" <brooks.b.finnegan@accenture.com> wrote in message news:023901c34bc4$0a249a30$a301280a@p...

Shortening SUMPRODUCT formulas
Hi all. This is a re-post, in a sense, of a question I posted last week to this forum. I work at a college and use SUMPRODUCT formulas to total up all kinds of stuff. For example, to calculate the number of people majoring in a particular area (in this case, Social Sciences and Arts), I use the following: =SUMPRODUCT(((Major="African/African Am Studies")+(Major="Ancient History")+(Major="Art")+(Major="Art History")+(Major="Asian Studies")+(Major="Chinese")+(Major="Classics")+(Major="Classical Studies")+(Major=&q...

making SQL the source for a report?
I am currently rewriting a big database from using things like DoCmd.OpenReport "Rpt_Y10_A2C_movement_by_student", acViewPreview, where the report's data source is a saved query, to something like Set db = CurrentDb strSQL = SQL_Y10_A2C_movement_by_student 'generate sql from a function db.Execute strSQL, dbFailOnError 'run sql Set db = Nothing where the SQL (copied from the original query) is held in a function eg Private Function SQL_Qry_Y10_A2C_movement_by_student() As String Dim s As String s = "" s = s & "SELECT Y10_ALL_Est_nu...

how do i make a word flash or blink in excel
How do i make a word (text) blink or flash in excel Most advise not too... Check out Chip's solution and advice on flashing cells/words. Read completely, it may be against the law in certain situations. http://www.cpearson.com/excel/BlinkingText.aspx HTH Regards, Howard "Rob" <Rob@discussions.microsoft.com> wrote in message news:C69A402E-0DAA-49E1-A5A5-763DB0F6BFFB@microsoft.com... > How do i make a word (text) blink or flash in excel ...

help with moving formula
here is my formula: =COUNTIF(OFFSET('Daily MP'!A:A,0,MATCH(H3,'Daily MP'!5:5,0)-1),"*04ac14*")-H10 when I drag my formula to the next column I only want certain values to change. For instance in my reference of my offset I want the A:A to stay A:A and not change to B:B. But in my match I want my lookup value to change from H3 to I3. How would I solve this? Hi use =COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H3,'Daily MP'!$5:$5,0)-1),"*04ac14*")-H10 -- Regards Frank Kabel Frankfurt, Germany "jtinne" <jtinne@discussions.micros...

standard cost
We have a Make item that is set up as FIFO Periodic. There was an item on the BOM for it that was FIFO Perpetual (normally you wouldn't be able to add a FIFO Perpetual item to the BOM for a FIFO Periodic item, but we push our BOMs in using a customized utility). I changed the Valuation Method for the BOM Item from FIFO Perpetual to FIFO Periodic using the Utilities > Inventory > Change Valuation. A pending material cost was entered for this item and the standard cost rolled up fine, but the standard cost for the Make item still will not roll up. We've tried deleting th...

Translate formula into VBA
I have a simple formula that I would like incorporate into my vba t loop through the document but it does seem to work. =IF(COUNTIF($C$1:C20,C20)=1,C20," ") ActiveCell "=If(Countif($R[0]$C[1]:R[0]C[1],R[0]C[1])=""1"",R[0]C[1],"" "")" Thanks in advance -- shtern ----------------------------------------------------------------------- shternm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=85 View this thread: http://www.excelforum.com/showthread.php?threadid=48601 How about ACtivecell.Formula = &q...

target #4 operation whirlwind part II
2b 1a 36 f7 4e f8 1a dc 7d be a2 c3 dc 60 73 65 50 22 32 3e ae 2b 4a 89 8a d1 cc 67 ac 5f 87 1e dd bd ac 1f 50 72 c0 ee 5c f7 56 eb fd 34 30 6c 17 c0 4f 4b 0d 81 ae d0 ba bf 2a 4c 53 79 2b 2a db c3 57 8f 87 52 01 81 62 32 d8 f3 2b b7 ac 8f 13 a6 b7 de 09 53 86 84 d3 79 b0 b8 d4 c4 c0 79 a6 b8 9b 2e f3 57 3e 72 94 4b e6 2c af 32 8a 31 24 af 92 11 a6 cd 8e 83 e0 a9 5c c4 03 87 b3 3a 84 12 69 7e 84 81 10 a2 63 28 65 2f ea 22 89 8f 4c af 80 44 cf 4a 26 e5 15 cb 3a 1a ...

Visual Basic ADODB Open Connection Make Table Query
I have the following code: - the code runs well, I get a message saying connection is open, but when I try to run the make table query I still get the login prompt - how do I avoid the login prompt? Many thanks! Dim qryArch112 As String qryArch112 = "" qryArch112 = "select DBOBJECTS_UDWEBFORMS_ARCHFRM_112_VIEW.PROPOSAL INTO tblArch112 FROM DBOBJECTS_UDWEBFORMS_ARCHFRM_112_VIEW;" '---open UD01 Dim oConn3 As ADODB.Connection Set oConn3 = New ADODB.Connection Dim sr As String sr = "pr01" If (udun <> "" And udpwd <>...

Macro Problem..... need help !
hai, i need help How to do the macro that can 1st check the right(same) name between tw excel, than pull out the data -- Irmann Jim Cone has a fine commercial Add-in program that wil manage "duplicates" very nicely.....take a lookk it may be just what you want....it's at http://www.realezsites.com/bus/primitivesoftware/ Vaya con Dios, Chuck, CABGx3 "Irmann" wrote: > > hai, i need help > > How to do the macro that can 1st check the right(same) name between two > excel, than pull out the data ? > > > > > -- > Irmann &...

Macro to clean empty cells
As I have had some great help from this group before, here is another request. Am looking to clean up some sheets in various workbooks in Excel 2007 Anybody able to help me out with a macro that will look for empty cells and then clear them out of all formats or hidden characters etc. that they may have but cant be seen. Will only need to run it on individual named sheets rather than on whole workbook many thanks ...

Formula shows as the result?
When I link a cell within a workbook the formula shows where to result should. How do I fix this. Most probably, the cell was formatted as Text before you entered the formula. Change the format to "Number" or "General", then re-enter the formula. Keyboard shortcut is: Select the cell, then, <Ctrl> <Shift> <~>, Then <F2>, Then <Enter>. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------...

preventing formulas from deletion
How do I prevent a cell (or a group of cells) from having their contents being accidentally deleted, but still allowing data entry? The cell has a formula which I would permit to be overwritten, but I just don't want its contents to be deleted outright. Thanks in advance. One way would be to put the formulas BACK (with a macro) on exiting the file -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "ACLrecon" <raichu.526@gmail.com> wrote in message news:d225a09d-0441-4db2-8e81-dd65a2e85348@a9g2000prl.googlegroups.com... > How do I prevent ...

what are the formulas to minus from excel
I'M TRYING TO BALANCE MY CHECK BOOK ON EXCEL AND I FORGOT HOW TO MINUS. CAN ANYONE PLEASE TELL ME THE FORMULAS THANK YOU, lastbalance-withdrawal+deposit "MARIA" <MARIA@discussions.microsoft.com> wrote in message news:B6DEAD76-1FB7-44BB-841E-87E0CAEB4375@microsoft.com... > I'M TRYING TO BALANCE MY CHECK BOOK ON EXCEL AND I FORGOT HOW TO MINUS. CAN > ANYONE PLEASE TELL ME THE FORMULAS > > THANK YOU, Maria, =A2-b2 will subtract the value in cell A2 from B2 Is this what you're after, or is it something more complexed Jon -- Jon Quixley ------...

Re: How to CANCEL file SAVE PROMPT when MACRO is running?
Sorry, I hit send by mistake, Try wrapping the code in Application.DisplayAlerts = False 'Your Code Application.DisplayAlerts = True This works for XL files, not sure about Text Files but worth a try, Regards, Alan. "Alan" <alan111@ntlworld.com> wrote in message news:... > Try wrapping the code in > > Application.DisplayAlerts = False > 'Your Code > > "Stuart Macro Muppet" <Stuart Macro Muppet@discussions.microsoft.com> > wrote in message > news:6EB73479-CF82-4258-A2DD-9612CC5939AD@microsoft.com... >>I have created a ma...

I want to set a variable for 30 days
Hello what i want is that, I am making application that should be valid only for thity days Can any one Help me out how to do that Plz reply me soon thanks in advance meenal meenal a �crit : > Hello what i want is that, I am making application that should be valid > only for thity days > Can any one Help me out how to do that > Plz reply me soon > thanks in advance > meenal > You can try this: http://www.ionworx.net/IceLicense.html Would it work to #define the date you want to start in a resource header, and then on construction or initialization, check whether it&#...

Help with date/cost formula
This is a booking form for a holiday rental property which is completed by the `renter` and the idea is my form works out the total cost and dates when deposit and balance are due, I thought this might help to explain what I am trying to achieve. In K7,J7,I7 & G7,F7,E7 I have dates eg 22 07 2010 & 29 07 2010 So in cell J24 I have =DATE(K7,J7,I7)-DATE(G7,F7,E7) which gives me the number of days which is 7 In K24 I have a cost (�42) which is J24* N3 (which is 7 X 24) which gives me a total cost for the 7 days. (�294) This is for someone renting my property ...

Disabled macros #2
Every time I open one of my excel documents it asks me if I want to disable the macros or enable them. The only thing is there are no macros to be enable, they were all deleted. How can I stop this message from coming up, without starting my whole document from scratch? Jolene, Two things to check. Even if you have deleted the macros, if you haven't removed the code modules, you will still get the message. Have you checked the worksheet and ThisWorkbook code modules, they may have macros. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere ...

Making a chart
Hello, I'm trying to create a chart that will mimic the candlestick chart but it does not use a timeline. Instead of displaying the timeline along the X-axis, it will show the thickness of the sheet. Sample data available as follow : 1st data set: Thickness: 0.010 Min: 10 Max: 15 2nd data set: Thickness: 0.013 Min: 10 Max: 25 3rd data set: Thickness: 0.025 Min: 15 Max: 35 The chart will display the "Thickness" along the X-axis and the "Soak Range" (Min and Max) along the Y-axis. Microsoft Excel require several variable to create a stock chart, namely: Open, Hi...

Help with excel formula... Please
I want to take the LAST cell of column A (which will be changing with entries) and paste it into a fixed cell in column B. Thanks The formula: =OFFSET(A1,COUNT(A2:A1000),0) in any cell will result in the value of the last occupied cell in Column A. Note that cell A2 must be occupied. HTH Otto "ventimaj" <ventimaj@discussions.microsoft.com> wrote in message news:FDCA035C-E8F4-4ECF-9CB5-E2FBE88A89AA@microsoft.com... >I want to take the LAST cell of column A (which will be changing with > entries) and paste it into a fixed cell in column B. > > Thanks Otto...

how to detect which macros are no longer in use?
Hi all, Over the years I created a lot of macros, some in modules of certain "normal" xls files, others in my personal.xls. In some cases there are more than macro in one module. I use XP and Excel 2000. Only "normal" modules are used. Some procedures call other macros which are not necessarily in the same module. I want to get rid of macros I don't need, but need a method (a macro?) that shows me wether a macro is called for in one or more other macros and also shows me which macros they are. I have no idea how to accomplish this. Your help will be much appreciated....

Making a query to get data from a parent-child table
Hello, Im quite new to VBA coding with MS Access and may do things that most of you wouldnt so I would appreciate any further insight into question if your willing to provide it. Im creating a system for my cousin so he can basically create invoices for his company. His company has 2 different mailing addresses so I have used a table (tblCompany) to store this information. The main data is stored on a parent (Client) and child (Product) table. When I open the parent table, I can view all the products that were shipped to the particular client. This works great. ...

function true false to macro ??
I have a function that tells if a table exists. I know that I can put an error message in the function that will respond if the table is found or not. But is there a way to pass the true false to a condition in a macro in the macro builder to give a message. Eg if it is found msgbox "found" if not msbox "not found" Basically can you have code pass something to a condition in a macro. thanks. Functions are intended to return values. The function should be defined as: Function TableExists(NameOfTable As String) As Boolean and then TableExists should be defined as Tru...

No access to certian parts of sites
Hi: Lately I have been having trouble getting on certain sites. I am not an expert, but I think I know what the problem is, I just don’t know how it happened or how to solve it. I can go to any site, AOL, my banks, google, anything. The problem starts when I try to sign in. I THINK (but once again, I’m no expert), it has something to so with sites that are SECURE or have high security. For example. I can get to AOL, but when I try to sign in, I get that error message you would get if you had no internet connection. Same thing with my bank, as soon as I try and sign in, it...