VBA setting the formula for a cell

Question

I m trying to set the formula for a cell using a  dynamically created  sheet name and a fixed cell address  I m using the following line but can t seem to get it working        amp  strProjectName  amp       amp  Cells 2  7  Address   Any advice on why this isn t working or a prod in the right direction would be greatly appreciated

User · Answer

If you want to make address directly  the worksheet must exist   Turning off automatic recalculation want help you     But    you can get value indirectly      FormulaR1C1     INDIRECT ADDRESS 2 7 1 0      amp  strProjectName  amp       FALSE     At the time formula is inserted it will return  REF error  because strProjectName sheet does not exist   But after this worksheet appear Excel will calculate formula again and proper value will be shown   Disadvantage  there will be no tracking  so if you move the cell or change worksheet name  the formula will not adjust to the changes as in the direct addressing

User · Answer

If Cells 1  1  Formula gives a 1004 error  like in my case  changes it to   Cells 1  1  FormulaLocal

User · Answer

Try    Formula         amp  strProjectName  amp        amp  Cells 2  7  Address   If your worksheet name  strProjectName  has spaces  you need to include the single quotes in the formula string   If this does not resolve it  please provide more information about the specific error or failure   Update  In comments you indicate you re replacing spaces with underscores   Perhaps you are doing something like   strProjectName   Replace strProjectName             But if you re not also pushing that change to the Worksheet Name property  you can expect these to happen    The file browse dialog appears The formula returns  REF error   The reason for both is that you are passing a reference to a worksheet that doesn t exist  which is why you get the  REF error   The file dialog is an attempt to let you correct that reference  by pointing to a file wherein that sheet name does exist   When you cancel out  the  REF error is expected   So you need to do   Worksheets strProjectName  Name   Replace strProjectName           strProjectName   Replace strProjectName             Then  your formula should work

User · Answer

Not sure what isn t working in your case  but the following code will put a formula into cell A1 that will retrieve the value in the cell G2   strProjectName    Sheet1  Cells 1  1  Formula        amp  strProjectName  amp       amp  Cells 2  7  Address   The workbook and worksheet that strProjectName references must exist at the time that this formula is placed   Excel will immediately try to evaluate the formula   You might be able to stop that from happening by turning off automatic recalculation until the workbook does exist

[excel] VBA setting the formula for a cell

Examples related to excel

Examples related to vba

Examples related to cell

Examples related to formula