ExcelBanter - Excel 2007 (Beta) - strange behavior of macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Excel 2007 (Beta) - strange behavior of macro (https://www.excelbanter.com/excel-programming/377207-excel-2007-beta-strange-behavior-macro.html)

Excel 2007 (Beta) - strange behavior of macro

I have written a small macro which gives names to cells. It ran fine in Excel
2003, but behaves strange in 2007 beta.

For sor = 2 To 43
Name = Cells(sor, 1)

Cellanev1 = "=Rikishi!R" & sor & "C17"
....
Cellanev11 = "=Rikishi!R" & sor & "C29"

Ujnev1 = Nev & "_gyoz"
....
Ujnev11 = Nev & "_yusho"

Cells(sor, 17).Select
ActiveWorkbook.Names.Add Name:=Ujnev1, RefersToR1C1:=Cellanev1
....
Cells(sor, 29).Select
ActiveWorkbook.Names.Add Name:=Ujnev11, RefersToR1C1:=Cellanev11

Next sor

For being shorter, I deleted some lines, but I think it's clear this way,
too. So, in Excel 2003 it ran fine through the lines ("sor") and gave name to
each cell. But now, in 2007, the macro runs, and gives name to the first
given cell, and leaves the rest. If I run it again, it names the second cell.
A third run results a name for the third cell, etc. When I run the macro from
VBA Editor step by step with F8, then it does its job fine. Any solution? Or
is it simply a bug of beta? Hm, well, I sincerely hope they have fixed it in
the final version.

Excel 2007 (Beta) - strange behavior of macro

When I run this in Excel 2003 it just creates 2 names "_gyoz" and "_yusho",
possibly because the code you posted never defines "Nev". I'd suggest that
you rework your example and post it here if that behaves differently in the
Excel versions.

--
Jim
"András" wrote in message
...
|I have written a small macro which gives names to cells. It ran fine in
Excel
| 2003, but behaves strange in 2007 beta.
|
| For sor = 2 To 43
| Name = Cells(sor, 1)
|
|
| Cellanev1 = "=Rikishi!R" & sor & "C17"
| ...
| Cellanev11 = "=Rikishi!R" & sor & "C29"
|
|
| Ujnev1 = Nev & "_gyoz"
| ...
| Ujnev11 = Nev & "_yusho"
|
|
| Cells(sor, 17).Select
| ActiveWorkbook.Names.Add Name:=Ujnev1, RefersToR1C1:=Cellanev1
| ...
| Cells(sor, 29).Select
| ActiveWorkbook.Names.Add Name:=Ujnev11, RefersToR1C1:=Cellanev11
|
| Next sor
|
|
| For being shorter, I deleted some lines, but I think it's clear this way,
| too. So, in Excel 2003 it ran fine through the lines ("sor") and gave name
to
| each cell. But now, in 2007, the macro runs, and gives name to the first
| given cell, and leaves the rest. If I run it again, it names the second
cell.
| A third run results a name for the third cell, etc. When I run the macro
from
| VBA Editor step by step with F8, then it does its job fine. Any solution?
Or
| is it simply a bug of beta? Hm, well, I sincerely hope they have fixed it
in
| the final version.

Excel 2007 (Beta) - strange behavior of macro

Sorry, that was my mistake I made only here in my post :(. The reason of the
error is not this. here is the full code:

Sub Macro8()

Sheets("Rikishi").Select

For sor = 2 To 43
Nev = Cells(sor, 1)

Cellanev1 = "=Rikishi!R" & sor & "C17"
Cellanev2 = "=Rikishi!R" & sor & "C18"
Cellanev3 = "=Rikishi!R" & sor & "C19"
Cellanev4 = "=Rikishi!R" & sor & "C20"
Cellanev5 = "=Rikishi!R" & sor & "C21"
Cellanev6 = "=Rikishi!R" & sor & "C22"
Cellanev7 = "=Rikishi!R" & sor & "C23"
Cellanev8 = "=Rikishi!R" & sor & "C24"
Cellanev9 = "=Rikishi!R" & sor & "C27"
Cellanev10 = "=Rikishi!R" & sor & "C28"
Cellanev11 = "=Rikishi!R" & sor & "C29"

Ujnev1 = Nev & "_gyoz"
Ujnev2 = Nev & "_ver"
Ujnev3 = Nev & "_K1"
Ujnev4 = Nev & "_K2"
Ujnev5 = Nev & "_kinboshi"
Ujnev6 = Nev & "_ginboshi"
Ujnev7 = Nev & "_sanyaku"
Ujnev8 = Nev & "_koshi"
Ujnev9 = Nev & "_extra"
Ujnev10 = Nev & "_sansho"
Ujnev11 = Nev & "_yusho"

Cells(sor, 17).Select
ActiveWorkbook.Names.Add Name:=Ujnev1, RefersToR1C1:=Cellanev1
Cells(sor, 18).Select
ActiveWorkbook.Names.Add Name:=Ujnev2, RefersToR1C1:=Cellanev2
Cells(sor, 19).Select
ActiveWorkbook.Names.Add Name:=Ujnev3, RefersToR1C1:=Cellanev3
Cells(sor, 20).Select
ActiveWorkbook.Names.Add Name:=Ujnev4, RefersToR1C1:=Cellanev4
Cells(sor, 21).Select
ActiveWorkbook.Names.Add Name:=Ujnev5, RefersToR1C1:=Cellanev5
Cells(sor, 22).Select
ActiveWorkbook.Names.Add Name:=Ujnev6, RefersToR1C1:=Cellanev6
Cells(sor, 23).Select
ActiveWorkbook.Names.Add Name:=Ujnev7, RefersToR1C1:=Cellanev7
Cells(sor, 24).Select
ActiveWorkbook.Names.Add Name:=Ujnev8, RefersToR1C1:=Cellanev8
Cells(sor, 27).Select
ActiveWorkbook.Names.Add Name:=Ujnev9, RefersToR1C1:=Cellanev9
Cells(sor, 28).Select
ActiveWorkbook.Names.Add Name:=Ujnev10, RefersToR1C1:=Cellanev10
Cells(sor, 29).Select
ActiveWorkbook.Names.Add Name:=Ujnev11, RefersToR1C1:=Cellanev11

Next sor
End Sub

"Jim Rech" wrote:

When I run this in Excel 2003 it just creates 2 names "_gyoz" and "_yusho",
possibly because the code you posted never defines "Nev". I'd suggest that
you rework your example and post it here if that behaves differently in the
Excel versions.

--
Jim

Excel 2007 (Beta) - strange behavior of macro

Excel 2007 (Beta) - strange behavior of macro

Yes, it's Beta 2TR.

"Jim Rech" wrote:

I gather I should fill in unique strings in A2:A43. After doing so I ran
your code in XL2003 and 2007. In both I had 462 names created. Are you
using Beta 2 TR?

--
Jim

Excel 2007 (Beta) - strange behavior of macro

Excel 2007 (Beta) - strange behavior of macro

OK, thanks for trying helping anyway, Jim.
I think I'll wait for the final release. Hopefully there won't be this
problem.

"Jim Rech" wrote:

Ok, well I guess I cannot reproduce the problem.

--
Jim