combining numbers and letters in a string

[smiler44]

Sorry if asked before but it seems I've forgotten what I have done.
is it possible to combine letters and numbers in a string?
I can not make the code below no matter how I write it. Am I right in thinking this can not be done? I have tried declaring cnt as an integer but that does not work.


Private Sub CommandButton2_Click()
Dim sht As String
Dim cnt As String
Dim bth As String
sht = "sheet"
cnt = "1"
bth = sht + cnt
If ActiveSheet.Name = bth Then
MsgBox ("hi")
End If
End Sub

thank you

smiler44

 

[Bilbo_Baggins_Esq]

You don't want to put the () on the msgbox, but that code should work.

use msgbox "hi"

I see a lot of people use + to concatentate, but I always use & because in some circumstances + can cause problems.

 

[Bilbo_Baggins_Esq]

oh, wait a minute, I see what's wrong

 

[Bilbo_Baggins_Esq]

Try this

Private Sub CommandButton2_Click()
Dim sht As String
Dim cnt As String
Dim bth As String
sht = "Sheet"
cnt = "1"
bth = sht + cnt
If ActiveSheet.Name = bth Then
MsgBox ("hi")
End If
End Sub

You don't need the () on the message box, but it is ok
You can use + to concatenate, but as I mentioned, it can cause problems

 

[smiler44]

Have tried replacing the + with & but alas it still does not work. I'm sure its to do with numbers and letters being in the same string and or I am declaring the variables wrong

smiler44

 

[Bilbo_Baggins_Esq]

Here is an example of why you don't want to get in a habit of using + to concatenate

Let's say you make cnt an integer so you can use it as a counter for checking other sheets

bth = sht + cnt would return an error
bth = sht & cnt would work fine

 

[Bilbo_Baggins_Esq]

look closely at my code.

YOu need to capitalize the "S" in "sheet"

 

[Brianwarnock]

Bilbo correctly altered the s tp S in Sheet but you need to code something like
sheetname=Activesheet.name
then compare that to bth

edit No you don't, just tried it , Bilbo's code is good.

pardon me for doubting it

Brian

 

[Brianwarnock]

Here is an example of why you don't want to get in a habit of using + to concatenate

Let's say you make cnt an integer so you can use it as a counter for checking other sheets

bth = sht + cnt would return an error
bth = sht & cnt would work fine

Used in these circumstances 1 has to be a string. To index through a collection you would have to use Sheets(1) etc.

Brian

 

[Brianwarnock]

I see Smiler has gone, this is not the first time he has signed off without a thanks its working now.
Think I'll skip him in future.

Brian

 

[Bilbo_Baggins_Esq]

Used in these circumstances 1 has to be a string. To index through a collection you would have to use Sheets(1) etc.

Brian

Hmmm, are you sure?

This code works fine?

Private Sub CommandButton2_Click()
Dim sht As String
Dim cnt As Integer
Dim bth As String
sht = "Sheet"
cnt = 1
bth = sht & cnt
If ActiveSheet.Name = bth Then
MsgBox "hi"
End If
End Sub

 

[Brianwarnock]

Then I guess the answer is No , but I'm sure I got a type mismatch.
Guess I shouldn't work on a Saturday.

Brian

 

[Brianwarnock]

Damn, well that proves your point, I left the + instead of changing to &

Brian

 

[Brianwarnock]

Stop laughing

 

[Bilbo_Baggins_Esq]

Then I guess the answer is No , but I'm sure I got a type mismatch.
Guess I shouldn't work on a Saturday.

Brian

none of us should!

 

[Bilbo_Baggins_Esq]

Stop laughing

lol, don't deprive me of my one time!!!!

jeeze, nobody even got any rep either.

c'est la vive

 

[smiler44]

Sorry for not staying on line but I never know when I get a reply. I post my question then go off and do something else coming back later as I have just done.
FWIW the last help I got I did add to the reputaion of those that had helped.
smiler44

 

[smiler44]

Bilbo Baggins Esg, thank you. I have copied and pasted the code and it works fine. I had not capitalized the S. It would not have occured to me to do so. So close but yet so far.

Thnak you again
smiler44

 

[smiler44]

I found on the internet a way to convert a number which is an integer to a number that is a string, text. This allows me to combine a letter and a number into one variable.

Dim i As Integer
Dim won As String
        i = 1
        won = LTrim(Str(i))

smiler44