cell vs range problem with formula

[Martin Skrenek]

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

 

[Snake Plissken]

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

 

[Bob Phillips]

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)

 

[Peter T]

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]

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

 

[Martin Skrenek]

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]

Nice approach I didn't know that,
Thanks

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