VBA Syntax: Writing as Text, not Number

[OldDogNewTricks]

How do I have VBA understand that my content is text, not numbers? I keep getting an error when I try to run my code because my words have dashes in them.

Here is the part of my code that is giving me issues:
Word = "I-BUTANE"

 

[VoG]

What is the error? This works for me

Sub test()
Dim word As String
word = "I-BUTANE"
MsgBox word
End Sub

 

[Rick Rothstein]

You are going to have to tells us more about where the word is coming from and exactly what you are attempting to do with it. Also, if you show us your non-working code, that maybe also give us a better understanding of the description I just asked you to provide.

 

[OldDogNewTricks]

Here is my code:

Dim X As Long, Index As Long, Word As String
Word = "I-BUTANE"
If Len(Word) Then
For X = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If UCase(Trim(Cells(X, "A").Value)) = UCase(Word) Then
Index = Index + 1
Rows(X).Name = Word & Index
End If
Next
End If




I have a column filled with various chemicals. The order changes, but each chemical will repeat itself ten times. Whenever a row contains "I-BUTANE" in the first column, the entire row will be labeled "I-BUTANE#," where # changes with each appearance (first time you see I-BUTANE, the row is labeled I-BUTANE1).

I keep getting an error when I run my code for all the chemicals that contain a "-" in it. I am assuming Excel does not consider this to be a string.

Does this help?

 

[Jonmo1]

I believe a hyphen is an invalid character for Range Names.

I would suggest replacing the - with _

Rows(X).Name = Replace(Word, "-", "_") & Index

 

[OldDogNewTricks]

Although it is not what I was expecting, it works. Thank you so much! I appreciate your help.

 

[Jonmo1]

You're welcome.