I have looked through some examples on here but nothing seems to quite fit what I need.
I need to take the variable DeptVar = "E22E27E33" (this is the result from the userform)
and turn it into this [Department] = "E22" and so on.
The problem I am having is getting the quotation marks around the Department (E22)
Here is my code. I have tried every variation that i can think of to get it to add the quotations.
Sub CreateString ()
Dim Index as Integer
Dim DeptVar as String
Dim DeptString as String
DeptVar = "E22E27E33" ' <---This is how it is gathered from the userform
Index = 1
Do While Index < 8
If DeptString = "" Then
DeptString = "[Department] = " & "" Mid(DeptVar, index, 3) & ""
Else: DeptString = DeptString & "," " AND [Department = " & Mid, index, 3)
end if
index = index +3
loop
end Sub
It currently returns this:
[Department] = E22, AND [Department] = E27, AND [Department] = E33
I need it to return this:
[Department] = "E22", AND [Department] = "E27", AND [Department] = "E33"
2
Sub CreateString()
Const TMPL = "[Department] = ""###"""
Dim DeptVar As String, DeptString As String, i As Long, s As String
DeptVar = "E22E27E33" ' <---This is how it is gathered from the userform
For i = 1 To Len(DeptVar) Step 3
DeptString = DeptString & s & Replace(TMPL, "###", Mid(DeptVar, i, 3))
s = ", AND "
Next
Debug.Print DeptString
End Sub
Build a String
Sub CreateString()
Const CODE_LEN As Long = 3
Dim DeptCodes As String: DeptCodes = "E22E27E33" ' <--- from the userform
Dim i As Long, DeptString As String, CodeString As String
Dim IsFirstFound As Boolean
For i = 1 To Len(DeptCodes) Step CODE_LEN
CodeString = "[Department] = """ & Mid(DeptCodes, i, CODE_LEN) & """"
If IsFirstFound Then
DeptString = DeptString & ", AND " & CodeString
Else
DeptString = CodeString
IsFirstFound = True
End If
Next i
Debug.Print DeptString
End Sub
1
"" Returns an empty string. Doubling double quotes are converted to single sets of double quotes. Examples: """" is converted to ", """E2""" returns "E2". Use Chr(34) to return " as an alternative.
Sub CreateString()
Dim Index As Integer
Dim DeptVar As String
Dim DeptString As String
DeptVar = "E22E27E33" ' <---This is how it is gathered from the userform
Index = 1
Do While Index < 8
If DeptString = "" Then
DeptString = "[Department] = " & Chr(34) & Mid(DeptVar, Index, 3) & Chr(34)
Else
DeptString = DeptString & "," & Chr(34) & " AND [Department] = " & Chr(34) & Mid(DeptVar, Index, 3)
End If
Index = Index + 3
Loop
End Sub
I would avoid the loop if we are always getting back a 9 character string:
Sub CreateString2()
Dim DeptVar As String
Dim DeptString As String
DeptVar = "E22E27E33" ' <---This is how it is gathered from the userform
DeptString = "[Department] = " & Chr(34) & Left(DeptVar, 3) & Chr(34) & _
" AND [Department] = " & Chr(34) & Mid(DeptVar, 4, 3) & Chr(34) & _
" AND [Department] = " & Chr(34) & Mid(DeptVar, 7, 3) & Chr(34)
End Sub
The Immediate Window is a great way to test code like this.
3
Assuming identical department abbreviations (e.g. “E”), but even varying lengths of dept numbers you might try the following function combining Split & Join:
Function dept(ByVal DeptVar As String, Optional ByVal letter As String = "E") As String
Const prefix = """, AND "
Const pattern = "[Department] = """
Dim a: a = Split(DeptVar, letter)
dept = Mid(Join(a, prefix & pattern & letter), Len(prefix) + 1) & Chr(34)
End Function
Example call
Dim DeptVar As String
DeptVar = "E22E27E33"
Debug.Print dept(DeptVar)
Result in VB Editor’s immediate window
[Department] = "E22", AND [Department] = "E27", AND [Department] = "E33"