demetre
01-22-2008, 04:09 AM
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
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
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
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