In Worksheet ‘Start Page’ column I range I5:I34 are names of worksheets. Some of the…
- 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
- 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 SubErrorHandler: MsgBox "Error: " & Err.Description Exit Sub
End Sub
- User
Code returns NA for all values in Strat Page column I that show sheet names
- 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" ThenThis will convert the value to lowercase, remove any extra spaces, and then check if it matches "service", regardless of capitalization.
- 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
- Assistant
The error could be caused by the line
Set wd = ThisWorkbook.Sheets(ws.Value). You are trying to setwdto a sheet object based on the name ofws. It is possible thatws.Valueis not a valid sheet name, which would cause an error.Instead, you should set
wdtowsdirectly, since you have already setwsto the named worksheet:Set wd = wsThis will set
wdto the same worksheet asws, so you can usewdto 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 SubNote that I have also moved the line
startPage.Range("J" & i) = "NA"inside theIf Not ws Is Nothingblock, so it will only be set for worksheets that are recognized. - 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)
- Assistant
To set
wdto the worksheet with the name inws.Value, you can useThisWorkbook.Worksheets(ws.Value):Set wd = ThisWorkbook.Worksheets(ws.Value)This will set
wdto the worksheet inThisWorkbookwith the same name asws.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 SubNote that this assumes that
ws.Valuecontains 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. - 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
- 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).Value2Instead, you should set the value of the cell in the worksheet:
startPage.Range("J" & i).Value = wd.Range("H" & j).Value2Here 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 SubNote that I have also commented out the
Application.EnableEventslines, since they are not used in this code and might cause issues if you are using them elsewhere in your workbook. - User
On this line it serror says metod or value not found: Set wd = ThisWorkbook.Worksheets(ws.Value)
- Assistant
The error could occur if
ws.Valuedoes not contain a valid worksheet name. You should check thatws.Valueis a valid sheet name before trying to setwdto 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 FunctionThe
WorksheetExistsfunction checks if a worksheet with the given name exists in the workbook and returnsTrueorFalseaccordingly. - User
It is still telling me that method or data member not found on this line of code: If WorksheetExists(ws.Value) Then
- Assistant
The error could occur if the
WorksheetExistsfunction is not defined properly or the function name is misspelled in theIfstatement.Make sure that the
WorksheetExistsfunction is defined correctly in the same module as theCheckServiceIsubroutine. The function should have the following signature:Function WorksheetExists(shtName As String) As BooleanAlso, make sure that the function name is spelled correctly in the
Ifstatement. If the function is defined asFunction worksheetExists(without capitalization), then you should change theIfstatement to:If worksheetExists(ws.Value) ThenHere is the full code with the corrected
Ifstatement: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