Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell vs range problem with formula
Hi,
i try to make a new workbook with two sheets named "Report" and "Data" and put a formula there. Public wbNew As Workbook Set wbNew = Application.Workbooks.Add(ActiveWorkbook.Path & "\new.xlt") .... a) wbNew.Worksheets("Report").Cells(1, 1).FormulaR1C1 = "=round(Data!R1C1,1)" b) wbNew.Worksheets("Report").Range("a1").FormulaR1C1 = "=round(Data!R1C1,1)" ... If I try to use a) with cells, in sheet there is is #name? error. When I use F2 for editting e cell and I press Enter (nothing changed), it's OK and calculates. When I use this approach in one workbook it works, but I use one workbook from anoher. If I try to use b) It works fine Does anymody know whats the problem? For me it's better to use cells property in my program. Thanks, Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell vs range problem with formula
Użytkownik "Martin Skrenek" napisał w wiadomości ... Hi, i try to make a new workbook with two sheets named "Report" and "Data" and put a formula there. Public wbNew As Workbook Set wbNew = Application.Workbooks.Add(ActiveWorkbook.Path & "\new.xlt") ... a) wbNew.Worksheets("Report").Cells(1, 1).FormulaR1C1 = "=round(Data!R1C1,1)" b) wbNew.Worksheets("Report").Range("a1").FormulaR1C1 = "=round(Data!R1C1,1)" .. If I try to use a) with cells, in sheet there is is #name? error. When I use F2 for editting e cell and I press Enter (nothing changed), it's OK and calculates. When I use this approach in one workbook it works, but I use one workbook from anoher. If I try to use b) It works fine Does anymody know whats the problem? For me it's better to use cells property in my program. Thanks, Martin try this: a) wbNew.Worksheets("Report").Range(Cells(x1, x2),Cells(x3, x4)).FormulaR1C1 = "=round(Data!R1C1,1)" where x1, x2, x3, x4 are variables, or just put 1 instead of it |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell vs range problem with formula
Martin,
Approach a ) should work, and it worked fine in my test. Making asking silly questions here, but does the template already have sheets named Report and Data? How did you run the code, versions a and b)? -- HTH RP (remove nothere from the email address if mailing direct) "Martin Skrenek" wrote in message ... Hi, i try to make a new workbook with two sheets named "Report" and "Data" and put a formula there. Public wbNew As Workbook Set wbNew = Application.Workbooks.Add(ActiveWorkbook.Path & "\new.xlt") ... a) wbNew.Worksheets("Report").Cells(1, 1).FormulaR1C1 = "=round(Data!R1C1,1)" b) wbNew.Worksheets("Report").Range("a1").FormulaR1C1 = "=round(Data!R1C1,1)" .. If I try to use a) with cells, in sheet there is is #name? error. When I use F2 for editting e cell and I press Enter (nothing changed), it's OK and calculates. When I use this approach in one workbook it works, but I use one workbook from anoher. If I try to use b) It works fine Does anymody know whats the problem? For me it's better to use cells property in my program. Thanks, Martin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell vs range problem with formula
Hi Martin,
Both your Range & Cells methods worked for me when I tried this: 'code in a saved test file with a Path Sub NewXLT() Dim wb As Workbook Set wb = Workbooks.Add wb.Worksheets(1).Name = "Report" wb.Worksheets(2).Name = "Data" wb.SaveAs ThisWorkbook.Path & "\New.xlt", xlTemplate wb.Close Set wb = Nothing End Sub Sub CellRange() Dim wbNew As Workbook Set wbNew = Application.Workbooks.Add(ThisWorkbook.Path & "\new.xlt") wbNew.Worksheets("Report").Cells(1, 1).FormulaR1C1 = _ "=round(Data!R1C1,1)" Set wbNew = Application.Workbooks.Add(ThisWorkbook.Path & "\new.xlt") wbNew.Worksheets("Report").Range("a1").FormulaR1C1 = _ "=round(Data!R1C1,1)" set wbNew = Nothing End Sub Only change to your code is ThisWorkbook.Path instead of ActiveWorkbook.Path Both "New" files showed A1 =ROUND(Data!$A$1,1) Regards, Peter T "Martin Skrenek" wrote in message ... Hi, i try to make a new workbook with two sheets named "Report" and "Data" and put a formula there. Public wbNew As Workbook Set wbNew = Application.Workbooks.Add(ActiveWorkbook.Path & "\new.xlt") ... a) wbNew.Worksheets("Report").Cells(1, 1).FormulaR1C1 = "=round(Data!R1C1,1)" b) wbNew.Worksheets("Report").Range("a1").FormulaR1C1 = "=round(Data!R1C1,1)" .. If I try to use a) with cells, in sheet there is is #name? error. When I use F2 for editting e cell and I press Enter (nothing changed), it's OK and calculates. When I use this approach in one workbook it works, but I use one workbook from anoher. If I try to use b) It works fine Does anymody know whats the problem? For me it's better to use cells property in my program. Thanks, Martin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell vs range problem with formula
Hi Bob,
you are right, both versions works now. I had just frsh installation of multi language pack and when I switched from Czech lanhuage to English and back, it works now without any change in my code. Thanks for help, Martin "Bob Phillips" wrote in message ... Martin, Approach a ) should work, and it worked fine in my test. Making asking silly questions here, but does the template already have sheets named Report and Data? How did you run the code, versions a and b)? -- HTH RP (remove nothere from the email address if mailing direct) "Martin Skrenek" wrote in message ... Hi, i try to make a new workbook with two sheets named "Report" and "Data" and put a formula there. Public wbNew As Workbook Set wbNew = Application.Workbooks.Add(ActiveWorkbook.Path & "\new.xlt") ... a) wbNew.Worksheets("Report").Cells(1, 1).FormulaR1C1 = "=round(Data!R1C1,1)" b) wbNew.Worksheets("Report").Range("a1").FormulaR1C1 = "=round(Data!R1C1,1)" .. If I try to use a) with cells, in sheet there is is #name? error. When I use F2 for editting e cell and I press Enter (nothing changed), it's OK and calculates. When I use this approach in one workbook it works, but I use one workbook from anoher. If I try to use b) It works fine Does anymody know whats the problem? For me it's better to use cells property in my program. Thanks, Martin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell vs range problem with formula
Both versions are all right. When I switched languages in multilangue pack
to english and back, it works. Thanks for concerning to everybody, Martin "Martin Skrenek" wrote in message ... Hi, i try to make a new workbook with two sheets named "Report" and "Data" and put a formula there. Public wbNew As Workbook Set wbNew = Application.Workbooks.Add(ActiveWorkbook.Path & "\new.xlt") ... a) wbNew.Worksheets("Report").Cells(1, 1).FormulaR1C1 = "=round(Data!R1C1,1)" b) wbNew.Worksheets("Report").Range("a1").FormulaR1C1 = "=round(Data!R1C1,1)" .. If I try to use a) with cells, in sheet there is is #name? error. When I use F2 for editting e cell and I press Enter (nothing changed), it's OK and calculates. When I use this approach in one workbook it works, but I use one workbook from anoher. If I try to use b) It works fine Does anymody know whats the problem? For me it's better to use cells property in my program. Thanks, Martin |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell vs range problem with formula
Nice approach I didn't know that,
Thanks "Snake Plissken" wrote in message ... Użytkownik "Martin Skrenek" napisał w wiadomości ... Hi, i try to make a new workbook with two sheets named "Report" and "Data" and put a formula there. Public wbNew As Workbook Set wbNew = Application.Workbooks.Add(ActiveWorkbook.Path & "\new.xlt") ... a) wbNew.Worksheets("Report").Cells(1, 1).FormulaR1C1 = "=round(Data!R1C1,1)" b) wbNew.Worksheets("Report").Range("a1").FormulaR1C1 = "=round(Data!R1C1,1)" .. If I try to use a) with cells, in sheet there is is #name? error. When I use F2 for editting e cell and I press Enter (nothing changed), it's OK and calculates. When I use this approach in one workbook it works, but I use one workbook from anoher. If I try to use b) It works fine Does anymody know whats the problem? For me it's better to use cells property in my program. Thanks, Martin try this: a) wbNew.Worksheets("Report").Range(Cells(x1, x2),Cells(x3, x4)).FormulaR1C1 = "=round(Data!R1C1,1)" where x1, x2, x3, x4 are variables, or just put 1 instead of it |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup-cell range in formula changes when copied to another cell | Excel Discussion (Misc queries) | |||
How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
function problem regarding cell range | Excel Worksheet Functions | |||
Problem copying formula to range of cells | Setting up and Configuration of Excel | |||
Reference range in formula problem | Excel Programming |