Hi,

Simple question.

Part of my workbook has numbered sheets (1,2, etc. - it has others as well).
I would like to add sheets using a macro, calling them by the next number in
the sequence - eg if the last sheet is 10, the next sheets should be named
11, 12, etc. (Sheets.Count for the last sheet is not the same as its name
and can vary from workbook to workbook).

If I capture the name of the last sheet (Sheets.Name), the macro deals with
it as a string and thus I am encountering a problem raising its value. How
does one do arithmetic on a string or more specifically, how does one convert
a string into a number?

Conversely, how would one change a number into a string (not needed for
this macro, but generally useful to know)?

---
Eugene

how does one convert a string into a number?

Use the Val or CInt function.

how would one change a number into a string?

Use the CStr function.

Hth,
Merjet

to convert a string to a number use Cint():

Sub demo()
Dim s As String, i As Integer
s = "1"
i = CInt(s)
MsgBox (i)
End Sub



--
Gary''s Student
gsnu200707


"eugene" wrote:

Hi,

Simple question.

Part of my workbook has numbered sheets (1,2, etc. - it has others as well).
I would like to add sheets using a macro, calling them by the next number in
the sequence - eg if the last sheet is 10, the next sheets should be named
11, 12, etc. (Sheets.Count for the last sheet is not the same as its name
and can vary from workbook to workbook).

If I capture the name of the last sheet (Sheets.Name), the macro deals with
it as a string and thus I am encountering a problem raising its value. How
does one do arithmetic on a string or more specifically, how does one convert
a string into a number?

Conversely, how would one change a number into a string (not needed for
this macro, but generally useful to know)?

---
Eugene

Thanks both of you
--
eugene


"merjet" wrote:

how does one convert a string into a number?

Use the Val or CInt function.

how would one change a number into a string?

Use the CStr function.

Hth,
Merjet

What version of Excel are you using?
I've got 2003 and the following code retrives the sheet name (which I've
renamed to the number 1) and then adds 1 and it comes up with the value of 2
and then adds a new sheet named 2

x = Sheets(1).Name
x = x + 1
Sheets.Add.Name = x

even if I start with

Dim x As String

VBA converts the string 1 to a value when I try to add 1 to the value

Is there something else going on with your code that might be causing a
problem here?

also one way to convert your number to a string is
y = Str(3)

or simply use the & function to join strings such as

y & "my text string"

both should convert a number to a string

hope this helps

David
"eugene" wrote:

Hi,

Simple question.

Part of my workbook has numbered sheets (1,2, etc. - it has others as well).
I would like to add sheets using a macro, calling them by the next number in
the sequence - eg if the last sheet is 10, the next sheets should be named
11, 12, etc. (Sheets.Count for the last sheet is not the same as its name
and can vary from workbook to workbook).

If I capture the name of the last sheet (Sheets.Name), the macro deals with
it as a string and thus I am encountering a problem raising its value. How
does one do arithmetic on a string or more specifically, how does one convert
a string into a number?

Conversely, how would one change a number into a string (not needed for
this macro, but generally useful to know)?

---
Eugene

Thanks. I'll start over and try your suggestions. You are probably right that
my code is bad. If I encounter a problem with what you say, I'll post it.
Otherwise, one can assume that what you are saying works.
--
eugene


"dkinn" wrote:

What version of Excel are you using?
I've got 2003 and the following code retrives the sheet name (which I've
renamed to the number 1) and then adds 1 and it comes up with the value of 2
and then adds a new sheet named 2

x = Sheets(1).Name
x = x + 1
Sheets.Add.Name = x

even if I start with

Dim x As String

VBA converts the string 1 to a value when I try to add 1 to the value

Is there something else going on with your code that might be causing a
problem here?

also one way to convert your number to a string is
y = Str(3)

or simply use the & function to join strings such as

y & "my text string"

both should convert a number to a string

hope this helps

David
"eugene" wrote:

Hi,

Simple question.

Part of my workbook has numbered sheets (1,2, etc. - it has others as well).
I would like to add sheets using a macro, calling them by the next number in
the sequence - eg if the last sheet is 10, the next sheets should be named
11, 12, etc. (Sheets.Count for the last sheet is not the same as its name
and can vary from workbook to workbook).

If I capture the name of the last sheet (Sheets.Name), the macro deals with
it as a string and thus I am encountering a problem raising its value. How
does one do arithmetic on a string or more specifically, how does one convert
a string into a number?

Conversely, how would one change a number into a string (not needed for
this macro, but generally useful to know)?

---
Eugene

dkinn,

You are right. Iin fact, my code had used part of what you say. But still
conversion is necessary and that's why I was having problems.

At some point my code selects Sheets(sheetname). Since my sheet names are
the same as the numbers Excel assigns to sheets, it is necessary to make sure
that "sheetname" is a string, not an integer. Sheets("1") is not the same as
Sheets(1), unless Sheets("1") is in fact the first sheet (which it is not in
my workbook).

So it is not possible for me to simply take the name and add a number. I
have to convert that number back to a string for things to work properly. (I
guess the second question in my original post is thus more important than my
first.)

Anyway, thanks again to all. Problem solved.

eugene


"dkinn" wrote:

What version of Excel are you using?
I've got 2003 and the following code retrives the sheet name (which I've
renamed to the number 1) and then adds 1 and it comes up with the value of 2
and then adds a new sheet named 2

x = Sheets(1).Name
x = x + 1
Sheets.Add.Name = x

even if I start with

Dim x As String

VBA converts the string 1 to a value when I try to add 1 to the value

Is there something else going on with your code that might be causing a
problem here?

also one way to convert your number to a string is
y = Str(3)

or simply use the & function to join strings such as

y & "my text string"

both should convert a number to a string

hope this helps

David
"eugene" wrote:

Hi,

Simple question.

Part of my workbook has numbered sheets (1,2, etc. - it has others as well).
I would like to add sheets using a macro, calling them by the next number in
the sequence - eg if the last sheet is 10, the next sheets should be named
11, 12, etc. (Sheets.Count for the last sheet is not the same as its name
and can vary from workbook to workbook).

If I capture the name of the last sheet (Sheets.Name), the macro deals with
it as a string and thus I am encountering a problem raising its value. How
does one do arithmetic on a string or more specifically, how does one convert
a string into a number?

Conversely, how would one change a number into a string (not needed for
this macro, but generally useful to know)?

---
Eugene

Sorry for any mis-communications there, but sometimes that happens through
the NG's but glad to here that your problem is corrected.

I just enjoy seeing how varied the uses for excel really are and for me the
fun is helping to come up with answers when problems do arise.

Have a great day

David

"eugene" wrote:

Hi,

Simple question.

Part of my workbook has numbered sheets (1,2, etc. - it has others as well).
I would like to add sheets using a macro, calling them by the next number in
the sequence - eg if the last sheet is 10, the next sheets should be named
11, 12, etc. (Sheets.Count for the last sheet is not the same as its name
and can vary from workbook to workbook).

If I capture the name of the last sheet (Sheets.Name), the macro deals with
it as a string and thus I am encountering a problem raising its value. How
does one do arithmetic on a string or more specifically, how does one convert
a string into a number?

Conversely, how would one change a number into a string (not needed for
this macro, but generally useful to know)?

---
Eugene