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

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

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

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

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

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

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