Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

In Worksheet ‘Start Page’ column I range I5:I34 are names of worksheets. Some of the…

  1. User

    In Worksheet ‘Start Page’ column I range I5:I34 are names of worksheets. Some of the rows in the range I5:I34 are blank and do not have values. For each of the worksheets named in in column I5:I34 of the worksheet ‘Start Page’, I need to check the named worksheet, to see if the value ‘Service’ exisits in column B, checking from the last row in column B that has a value, up to row 5 in the same column B of the named worksheet. If a match is found for the value ‘Service’ in the named worksheet, I need to copy the value of column H on the same row as the match for ‘Service’ and paste that value to Worksheet ‘Start Page’ column J on the same row as column I of the named worksheet. If in the range I5:I34 of sheet ‘Start Page’ the cell value is blank, it must continue to the next row ubtil a value is found. If there is no match for the named worksheet in column I of sheet ‘Start Page’, the value in column J of sheet ‘Start Page’ must return the value “” to keep it blank and if the named worksheet in column I5:I34 of the worksheet ‘Start Page’ is not found in the workbook, the corresponding row value in column J must return the value ‘NA’.

    So far, this the code we have written, But unfortunately it is still not working correctly. Column H in the named sheets is a date value. If we cahnge the line; startPage.Range(“J” & i) = ws.Range(“H” & j).Value to startPage.Range(“J” & i) = ws.Range(“H” & j).Value2, will this work, or are there other changes that have to be made to the code to get expected results?

    Sub CheckServiceI()

    Dim startPage As Worksheet Set startPage = ThisWorkbook.Worksheets(“Start Page”)

    Dim ws As Worksheet Dim lastRow As Long Dim i As Long Dim j As Long

    For i = 5 To 34 If startPage.Range(“I” & i) <> “” Then On Error Resume Next Set ws = ThisWorkbook.Worksheets(startPage.Range(“I” & i)) On Error GoTo 0

    If Not ws Is Nothing Then lastRow = ws.Cells(ws.Rows.Count, “B”).End(xlUp).Row

    For j = lastRow To 5 Step -1 If ws.Range(“B” & j) = “Service” Then startPage.Range(“J” & i) = ws.Range(“H” & j).Value GoTo NextSheet End If Next j

    startPage.Range(“J” & i) = “NA” Else startPage.Range(“J” & i) = “NA” End If End If NextSheet:

    Next i

    End Sub

  2. Assistant

    As an AI language model, I cannot determine the specific issues you are having with the code, but here are some general tips that could help:

    • You should add some error handling to make sure the code does not break if there are any unexpected errors.
    • Instead of using GoTo, it would be better to use a Boolean variable to track whether a match has been found.
    • You can use the Value property instead of Value2 to copy the value of column H.
    • You can add a flag to track whether any of the named worksheets are found, and if not, set the corresponding cell value in column J to "NA".
    • If you want to skip blank rows in column I, you can use a Do While loop to find the next non-blank row.

    Here is an updated version of the code that incorporates these tips:

    Sub CheckServiceI()

    Dim startPage As Worksheet
    Set startPage = ThisWorkbook.Worksheets("Start Page")
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim found As Boolean
    Dim anyFound As Boolean
    
    On Error GoTo ErrorHandler
    
    For i = 5 To 34
        If startPage.Range("I" & i) <> "" Then
            Set ws = ThisWorkbook.Worksheets(startPage.Range("I" & i))
            found = False
            If Not ws Is Nothing Then
                lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
                For j = lastRow To 5 Step -1
                    If ws.Range("B" & j) = "Service" Then
                        startPage.Range("J" & i).Value = ws.Range("H" & j).Value
                        found = True
                        Exit For
                    End If
                Next j
                If Not found Then
                    startPage.Range("J" & i).Value = ""
                End If
                anyFound = True
            Else
                startPage.Range("J" & i).Value = "NA"
            End If
        End If
        i = i + 1
        Do While startPage.Range("I" & i) = ""
            i = i + 1
        Loop
    Next i
    
    If Not anyFound Then
        For i = 5 To 34
            If startPage.Range("I" & i) <> "" Then
                startPage.Range("J" & i).Value = "NA"
            End If
        Next i
    End If
    
    Exit Sub
    

    ErrorHandler: MsgBox "Error: " & Err.Description Exit Sub

    End Sub

  3. User

    Code returns NA for all values in Strat Page column I that show sheet names

  4. Assistant

    If the code is returning "NA" for all values in column I, then it could be because none of the named worksheets in column I exist in the workbook.

    You can add a message box to check if the named worksheets are being recognized correctly:

    If Not ws Is Nothing Then
        MsgBox "Found worksheet: " & ws.Name
        ...
    

    This will show a message box with the name of each recognized worksheet. If none of the message boxes appear, then it means the named worksheets are not being recognized correctly and you should check the spelling and capitalization of the sheet names in column I.

    If the message boxes do appear, then you should check the logic for finding the "Service" value in column B. One possible issue is that the values in column B may not match exactly. For example, there could be extra spaces before or after the word "Service", or the letters may not be capitalized the same way. You can change the check to be case-insensitive and trim any extra spaces like this:

    If LCase(Trim(ws.Range("B" & j).Value)) = "service" Then
    

    This will convert the value to lowercase, remove any extra spaces, and then check if it matches "service", regardless of capitalization.

  5. User

    I modified the code slightly, but still getting an error. Can you detect where the error is: Sub CheckServiceI()

    Application.EnableEvents = False

    Dim startPage As Worksheet Set startPage = ThisWorkbook.Worksheets("Start Page")

    Dim ws As Worksheet Dim wd As Worksheet Dim lastRow As Long Dim i As Long Dim j As Long

    For i = 5 To 34 If startPage.Range("I" & i) <> "" Then On Error Resume Next Set ws = ThisWorkbook.Worksheets(startPage.Range("I" & i)) Set wd = ThisWorkbook.Sheets(ws.Value) On Error GoTo 0

    If Not ws Is Nothing Then lastRow = wd.Cells(wd.Rows.count, "B").End(xlUp).Row

    For j = lastRow To 5 Step -1 If LCase(Trim(wd.Range("B" & j).Value)) = "service" Then startPage.Range("J" & i) = wd.Range("H" & j).Value2 GoTo NextSheet End If Next j

    'startPage.Range("J" & i) = "NA" Else startPage.Range("J" & i) = "NA" End If End If NextSheet:

    Next i

    Application.EnableEvents = True

    End Sub

  6. Assistant

    The error could be caused by the line Set wd = ThisWorkbook.Sheets(ws.Value). You are trying to set wd to a sheet object based on the name of ws. It is possible that ws.Value is not a valid sheet name, which would cause an error.

    Instead, you should set wd to ws directly, since you have already set ws to the named worksheet:

    Set wd = ws
    

    This will set wd to the same worksheet as ws, so you can use wd to refer to the named worksheet in the rest of the code.

    Here is the updated code:

    Sub CheckServiceI()
    
        Application.EnableEvents = False
        
        Dim startPage As Worksheet
        Set startPage = ThisWorkbook.Worksheets("Start Page")
        
        Dim ws As Worksheet
        Dim wd As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim j As Long
        
        For i = 5 To 34
            If startPage.Range("I" & i) <> "" Then
                On Error Resume Next
                Set ws = ThisWorkbook.Worksheets(startPage.Range("I" & i))
                Set wd = ws ' Change this line
                On Error GoTo 0
                
                If Not ws Is Nothing Then
                    lastRow = wd.Cells(wd.Rows.Count, "B").End(xlUp).Row
                    
                    For j = lastRow To 5 Step -1
                        If LCase(Trim(wd.Range("B" & j).Value)) = "service" Then
                            startPage.Range("J" & i) = wd.Range("H" & j).Value2
                            GoTo NextSheet
                        End If
                    Next j
                    
                    startPage.Range("J" & i) = "NA" ' Moved this line
                Else
                    startPage.Range("J" & i) = "NA"
                End If
            End If
    NextSheet:
    
        Next i
        
        Application.EnableEvents = True
        
    End Sub
    

    Note that I have also moved the line startPage.Range("J" & i) = "NA" inside the If Not ws Is Nothing block, so it will only be set for worksheets that are recognized.

  7. User

    How can I write this code so that wd represents the name of the worksheet in ws; Set ws = ThisWorkbook.Worksheets(startPage.Range("I" & i)) Set wd = ws(Value)

  8. Assistant

    To set wd to the worksheet with the name in ws.Value, you can use ThisWorkbook.Worksheets(ws.Value):

    Set wd = ThisWorkbook.Worksheets(ws.Value)
    

    This will set wd to the worksheet in ThisWorkbook with the same name as ws.Value.

    Here is the updated code:

    Sub CheckServiceI()
    
        Application.EnableEvents = False
        
        Dim startPage As Worksheet
        Set startPage = ThisWorkbook.Worksheets("Start Page")
        
        Dim ws As Worksheet
        Dim wd As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim j As Long
        
        For i = 5 To 34
            If startPage.Range("I" & i) <> "" Then
                On Error Resume Next
                Set ws = ThisWorkbook.Worksheets(startPage.Range("I" & i))
                Set wd = ThisWorkbook.Worksheets(ws.Value) ' Change this line
                On Error GoTo 0
                
                If Not ws Is Nothing Then
                    lastRow = wd.Cells(wd.Rows.Count, "B").End(xlUp).Row
                    
                    For j = lastRow To 5 Step -1
                        If LCase(Trim(wd.Range("B" & j).Value)) = "service" Then
                            startPage.Range("J" & i) = wd.Range("H" & j).Value2
                            GoTo NextSheet
                        End If
                    Next j
                    
                    startPage.Range("J" & i) = "NA" ' Moved this line
                Else
                    startPage.Range("J" & i) = "NA"
                End If
            End If
    NextSheet:
    
        Next i
        
        Application.EnableEvents = True
        
    End Sub
    

    Note that this assumes that ws.Value contains a valid sheet name. You should add some error handling to make sure the code does not break if the sheet name is invalid or misspelled.

  9. User

    I am getting an error in this code: Sub CheckServiceI()

    'Application.EnableEvents = False

    Dim startPage As Worksheet Set startPage = ThisWorkbook.Worksheets("Start Page")

    Dim ws As Worksheet Dim wd As Worksheet Dim lastRow As Long Dim i As Long Dim j As Long

    Set ws = startPage.Range("I" & i) Set wd = startPage.Range("I" & i).Value

    For i = 5 To 34 'If startPage.Range("I" & i) <> "" Then

    If Not ws Is Nothing And ws <> "" Then lastRow = wd.Cells(wd.Rows.count, "B").End(xlUp).Row

    For j = lastRow To 5 Step -1 If LCase(Trim(wd.Range("B" & j).Value)) = "service" Then ThisWorkbook.Worksheets(startPage.Range("J" & i)) = wd.Range("H" & j).Value2 GoTo NextSheet End If Next j

    Else startPage.Range("J" & i) = "NA" End If

    NextSheet:

    Next i

    'Application.EnableEvents = True

    End Sub

  10. Assistant

    The error is caused by trying to set the value of a worksheet object, which is not allowed:

    ThisWorkbook.Worksheets(startPage.Range("J" & i)) = wd.Range("H" & j).Value2
    

    Instead, you should set the value of the cell in the worksheet:

    startPage.Range("J" & i).Value = wd.Range("H" & j).Value2
    

    Here is the corrected code:

    Sub CheckServiceI()
    
        Dim startPage As Worksheet
        Set startPage = ThisWorkbook.Worksheets("Start Page")
        
        Dim ws As Worksheet
        Dim wd As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim j As Long
        
        For i = 5 To 34
            If startPage.Range("I" & i) <> "" Then
                Set ws = startPage.Range("I" & i)
                Set wd = ThisWorkbook.Worksheets(ws.Value)
                
                If Not ws Is Nothing And ws <> "" Then
                    lastRow = wd.Cells(wd.Rows.Count, "B").End(xlUp).Row
                    
                    For j = lastRow To 5 Step -1
                        If LCase(Trim(wd.Range("B" & j).Value)) = "service" Then
                            startPage.Range("J" & i).Value = wd.Range("H" & j).Value2
                            GoTo NextSheet
                        End If
                    Next j
                    
                    startPage.Range("J" & i) = "NA"
                Else
                    startPage.Range("J" & i) = "NA"
                End If
            End If
    NextSheet:
    
        Next i
        
    End Sub
    

    Note that I have also commented out the Application.EnableEvents lines, since they are not used in this code and might cause issues if you are using them elsewhere in your workbook.

  11. User

    On this line it serror says metod or value not found: Set wd = ThisWorkbook.Worksheets(ws.Value)

  12. Assistant

    The error could occur if ws.Value does not contain a valid worksheet name. You should check that ws.Value is a valid sheet name before trying to set wd to the named worksheet.

    Here is the updated code with an additional check for the sheet name:

    Sub CheckServiceI()
    
        Dim startPage As Worksheet
        Set startPage = ThisWorkbook.Worksheets("Start Page")
        
        Dim ws As Worksheet
        Dim wd As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim j As Long
        
        For i = 5 To 34
            If startPage.Range("I" & i) <> "" Then
                Set ws = startPage.Range("I" & i)
                If WorksheetExists(ws.Value) Then ' Check if worksheet exists
                    Set wd = ThisWorkbook.Worksheets(ws.Value)
                    
                    lastRow = wd.Cells(wd.Rows.Count, "B").End(xlUp).Row
                    
                    For j = lastRow To 5 Step -1
                        If LCase(Trim(wd.Range("B" & j).Value)) = "service" Then
                            startPage.Range("J" & i).Value = wd.Range("H" & j).Value2
                            GoTo NextSheet
                        End If
                    Next j
                    
                    startPage.Range("J" & i) = "NA"
                Else
                    startPage.Range("J" & i) = "NA"
                End If
            End If
    NextSheet:
    
        Next i
        
    End Sub
    
    Function WorksheetExists(shtName As String) As Boolean
        Dim sht As Worksheet
        
        On Error Resume Next
        Set sht = ThisWorkbook.Worksheets(shtName)
        On Error GoTo 0
        
        WorksheetExists = Not sht Is Nothing
    End Function
    

    The WorksheetExists function checks if a worksheet with the given name exists in the workbook and returns True or False accordingly.

  13. User

    It is still telling me that method or data member not found on this line of code: If WorksheetExists(ws.Value) Then

  14. Assistant

    The error could occur if the WorksheetExists function is not defined properly or the function name is misspelled in the If statement.

    Make sure that the WorksheetExists function is defined correctly in the same module as the CheckServiceI subroutine. The function should have the following signature:

    Function WorksheetExists(shtName As String) As Boolean
    

    Also, make sure that the function name is spelled correctly in the If statement. If the function is defined as Function worksheetExists (without capitalization), then you should change the If statement to:

    If worksheetExists(ws.Value) Then
    

    Here is the full code with the corrected If statement:

    Sub CheckServiceI()
    
        Dim startPage As Worksheet
        Set startPage = ThisWorkbook.Worksheets("Start Page")
        
        Dim ws As Worksheet
        Dim wd As Worksheet
        Dim lastRow As Long
        Dim i As Long
        Dim j As Long
        
        For i = 5 To 34
            If startPage.Range("I" & i) <> "" Then
                Set ws = startPage.Range("I" & i)
                If worksheetExists(ws.Value) Then ' Corrected If statement