Concatenation yields error with underscore
I am trying to create a macro that brings in t开发者_JAVA百科he name of the sheet and combine it with text. For example, for sheet one, I want it to say "ThisIs_Sheet1_Test" in I5 of Sheet1. There are several sheets but it should work for all of them.
What is wrong with my code? I think the underscore might be ruining it all. Here's what I have:
Dim SheetName As String
Public Sub CommandButton1_Click()
SheetName = ActiveSheet.Name
Sheets("Sheet1").Range("I5", "I5") = ThisIs_" & SheetName.text & "_Test
Sheets("Sheet2").Range("H5", "H5") = ThisIs_" & SheetName.text & "_Test
Sheets("Sheet3").Range("G5", "G5") = ThisIs_" & SheetName.text & "_Test
End Sub
This question has been forwarded to Pull in Earlier Value Using Concatenation
looks like a quoting problem. ThisIs_
and _Test
are strings, right? So the quotes should be around them, not around & SheetName.text &
Sheets("Sheet1").Range("I5", "I5") = "ThisIs_" & SheetName.text & "_Test"
In addition to the missing quotes, SheetName is a string, not an object, so it won't have a Text property. Did you want the name of the sheet to change as the sheet changes? You need this:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
For Each ws In Me.Parent.Worksheets
ws.Range("I5").Value = "ThisIs_" & ws.Name & "_Test"
Next ws
End Sub
I am able to get it to work...sort of.
Now, it displays ThisIS_Sheet1_Test in Sheet1, etc.
However, I have it set up to pull data from a listbox into a function which I called ThisIS_Sheet1_Test. I figured that once I got it to display the name, it would pull the function in. Here's what I have above:
Public Sub ListBox2_LostFocus()
ListBox2.Height = 15
With ListBox2
ThisIS_Sheet1_Test = "'"
For i = 0 To .ListCount - 1
If .Selected(i) Then
ThisIS_Sheet1_Test = ThisIS_Sheet1_Test & .List(i) & "','"
End If
Next i
End With
ThisIS_Sheet1_Test = Left(ThisIS_Sheet1_Test, Len(ThisIS_Sheet1_Test) - 2)
End Sub
How come when I get the text I want it doesn't translate into the function I thought it would.
can you take few minutes to compile your code (in VBA Window, Debug->Compile VBA Project) before looking for other's help?? That would have screamed about your missing quotes, using .Text on a string variable etc.
Public Sub dummy()
Dim SheetName As String
SheetName = ActiveSheet.Name
Sheets("Sheet1").Range("I5", "I5") = "ThisIs_" & SheetName & "_Test"
Sheets("Sheet2").Range("H5", "H5") = "ThisIs_" & SheetName & "_Test"
Sheets("Sheet3").Range("G5", "G5") = "ThisIs_" & SheetName & "_Test"
End Sub
Underscore carries a special meaning in VBA/VB world. Its code concatination (meaning if your code is too long and you want to split it across two lines then you put a space underscore ( _) and continue with next line. And also Dick Kusleika is right about object/string. Only for objects you will have differnt peopreties (.Text means you are asking for Text property of that object), and usually that Text property would be of String type. here you already have a String, and you just use it as it is.
精彩评论