Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative cell references
Hi,
I am fairly new to programming in VBA/Excel, and find myself having trouble with the most seemingly straightforward things... I am working on a macro that creates a new worksheet and a new row in an existing worksheet, and links a cell in the new row to a cell in the new worksheet. The relevant code (from a recorded macro) is as follows: Sub SettInnNy() Windows("innretninger.xls").Activate Sheets("innretninger").Select Range("B108").Select Selection.EntireRow.Insert Windows("data.xls").Activate Sheets("Ny").Copy Befo=Sheets(1) Windows("innretninger.xls").Activate Range("H108").Select ActiveCell.FormulaR1C1 = "='[data.xls]'Sheets(1)!R12C5" End Sub It gives the following error message: <Run-time error '1004': Application-defined or object-defined error When recorded the last line is ActiveCell.FormulaR1C1 = "='[data.xls]Ny (2)'!R12C5" which works fine the first time, but the second and third time keeps on referring to the same sheet, when it should refer to the newest one. It is obviously my modification that isn't working -- any help on how to do this would be greatly appreciated. Thomas *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative cell references
Hi Thomas,
Try this: Sub SettInnNy() With Workbooks("data.xls") .Sheets("Ny").Copy Befo=.Sheets(1) wsName = .Sheets(1).Name End With With Workbooks("innretninger.xls").Sheets("innretninger ") .Rows(108).Insert .Range("H108").FormulaR1C1 = "='[data.xls]" & _ wsName & "'!R12C5" End With End Sub Regards, KL "Thomas F" wrote in message ... Hi, I am fairly new to programming in VBA/Excel, and find myself having trouble with the most seemingly straightforward things... I am working on a macro that creates a new worksheet and a new row in an existing worksheet, and links a cell in the new row to a cell in the new worksheet. The relevant code (from a recorded macro) is as follows: Sub SettInnNy() Windows("innretninger.xls").Activate Sheets("innretninger").Select Range("B108").Select Selection.EntireRow.Insert Windows("data.xls").Activate Sheets("Ny").Copy Befo=Sheets(1) Windows("innretninger.xls").Activate Range("H108").Select ActiveCell.FormulaR1C1 = "='[data.xls]'Sheets(1)!R12C5" End Sub It gives the following error message: <Run-time error '1004': Application-defined or object-defined error When recorded the last line is ActiveCell.FormulaR1C1 = "='[data.xls]Ny (2)'!R12C5" which works fine the first time, but the second and third time keeps on referring to the same sheet, when it should refer to the newest one. It is obviously my modification that isn't working -- any help on how to do this would be greatly appreciated. Thomas *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative cell references
Thanks a million, KL -- not only did it work, it also gave me some faith
in VBA. Good to see that things can be done more elegantly than what comes out of the record-macro function. *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative cell references
You're welcome. And yes, the macro recorder is good for quick'n'dirty code,
whcih then has to be tidied up for optimal performance. It is also a great tool to quickly identify objects, properties, methods, etc. you may need to use. KL "Thomas F" wrote in message ... Thanks a million, KL -- not only did it work, it also gave me some faith in VBA. Good to see that things can be done more elegantly than what comes out of the record-macro function. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute and Relative Cell References | Excel Discussion (Misc queries) | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Relative Cell References within VBA code | Excel Discussion (Misc queries) | |||
Transpose-relative cell references would be useful. | Excel Worksheet Functions | |||
how to use relative cell references with solver | Excel Programming |