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

The following VBA code bellow, works perfectly. However, I would like to ADD other…

  1. User

    The following VBA code bellow, works perfectly. However, I would like to ADD other functionality to it. If the original loop completes and in all cells it finds that If wsQuotes.Cells(cell.row, 5).Value = wsRequest.Cells(Target.row, "E").Value IS FALSE And wsQuotes.Cells(cell.row, 6).Value = wsRequest.Cells(Target.row, "F").Value IS ALSO FALSE Then find the next blank cell in column A of sheet 'QUOTES' and using that row, do the following ThisWorkbook.Sheets("QUOTES").Unprotect Password:="edit" wsQuotes.Cells(cell.row, 1).Value = wsRequest.Cells(Target.row, "A").Value ' CONTRACTOR wsQuotes.Cells(cell.row, 2).Value = wsRequest.Cells(Target.row, "B").Value ' JOB wsQuotes.Cells(cell.row, 3).Value = wsRequest.Cells(Target.row, "C").Value ' SECTOR wsQuotes.Cells(cell.row, 4).Value = wsRequest.Cells(Target.row, "D").Value ' SUBSECTOR wsQuotes.Cells(cell.row, 5).Value = wsRequest.Cells(Target.row, "E").Value ' QUO/INV# wsQuotes.Cells(cell.row, 6).Value = wsRequest.Cells(Target.row, "F").Value ' VALUE wsQuotes.Cells(cell.row, 7).Value = wsRequest.Cells(Target.row, "G").Value ' LOCATION wsQuotes.Cells(cell.row, 8).Value = wsRequest.Cells(Target.row, "H").Value ' NOTES wsQuotes.Cells(cell.row, 9).Value = wsRequest.Cells(Target.row, "I").Value ' P REQUEST# wsQuotes.Cells(cell.row, 10).Value = wsRequest.Cells(Target.row, "J").Value ' DATE wsQuotes.Cells(cell.row, 13).Value = "Finance" ThisWorkbook.Sheets("QUOTES").Protect Password:="edit"

    Here is the original VBA event:

    Sub SearchSubmittedQuotes(Target As Range)

    Dim wsRequest As Worksheet
    Dim wsQuotes As Worksheet
    Dim cell As Range
    Dim searchValue As Variant
    
    ''''If ActiveCell.Column <> 7 Or ActiveSheet.Name <> "PREQUEST" Then
    ''''Application.EnableEvents = True
        ''''Exit Sub
    ''''End If
    

    Application.EnableEvents = False Set wsRequest = ThisWorkbook.Sheets("PREQUEST") Set wsQuotes = ThisWorkbook.Sheets("QUOTES")

    wsQuotes.Unprotect Password:="edit"
        searchValue = Target.Offset(0, -7).Value
        For Each cell In wsQuotes.Range("A5:A" & wsQuotes.Cells(wsQuotes.Rows.count, "A").End(xlUp).row)
            If wsQuotes.Cells(cell.row, 1).Value = searchValue Then
                If wsQuotes.Cells(cell.row, 5).Value = wsRequest.Cells(Target.row, "E").Value _
                    And wsQuotes.Cells(cell.row, 6).Value = wsRequest.Cells(Target.row, "F").Value Then
                    ThisWorkbook.Sheets("QUOTES").Unprotect Password:="edit"
                        wsQuotes.Cells(cell.row, 8).Value = wsRequest.Cells(Target.row, "H").Value ' NOTES
                        wsQuotes.Cells(cell.row, 9).Value = wsRequest.Cells(Target.row, "I").Value ' P REQUEST#
                        wsQuotes.Cells(cell.row, 10).Value = wsRequest.Cells(Target.row, "J").Value ' DATE
                        wsQuotes.Cells(cell.row, 13).Value = "Finance"
                    ThisWorkbook.Sheets("QUOTES").Protect Password:="edit"
                End If
            End If
        Next cell
                Target.AddComment "Quote sent to Finance " & Date
            wsQuotes.Protect Password:="edit"
    Application.Wait Now + TimeValue("00:00:01")
    'Application.EnableEvents = True
    ProcessQuote Target
    

    End Sub

  2. Assistant

    To achieve the additional functionality you described, you can add an If statement after the original loop to check if both conditions are false. If they are, then find the next blank cell in column A of the 'QUOTES' sheet and paste the values from the 'PREQUEST' sheet.

    Here is the updated code with the added functionality:

    Sub SearchSubmittedQuotes(Target As Range)
    
        Dim wsRequest As Worksheet
        Dim wsQuotes As Worksheet
        Dim cell As Range
        Dim searchValue As Variant
        Dim nextBlankRow As Long
        
        Application.EnableEvents = False
        Set wsRequest = ThisWorkbook.Sheets("PREQUEST")
        Set wsQuotes = ThisWorkbook.Sheets("QUOTES")
        
        wsQuotes.Unprotect Password:="edit"
        searchValue = Target.Offset(0, -7).Value
        For Each cell In wsQuotes.Range("A5:A" & wsQuotes.Cells(wsQuotes.Rows.Count, "A").End(xlUp).Row)
            If wsQuotes.Cells(cell.Row, 1).Value = searchValue Then
                If wsQuotes.Cells(cell.Row, 5).Value = wsRequest.Cells(Target.Row, "E").Value _
                    And wsQuotes.Cells(cell.Row, 6).Value = wsRequest.Cells(Target.Row, "F").Value Then
                    ThisWorkbook.Sheets("QUOTES").Unprotect Password:="edit"
                    wsQuotes.Cells(cell.Row, 8).Value = wsRequest.Cells(Target.Row, "H").Value ' NOTES
                    wsQuotes.Cells(cell.Row, 9).Value = wsRequest.Cells(Target.Row, "I").Value ' P REQUEST#
                    wsQuotes.Cells(cell.Row, 10).Value = wsRequest.Cells(Target.Row, "J").Value ' DATE
                    wsQuotes.Cells(cell.Row, 13).Value = "Finance"
                    ThisWorkbook.Sheets("QUOTES").Protect Password:="edit"
                End If
            End If
        Next cell
        
        ' Check if both conditions are false
        If Not wsQuotes.Cells(cell.Row, 5).Value = wsRequest.Cells(Target.Row, "E").Value _
            And Not wsQuotes.Cells(cell.Row, 6).Value = wsRequest.Cells(Target.Row, "F").Value Then
            ' Find the next blank cell in column A
            nextBlankRow = wsQuotes.Cells(wsQuotes.Rows.Count, "A").End(xlUp).Row + 1
            ThisWorkbook.Sheets("QUOTES").Unprotect Password:="edit"
            wsQuotes.Cells(nextBlankRow, 1).Value = wsRequest.Cells(Target.Row, "A").Value ' CONTRACTOR
            wsQuotes.Cells(nextBlankRow, 2).Value = wsRequest.Cells(Target.Row, "B").Value ' JOB
            wsQuotes.Cells(nextBlankRow, 3).Value = wsRequest.Cells(Target.Row, "C").Value ' SECTOR
            wsQuotes.Cells(nextBlankRow, 4).Value = wsRequest.Cells(Target.Row, "D").Value ' SUBSECTOR
            wsQuotes.Cells(nextBlankRow, 5).Value = wsRequest.Cells(Target.Row, "E").Value ' QUO/INV#
            wsQuotes.Cells(nextBlankRow, 6).Value = wsRequest.Cells(Target.Row, "F").Value ' VALUE
            wsQuotes.Cells(nextBlankRow, 7).Value = wsRequest.Cells(Target.Row, "G").Value ' LOCATION
            wsQuotes.Cells(nextBlankRow, 8).Value = wsRequest.Cells(Target.Row, "H").Value ' NOTES
            wsQuotes.Cells(nextBlankRow, 9).Value = wsRequest.Cells(Target.Row, "I").Value ' P REQUEST#
            wsQuotes.Cells(nextBlankRow, 10).Value = wsRequest.Cells(Target.Row, "J").Value ' DATE
            wsQuotes.Cells(nextBlankRow, 13).Value = "Finance"
            ThisWorkbook.Sheets("QUOTES").Protect Password:="edit"
        End If
    
        Target.AddComment "Quote sent to Finance " & Date
        wsQuotes.Protect Password:="edit"
        Application.Wait Now + TimeValue("00:00:01")
        ProcessQuote Target
    
    End Sub
    

    This code will now check if both conditions are false and if so, find the next blank cell in column A of the 'QUOTES' sheet and paste the values from the 'PREQUEST' sheet.

  3. User

    Can you please add a message after finding that both conditions are false to say "NO QUOTE ENTRY FOUND" new line "New Entry Will Be Recorded", vbInformation, "NO QUOTE ENTRY"

  4. Assistant

    Sure! You can add a message box after finding that both conditions are false to display the message "NO QUOTE ENTRY FOUND. New Entry Will Be Recorded" with the following code:

    Sub SearchSubmittedQuotes(Target As Range)
    
        Dim wsRequest As Worksheet
        Dim wsQuotes As Worksheet
        Dim cell As Range
        Dim searchValue As Variant
        Dim nextBlankRow As Long
        
        Application.EnableEvents = False
        Set wsRequest = ThisWorkbook.Sheets("PREQUEST")
        Set wsQuotes = ThisWorkbook.Sheets("QUOTES")
        
        wsQuotes.Unprotect Password:="edit"
        searchValue = Target.Offset(0, -7).Value
        For Each cell In wsQuotes.Range("A5:A" & wsQuotes.Cells(wsQuotes.Rows.Count, "A").End(xlUp).Row)
            If wsQuotes.Cells(cell.Row, 1).Value = searchValue Then
                If wsQuotes.Cells(cell.Row, 5).Value = wsRequest.Cells(Target.Row, "E").Value _
                    And wsQuotes.Cells(cell.Row, 6).Value = wsRequest.Cells(Target.Row, "F").Value Then
                    ThisWorkbook.Sheets("QUOTES").Unprotect Password:="edit"
                    wsQuotes.Cells(cell.Row, 8).Value = wsRequest.Cells(Target.Row, "H").Value ' NOTES
                    wsQuotes.Cells(cell.Row, 9).Value = wsRequest.Cells(Target.Row, "I").Value ' P REQUEST#
                    wsQuotes.Cells(cell.Row, 10).Value = wsRequest.Cells(Target.Row, "J").Value ' DATE
                    wsQuotes.Cells(cell.Row, 13).Value = "Finance"
                    ThisWorkbook.Sheets("QUOTES").Protect Password:="edit"
                End If
            End If
        Next cell
        
        ' Check if both conditions are false
        If Not wsQuotes.Cells(cell.Row, 5).Value = wsRequest.Cells(Target.Row, "E").Value _
            And Not wsQuotes.Cells(cell.Row, 6).Value = wsRequest.Cells(Target.Row, "F").Value Then
            ' Display message
            MsgBox "NO QUOTE ENTRY FOUND" & vbCrLf & "New Entry Will Be Recorded", vbInformation, "NO QUOTE ENTRY"
            
            ' Find the next blank cell in column A
            nextBlankRow = wsQuotes.Cells(wsQuotes.Rows.Count, "A").End(xlUp).Row + 1
            ThisWorkbook.Sheets("QUOTES").Unprotect Password:="edit"
            wsQuotes.Cells(nextBlankRow, 1).Value = wsRequest.Cells(Target.Row, "A").Value ' CONTRACTOR
            wsQuotes.Cells(nextBlankRow, 2).Value = wsRequest.Cells(Target.Row, "B").Value ' JOB
            wsQuotes.Cells(nextBlankRow, 3).Value = wsRequest.Cells(Target.Row, "C").Value ' SECTOR
            wsQuotes.Cells(nextBlankRow, 4).Value = wsRequest.Cells(Target.Row, "D").Value ' SUBSECTOR
            wsQuotes.Cells(nextBlankRow, 5).Value = wsRequest.Cells(Target.Row, "E").Value ' QUO/INV#
            wsQuotes.Cells(nextBlankRow, 6).Value = wsRequest.Cells(Target.Row, "F").Value ' VALUE
            wsQuotes.Cells(nextBlankRow, 7).Value = wsRequest.Cells(Target.Row, "G").Value ' LOCATION
            wsQuotes.Cells(nextBlankRow, 8).Value = wsRequest.Cells(Target.Row, "H").Value ' NOTES
            wsQuotes.Cells(nextBlankRow, 9).Value = wsRequest.Cells(Target.Row, "I").Value ' P REQUEST#
            wsQuotes.Cells(nextBlankRow, 10).Value = wsRequest.Cells(Target.Row, "J").Value ' DATE
            wsQuotes.Cells(nextBlankRow, 13).Value = "Finance"
            ThisWorkbook.Sheets("QUOTES").Protect Password:="edit"
        End If
    
        Target.AddComment "Quote sent to Finance " & Date
        wsQuotes.Protect Password:="edit"
        Application.Wait Now + TimeValue("00:00:01")
        ProcessQuote Target
    
    End Sub
    

    This code will display the message box with the specified message if both conditions are false before creating a new entry in the 'QUOTES' sheet.

  5. User

    Using VBA, is it possible to do the following:

    If I change the value in column A of my sheet PREQUEST, and my Target Offset(0, 1).Value = "" Then scan the following folder Dim folderPath As String folderPath = ThisWorkbook.Path & "\z Quotes On Hold" and check if the three letter word found in my Target Offset(0, 10).Value exists in any part of the named files in the folder 'ThisWorkbook.Path & "\z Quotes On Hold"'. If a matching three letter word is found Then PopUp a message "QUOTE FOUND ON HOLD" new line & name of file found

  6. Assistant

    Yes, it is possible to accomplish the task described using VBA. You can use the FileSystemObject to loop through files in a folder, read the contents of each file, and check if a specific three-letter word exists in the file names or content.

    Here is an example VBA code that demonstrates this functionality:

    Sub CheckForQuoteOnHold()
        Dim folderPath As String
        Dim fso As Object
        Dim folder As Object
        Dim file As Object
        Dim fileContent As String
        Dim searchWord As String
        
        ' Define folder path
        folderPath = ThisWorkbook.Path & "\z Quotes On Hold"
        searchWord = UCase(Target.Offset(0, 10).Value) ' Convert search word to uppercase for case insensitive search
        
        ' Create FileSystemObject
        Set fso = CreateObject("Scripting.FileSystemObject")
        
        ' Check if folder exists
        If fso.FolderExists(folderPath) Then
            Set folder = fso.GetFolder(folderPath)
            
            ' Loop through each file in the folder
            For Each file In folder.Files
                ' Check if the search word exists in the file name or content
                If InStr(1, UCase(file.Name), searchWord) > 0 Then
                    ' Read file content
                    fileContent = fso.OpenTextFile(file.Path).ReadAll
                    
                    ' Check if the search word exists in the file content
                    If InStr(1, UCase(fileContent), searchWord) > 0 Then
                        ' Display message if search word is found
                        MsgBox "QUOTE FOUND ON HOLD" & vbCrLf & file.Name, vbInformation
                        Exit Sub ' Exit the loop if a match is found
                    End If
                End If
            Next file
            
            ' Display message if search word is not found in any file
            MsgBox "NO QUOTE FOUND ON HOLD", vbInformation
        Else
            MsgBox "Folder not found: " & folderPath, vbExclamation
        End If
    End Sub
    

    You can call this CheckForQuoteOnHold subroutine when the value in column A of the PREQUEST sheet is changed and the condition Target.Offset(0, 1).Value = "" is met. The code will then search for the three-letter word in the files within the specified folder and display a message if a match is found or not found. Make sure to adjust the code as per your specific requirements and error handling needs.