problem with using Instr function in VBA and Excl

Question

I have a problem with VBA created sheets (ie: No problems by copying from a MASTER template and renaming the sheet). New sheets created have a common format, such as : '6-n VOL-WTS-AREA'. To automate the creation of a new sheet, I scan the sheets and look for 'VOL-WTS-AREA' in the sheet name, if found I extract the n and use it create the new sheet name by adding ONE to n. I use the Instr function to locate the 'VOL-WTS-AREA' in the sheet names. However, VBA has a problem insisting the string cannot be found in the newly created VBA sheets. If I manually (ie: Interactively) create a sheet, VBA has no problem finding the 'VOL-WTS-AREA' and returning the current value of n. I have exited out of Excel and restarted it. The problem persists, in that VBA insists the sheets DO NOT have the 'VOL-WTS- AREA embedded in the sheet names. Anyone run into this problem. I have tried using suggestion by ChatGPT, it finally suggested calling Microsoft

Answer 1

I can attach a Worksheet which shows the problem, which you can run and debug. Here is the code:

Sub check_sheets()

For Each sht In ThisWorkbook.Sheets
MsgBox "Current SHEET NAME = " & sht.Name
    If InStr(sht.Name, "VOL-WTS-AREA") > 0 Then
        n = n + 1
    
MsgBox "SHEET NAME has 'VOL-WTS-AREA' in it = " & sht.Name
    
    End If
Next sht
End Sub

Sub check_sheets_2()

For i = 1 To 10 'assuming you have 10 sheets created by the VBA code
sheetName = "6-" & i & " VOL-WTS-AREA"
Debug.Print sheetName 'output the sheet name to the Immediate Window
If InStr(sheetName, "VOL-WTS-AREA") > 0 Then
n = n + 1
MsgBox "SHEET NAME has 'VOL-WTS-AREA' in it = " & sheetName
End If
Next i

End Sub

Public Sub cmdCreate_New_VOL_WTS_AREA_Sheet_Click()

'==================================================================================================================================================
' STEP 1: Scan and locate all the VOL_WTS_AREA sheets and find the largest value of 'n' in the string '6-n'
'==================================================================================================================================================
Dim wb As Workbook
Dim ws As Worksheet
Dim intNumSheetsInWorkbook As Integer
Dim intM As Integer
Dim intLargest_n_Value_Found As Integer
Dim intFIRST_Digit_Of_n As Integer
Dim intLAST_Digit_Of_n As Integer
Dim intVALUE_Of_n As Integer
Dim iPos As Integer
Dim strLast_VOL_WTS_AREA As String
Dim strSheet_Name As String
Dim booFound As Boolean
Dim strSheetId(1 To 50) As String

'Call check_sheets_2

'Call check_sheets

' turn OFF screen updating and other app events that night interfere with the execution code

Application.ScreenUpdating = False
Application.EnableEvents = False

    intNumSheetsInWorkbook = Application.Sheets.Count ' get number of worksheets in workbook
    
    intNum_VOL_WTS_AREA_Sheets = 1 ' tracks how many VOL-WTS-AREA sheetd found
    intLargest_n_Value_Found = 0 ' set to the LARGEST value of n found after scanning all worksheets, if NONE FOUND, then set it to 1
    
    booFound = False ' used to indicae if ANY '6-n VOL-WTS-AREA' sheets found
Application.Calculate
    For intM = 1 To intNumSheetsInWorkbook
        
        MsgBox "Current SheetList(" & intM & ") = '" & Sheets(intM).Name & "'"

strSheet_Name = ThisWorkbook.Sheets(intM).Name
        
'        strSheet_Name = Sheets(intM).Name
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
' the program fails to detect a VBA created sheet on the next statement. WHY WHY WHY???
        If InStr(1, strSheet_Name, "VOL-WTS-AREA", vbBinaryCompare) > 0 Then ' if the sheet name contains the string 'VOL-WTS-AREA' then save in in the array
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
        MsgBox "WITH VOL-WTS-AREA: SheetList(" & intM & ") = '" & strSheet_Name & "'"
            
            strSheetId(intNum_VOL_WTS_AREA_Sheets) = strSheet_Name

'            MsgBox "strSheetId_array(" & intNum_VOL_WTS_AREA_Sheets & ") = '" & Sheets(intM).Name & "'"
            
            If InStr(1, strSheet_Name, "6-", vbTextCompare) = 1 Then ' check if the WOL_WTS_AREA sheet found has a '6-' at the beginning of it
            
                booFound = True ' indicates a '6-n VOL-WTS-AREA' sheet has been found
                
