Excel 2007 (Beta) - strange behavior of macro

G

Guest

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.
 
J

Jim Rech

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
|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.
 
G

Guest

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
 
J

Jim Rech

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
| 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
 
J

Jim Rech

Ok, well I guess I cannot reproduce the problem.

--
Jim
| 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
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to create a varying named range 2
Insert Named Range using Excel Macro 1
Naming a range in a module 5
Excel macro to control Word Problem 5
Workaround for Excel 2002/2003 UDFs recalculating and changing cel 9
Macro to Create Name Ranges for variable lengths of Data 9
Code No Longer Works in 2007 2
Strange worksheet protection behavior 5

Top