Solved: Concatenate / Join two text variables in two cells

[demetre]

Morning Everyone

I have an issue where I cannot concatenate / join two string variables which reside in two different cells. Of course I can easily use ="str1"&"str2" and enter it in the worksheets, but I am analysing many, many worksheets which have already been completed.

In essence my code, uses the worksheet name as the pivot, and then uses MID function to extract parts of the worksheet name to call different function routines / macros. The first string looks for seven different types of 2 letter strings(occurs in every worksheet), whilst the second string only looks for a certain 2 letter string(only occurs in half of the worksheets).Thus when I use the Case Select function, I want to analyse both Strings together, then use Case 2 if it does not meet the criteria of the Select case. The main issue is that I cannot join these two variables from each of the two cells. All other aspects of the code works fine.

As always any help is very appreciated

thanks


[vba]
Private Sub SheetNameCell()
Dim WS_Count As Integer '/ Total number of worksheets
Dim i As Integer '/ Start point
Dim WrkSht As Worksheet '/ Workbook
Dim String1 As String
Dim String2 As String
Dim String3 As String
Dim String4 As String
Dim String5 As String

'/ On Error Resume Next
For Each WrkSht In ActiveWorkbook.Worksheets '/ Begin the loop

WrkSht.Range("A6000") = WrkSht.Name

'/ Breakup Sheetname
WrkSht.Range("A6001") = WrkSht.Name
WrkSht.Range("A6002") = WrkSht.Name
String1 = WrkSht.Range("A6001") '/Check to look for
String2 = WrkSht.Range("A6002") '/Check to look for 'sc' string
String3 = WrkSht.Range("A6004") '/ Concatenate for macro routine call
'/MsgBox ("Sheet cell name is: ") & String2
WrkSht.Range("A6003") = Mid(String1, 10, 2)
WrkSht.Range("A6004") = Mid(String2, 14, 2)
String3 = Mid(String1, 10, 2)
String4 = Mid(String2, 14, 2)

'/String5 = WrkSht.Range("A6003" & "A6004")
'/String5 = String3 & String4
'/String3 = Mid(String1, 10, 2) & Mid(String2, 14, 2) '/Concatenate Credit Type with Scenario
WrkSht.Range("A6005") = String5 '/Export concatenation to cell A6000

Select Case WrkSht.Range("A6004") '/ Check worksheet name to suit macro call routine
Case "CCsc", "FMsc", "MFsc", "ODsc", "PLsc", "RCsc", "SMsc"
WrkSht.Range("A6006") = WrkSht.Range("A6005")
End Select
Select Case WrkSht.Range("A6003") '/Call credit type macro function call
Case "PL", "FM", "CC", "SM", "RC", "MF", "OD"
WrkSht.Range("A6005") = WrkSht.Range("A6003")
Case Else
MsgBox "Incorrect naming convention used with worksheet: " & WrkSht.Name '/ Error message naming convention incorrect
On Error GoTo 0
End Select
'/End Select
'/End Select
'/End Select
'/End If
Next WrkSht '/ End For loop
End Sub
[/vba]

[rory]

Can you explain in English (not code) what you are trying to do? I can't make head nor tail out of that code or why you keep populating different cells with the same values rather than just using variables. I have a vague feeling you just need an If...Then construct within each branch of the Select Case but I am not sure. (i.e. you check string 1 using the select case then check string 2 with an If ...Then within each branch of the Select Case.)

[demetre]

Can you explain in English (not code) what you are trying to do? I can't make head nor tail out of that code or why you keep populating different cells with the same values rather than just using variables. I have a vague feeling you just need an If...Then construct within each branch of the Select Case but I am not sure. (i.e. you check string 1 using the select case then check string 2 with an If ...Then within each branch of the Select Case.)

Rory

thanks for the reply... i tidied up the code.. less garbage, and changed it slightly... I hope I am making more sense

this is how the call function should work (Using 2 different but correct worksheets: MN3CCGhacPL03 & MN3CCGhacPL03sc)

Store worksheet name in A6000 ==> eg MN3CCGhacPL03 or MN3CCGhacPL03sc (2 types of worksheets)
Store String1 in A6001 ==> "MN3CCGhacPL03"
Store String2 in A6002 ==> "MN3CCGhacPL03sc"
Use MID looking for the 10th charact in String1 ==> "PL" cell A6003
Use MID looking for the 14th charact in String2 ==> "sc" cell A6004

Concate / join cell contents A6003 and A6004 into cell A6005 / String5

Use Case Select to match Cell A6005 / String5 to our list (eg "PLsc")
If match, will then call PLsc function (which is alrdy written & works)
Else if Case does not match, try Cell A6003 to our list (eg "PL")
If match, will call the PL function (which is alrdy written & works)
Else
Incorrect worksheet name, MsgBox stating this error


