The following VBA code bellow, works perfectly. However, I would like to ADD other…
- 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 IfApplication.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 TargetEnd Sub
- 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 SubThis 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.
- 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"
- 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 SubThis code will display the message box with the specified message if both conditions are false before creating a new entry in the 'QUOTES' sheet.
- 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
- 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 SubYou can call this
CheckForQuoteOnHoldsubroutine when the value in column A of thePREQUESTsheet is changed and the conditionTarget.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.