Simple question about strings?

[Shaolin]

If I want to write a string

"Menu: TBA
Wine: TBA
Price: TBA"

so that there is an enter after the first two TBAs

Currently I write "Menu: TBA Wine:TBA and Price: TBA, but I use spaces after the first two TBAs.

When I attempt to place the enter after the first two TBAs as such

"Menu: TBA
Wine: TBA
Price: TBA"

VBA automatically places open and close quotation marks to make it

"Menu: TBA"
"Wine: TBA"
"Price: TBA"

So basically, I want to know what should I place after the first two TBAs so that VBA understands I want a enter after the TBAs?

[Norie]

What code are you currently using?

Where are you putting the string?

[Killian]

You can use either vbconstants or ASCII codes when building strings


Sub StringTest()
Dim strText As String
strText = "Menu: TBA" & vbLf & "Wine: TBA" & vbLf & "Price: TBA"
MsgBox strText
End Sub

or


Sub StringTest()
Dim strText As String
strText = "Menu: TBA" & Chr(13) & "Wine: TBA" & Chr(13) & "Price: TBA"
MsgBox strText
End Sub

[Shaolin]

DRJ helped me out greatly with the code



Sub UpdateText()
Dim Cel As Range
Dim Str As String
Dim FirstAddress As String
Dim Rng As Range
Str = "Menu: TBD Wine: TBD Price: TBD"
With Range("E:E")
Set Cel = .Find(What:=Str, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
If Rng Is Nothing Then
Set Rng = Cel
Else
Set Rng = Union(Rng, Cel)
End If
Set Cel = .FindNext(Cel)
Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
Rng.Value = "RESTAURANT AVAILABLE SOON! PLEASE SUBMIT " & _
"PDP EVENT INFORMATION FORM TO EXPIDITE NEGOTIATIONS PROCESS."
Rng.Font.ColorIndex = 3
End If
End With
End Sub

[Shaolin]

You can use either vbconstants or ASCII codes when building strings


Sub StringTest()
Dim strText As String
strText = "Menu: TBA" & vbLf & "Wine: TBA" & vbLf & "Price: TBA"
MsgBox strText
End Sub

or


Sub StringTest()
Dim strText As String
strText = "Menu: TBA" & Chr(13) & "Wine: TBA" & Chr(13) & "Price: TBA"
MsgBox strText
End Sub



Thanks Killian, it still doesn't work though. I tried both the ASCII and vbcontants method and nothing happens. I attached a test spreadsheet. I used Alt + enter (I am assuming that is the same thing as enter), after the first two TBAs.

[Andy Pope]

What if you change your code to search in column G?


With Range("g:g")

[Shaolin]

lol! whoops!

It works on the test spreadsheet, but it still doesn't work on the the real spreadsheet for some weird reason

[Shaolin]

it works . . .

now I want to do one more thing to the code

I want it to change

Menu: TBD
Wine: TBD
Price: TBD

and

Courses: TBD
Wine: TBD
Price: TBD

to

"RESTAURANT AVAILABLE SOON! PLEASE SUBMIT PDP EVENT INFORMATION FORM TO EXPIDITE NEGOTIATIONS PROCESS." in red font