Please note, I have changed the code slightly.
[vba]
Private Sub SheetNameCell()
Dim WS_Count As Integer '/ Total number of worksheets
Dim i As Integer '/ Start point
Dim WrkSht As Worksheet '/ Workbook
Dim MyStr As String
Dim String1 As String
Dim String2 As String
Dim String3 As String '/ store string
Dim String4 As String
Dim String5 As String

'/ On Error Resume Next
For Each WrkSht In ActiveWorkbook.Worksheets '/ Begin the loop

WrkSht.Range("A6000") = WrkSht.Name

'/ Breakup Sheetname
WrkSht.Range("A6001") = WrkSht.Name
WrkSht.Range("A6002") = WrkSht.Name
String1 = WrkSht.Range("A6001") '/Credit Type
String2 = WrkSht.Range("A6002") '/Check to ensure 'sc'

'/MsgBox ("Sheet cell name is: ") & String2
WrkSht.Range("A6003") = Mid(String1, 10, 2)
WrkSht.Range("A6004") = Mid(String2, 14, 2)
String3 = Mid(String1, 10, 2)
String4 = Mid(String2, 14, 2)

'/Need to concatenate String3 and String4 into one cell ("A6006")
WrkSht.Range("A6005") = String5 '/Export concatenation to cell A6005

Select Case WrkSht.Range("A6005") '/ Check worksheet name to suit macro call routine
Case "CCsc", "FMsc", "MFsc", "ODsc", "PLsc", "RCsc", "SMsc"
WrkSht.Range("A6006") = WrkSht.Range("A6005")
Case Else
Select Case WrkSht.Range("A6003") '/Call credit type macro function call
Case "PL", "FM", "CC", "SM", "RC", "MF", "OD"
WrkSht.Range("A6006") = WrkSht.Range("A6003")
Case Else
MsgBox "Incorrect naming convention used with worksheet: " & WrkSht.Name '/ Error message naming convention incorrect
On Error GoTo 0
End Select
End Select
Next WrkSht '/ End For loop
End Sub

[/vba]

[rory]

I'm still not 100%, but I think you just want:
[VBA]Application.Run WrkSht.Range("A6006").Value [/VBA]
at the end?

[demetre]

I'm still not 100%, but I think you just want:
[vba]Application.Run WrkSht.Range("A6006").Value [/vba]
at the end?

Rory

for some reason, most likely it is a logical error on my part, which caused this issue...

this is the solution

the reason why i was so dumb founded was the fact that I was declaring the strings as variables. Using '&' with String1 and String2 should always concatenate these strings into String5, but due to the logical error it would not. But now it is sorted

[vba]
Private Sub SheetNameCell()
Dim WS_Count As Integer '/ Total number of worksheets
Dim i As Integer '/ Start point
Dim WrkSht As Worksheet '/ Workbook
Dim String1 As String
Dim String2 As String
Dim String3 As String '/ store string
Dim String4 As String
Dim String5 As String

'/ On Error Resume Next
For Each WrkSht In ActiveWorkbook.Worksheets '/ Begin the loop

WrkSht.Range("A6000") = WrkSht.Name

'/ Breakup Sheetname
WrkSht.Range("A6001") = WrkSht.Name
WrkSht.Range("A6002") = WrkSht.Name
String1 = WrkSht.Range("A6001") '/Credit Type
String2 = WrkSht.Range("A6002") '/Check to ensure 'sc'

'/MsgBox ("Sheet cell name is: ") & String2
WrkSht.Range("A6003") = Mid(String1, 10, 2)
WrkSht.Range("A6004") = Mid(String2, 14, 2)
String3 = Mid(String1, 10, 2)
String4 = Mid(String2, 14, 2)
String5 = String3 & String4

'/Need to concatenate String3 and String4 into one cell ("A6006")
WrkSht.Range("A6005") = String5 '/Export concatenation to cell A6005

Select Case WrkSht.Range("A6005") '/ Check worksheet name to suit macro call routine
Case "CCsc", "FMsc", "MFsc", "ODsc", "PLsc", "RCsc", "SMsc"
WrkSht.Range("A6006") = WrkSht.Range("A6005")
Case Else
Select Case WrkSht.Range("A6003") '/Call credit type macro function call
Case "PL", "FM", "CC", "SM", "RC", "MF", "OD"
WrkSht.Range("A6006") = WrkSht.Range("A6003")
Case Else
MsgBox "Incorrect naming convention used with worksheet: " & WrkSht.Name '/ Error message naming convention incorrect
On Error GoTo 0
End Select
End Select
Next WrkSht '/ End For loop
End Sub

[/vba]