' if it has a '6-n' starting at position 1, then locate the SPACE char AFTER the '6-' which comes BEFORE the VOL_WTS_AREA portion
' it may be possible that the n value in the '6-n' portion may be MORE than ONE digit, we have to allow for this. We need to get
' the START and END location of the value of n

                intFIRST_Digit_Of_n = 3 ' since we know that '6-' are in positions 1 and 2
                iPos = InStr(strSheet_Name, " ")    ' the last digit of n is BEFORE the SPACE char (ie: before the VOL_WTS_AREA string)
                intLAST_Digit_Of_n = iPos - intFIRST_Digit_Of_n    ' this gives position of the LAST digit of n
                intVALUE_Of_n = CInt(Mid(strSheet_Name, intFIRST_Digit_Of_n, intLAST_Digit_Of_n))
                
                If intLargest_n_Value_Found < intVALUE_Of_n Then
                    intLargest_n_Value_Found = intVALUE_Of_n    ' keep track of the largest value of n
                    strLast_VOL_WTS_AREA = strSheet_Name ' the NEW VOL-WTS-AREA sheet will be stored AFTER this sheet
                End If
            End If ' If InStr(strSheet_Name, "6-")
            
            intNum_VOL_WTS_AREA_Sheets = intNum_VOL_WTS_AREA_Sheets + 1 ' keep count of number of  '6-n VOL-WTS-AREA' found
        End If ' If InStr(strSheet_Name, "VOL-WTS-AREA")
    Next intM
    
    intNum_VOL_WTS_AREA_Sheets = intNum_VOL_WTS_AREA_Sheets - 1 ' since at end of loop, sunscript is ONE more than Last sheet
    
'    MsgBox "Number of VOL-WTS-AREA sheets found = " & intNum_VOL_WTS_AREA_Sheets
'    For intM = 1 To intNum_VOL_WTS_AREA_Sheets
'        MsgBox "INSIDE sub VOL-WTS-AREA List(" & intM & ") = '" & strSheetId(intM) & "'"
'    Next intM

MsgBox "LARGEST value of N found = " & intLargest_n_Value_Found

' if no VOL_WTS_AREA sheets found then intNum_VOL_WTS_AREA_Sheets = 0

    If intNum_VOL_WTS_AREA_Sheets = 0 Then
        strLast_VOL_WTS_AREA = strSheet_Name
    End If

' copy the '6 MASTER' AFTER the '6-n VOL-WTS-AREA' where n is the largest of the VOL-WTS-AREA sheets
' then rename the sheet which is named: "6 MASTER (2)" to the new VOL_WTS_AREA sheet name

    Worksheets("6 MASTER").Copy After:=Worksheets(strLast_VOL_WTS_AREA)
    
' create new name for the VOL_WTS_SHEET, NOTE: we are use the UNDERSCORE in our programming but USE the MINUS sign for SHEET NAMES

    strNEW_VOL_WTS_AREA_SHEET_NAME = "6-" + CStr(intLargest_n_Value_Found + 1) + " VOLS-WTS-AREA"
    
    'On Error Resume Next
    ActiveWorkbook.Worksheets("6 MASTER (2)").Name = strNEW_VOL_WTS_AREA_SHEET_NAME
    Application.Calculate ' allow EXCEL to recalculate internal workbook structure, so the NEW sheet can be processed in subsequent code
    
' the NEWLY CREATED VOL_WTS_AREA sheet MUST BE ACTIVATED before it is recognisable on subsequent passes. Otherwise VBA
' CANNOT process the sheet (it CAUSES a problem , in that the string 'VOL-WTS-AREA' CANNOT be found in the sheet name and CAUSES
' the FOR intM loop to BOMB OUT at the if statement: 'If InStr(strSheet_Name, "VOL-WTS-AREA") Then'!!

    Worksheets(strNEW_VOL_WTS_AREA_SHEET_NAME).Activate
    
' place the new VOL_WTS_AREA sheet name into the LABEL: lblNew_VOL_WTS_AREA in the form: frmNEW_VOL_WTS_AREA_CREATED
' center justify the form and display the form

    frmNEW_VOL_WTS_AREA_CREATED.lblNew_VOL_WTS_AREA_display.Caption = strNEW_VOL_WTS_AREA_SHEET_NAME
    
    frmNEW_VOL_WTS_AREA_CREATED.Left = Application.Left + (Application.Width - frmNEW_VOL_WTS_AREA_CREATED.Width) / 2
    frmNEW_VOL_WTS_AREA_CREATED.Top = Application.Top + (Application.Height - frmNEW_VOL_WTS_AREA_CREATED.Height) / 2
    
    frmNEW_VOL_WTS_AREA_CREATED.Show
    
' turn ON screen updating and other app events

Application.ScreenUpdating = ture
Application.EnableEvents = True


End Sub

Answer 2

Hi Viorel Unfortunately, I CANNOT attach the Worksheet, it will not accept Excel workbooks this FORUM will not allow them. I will need to email it to you to debug. I have tried everything using ChatGPT and its has GIVEN UP and suggested I contact Microsoft, so far they have not been of any help. I do not know if I am missing a patch that might fix the problem

Answer 3

Try to use VOL-WTS-AREA instead of VOLS-WTS-AREA.