Open a new instance of Notepad.exe Using the value of the active cell Search column A…
- User
Open a new instance of Notepad.exe Using the value of the active cell Search column A from A5 down to the first empty cell in sheet QUOTES for a match. For each match found in sheet QUOTES where the corresponding value in column M of the same row is empty Then copy the values of each row from A to G Activate notepad and paste each row value seperated by a line
- Assistant
Here is a VBA code snippet that accomplishes the task described:
Sub SearchAndCopyToNotepad() Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer ' Activate Notepad Set notepadApp = CreateObject("WScript.Shell") notepadApp.Run "notepad.exe" ' Get active workbook and worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("QUOTES") ' Get the range to search Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) ' Loop through each cell in the range For Each cell In rng If cell.Value = ActiveCell.Value Then If ws.Cells(cell.Row, 13).Value = "" Then ' Copy values from columns A to G For i = 1 To 7 notepadApp.AppActivate "Untitled - Notepad" SendKeys ws.Cells(cell.Row, i).Value & "~" Next i End If End If Next cell End SubThis code will search for the value of the active cell in column A of the QUOTES sheet and copy the values of columns A to G for each matching row where the corresponding value in column M is empty. It will then activate Notepad and paste each row's values separated by a line. Note that you may need to make adjustments based on your specific requirements.
- User
Is this correct - If cell.Value = Me.ActiveCell. Value Then
- Assistant
Yes, that is correct. If you are running the VBA code from a worksheet module, you can use
Me.ActiveCell.Valueto refer to the value of the active cell in that particular worksheet.So, the line
If cell.Value = Me.ActiveCell.Value Thenwould compare the value of the cell in column A of the QUOTES sheet with the value of the active cell in the currently active worksheet.This will work if the VBA code is placed in the worksheet module (for example, Sheet1, Sheet2, etc.) of the Excel file.
- User
The values in the ActiveCell are always Text values. Will this modigfication to the VBA work. Please note the Dim qt As String..... Sub SearchAndCopyToNotepad() Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer Dim qt As String
' Activate Notepad Set notepadApp = CreateObject("WScript.Shell") notepadApp.Run "notepad.exe" ' Set workbook and worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("QUOTES") Set qt = Me.ActiveCell.Value ' Get the range to search Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) ' Loop through each cell in the range For Each cell In rng If cell.Value = qt Then If ws.Cells(cell.Row, 13).Value = "" Then ' Copy values from columns A to G For i = 1 To 7 notepadApp.AppActivate SendKeys ws.Cells(cell.Row, i).Value & "~" Next i End If End If Next cellEnd Sub
- Assistant
Yes, your modification to the VBA code looks correct. By declaring
qtas a string variable and setting it toMe.ActiveCell.Value, you are storing the text value of the active cell in theqtvariable for comparison in the loop.So now, in the line
If cell.Value = qt Then, the code will check if the value of the cell in column A of the QUOTES sheet is equal to the text value stored in theqtvariable.The rest of your code remains the same, looping through the range and copying values from columns A to G for each matching row where the corresponding value in column M is empty.
Your code should work as intended for searching and copying the required values to Notepad based on the text value of the active cell.
- User
Will this code send the values to notepad one character at a time or will it send it one cell at a time
- Assistant
The
SendKeysfunction in VBA sends keystrokes to the active application one character at a time. In the code snippet provided, each value from columns A to G is being sent to Notepad usingSendKeysin a loop.If you want to send one cell at a time to Notepad, you can concatenate or format the values from columns A to G into a single string before sending it using
SendKeys. Here is an updated version of your code that sends each row value as one string to Notepad:Sub SearchAndCopyToNotepad() Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer Dim qt As String ' Activate Notepad Set notepadApp = CreateObject("WScript.Shell") notepadApp.Run "notepad.exe" 'Set workbook and worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("QUOTES") qt = Me.ActiveCell.Value ' Get the range to search Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) ' Loop through each cell in the range For Each cell In rng If cell.Value = qt Then If ws.Cells(cell.Row, 13).Value = "" Then ' Combine values from columns A to G into a single string Dim rowString As String For i = 1 To 7 rowString = rowString & ws.Cells(cell.Row, i).Value & vbCrLf Next i ' Activate Notepad and paste the rowString notepadApp.AppActivate "Untitled - Notepad" SendKeys rowString End If End If Next cell End SubIn this updated code, each row's values from columns A to G are concatenated into
rowStringand then sent to Notepad as a single string separated by new lines. This should send the complete row value at once instead of character by character. - User
Which will be best, one cell at a time or the entire row at a time
- Assistant
The best approach depends on your specific requirements and preferences:
-
Sending one cell at a time:
- If you want each cell value to be inputted separately in Notepad, sending one cell at a time using the
SendKeysfunction can provide more controlled and granular input. - This approach may be preferable if you need to format or modify individual cell values before sending them to Notepad.
- If you want each cell value to be inputted separately in Notepad, sending one cell at a time using the
-
Sending the entire row at a time:
- If you want to maintain the structure of the row and have each row displayed as a unit in Notepad, sending the entire row at a time as a single string can provide a more coherent representation.
- This approach can be more convenient when you want to view and work with the entire row as a single entity.
Consider the readability and usability of the data in Notepad, as well as any formatting or processing requirements you may have. Testing both approaches with sample data can help you decide which method best suits your needs.
-
- User
Can you please then write the code below to send One Row At A time: Sub SearchAndCopyToNotepad() Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer Dim qt As String
' Activate Notepad Set notepadApp = CreateObject("WScript.Shell") notepadApp.Run "notepad.exe" ' Set workbook and worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("QUOTES") Set qt = Me.ActiveCell.Value ' Get the range to search Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) ' Loop through each cell in the range For Each cell In rng If cell.Value = qt Then If ws.Cells(cell.Row, 13).Value = "" Then ' Copy values from columns A to G For i = 1 To 7 notepadApp.AppActivate SendKeys ws.Cells(cell.Row, i).Value & "~" Next i End If End If Next cellEnd Sub
- Assistant
Sure, here is the modified code that sends one row at a time to Notepad:
Sub SearchAndCopyToNotepad() Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer Dim qt As String Dim rowString As String ' Activate Notepad Set notepadApp = CreateObject("WScript.Shell") notepadApp.Run "notepad.exe" ' Set workbook and worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("QUOTES") qt = Me.ActiveCell.Value ' Get the range to search Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) ' Loop through each cell in the range For Each cell In rng If cell.Value = qt Then If ws.Cells(cell.Row, 13).Value = "" Then ' Combine values from columns A to G into a single string For i = 1 To 7 rowString = rowString & ws.Cells(cell.Row, i).Value & " " Next i ' Activate Notepad and paste the rowString notepadApp.AppActivate "Untitled - Notepad" SendKeys rowString & vbCrLf rowString = "" End If End If Next cell End SubIn this modified code, each row is concatenated into
rowStringand then sent to Notepad as a single string, with each cell value separated by a space and a new line after each row. This will send one row at a time to Notepad. Let me know if you require any further assistance. - User
This is how the VBA code is and it works accurately and quickly. Is there a way of putting a "," to seperate each cell of the row that is pasted in notepad.
Sub SearchPendingQuotes()
If ActiveCell.Column <> 1 Or ActiveSheet.Name <> "PREQUEST" Then Application.EnableEvents = True Me.Protect Password:="edit" Exit Sub End If
Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer Dim qt As String Dim rowString As String ' Activate Notepad Set notepadApp = CreateObject("WScript.Shell") notepadApp.Run "notepad.exe" ' Set workbook and worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("QUOTES") qt = ActiveCell.Value ' Get the range to search Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.count, "A").End(xlUp).row) Application.EnableEvents = False ' Loop through each cell in the range For Each cell In rng If cell.Value = qt Then If ws.Cells(cell.row, 13).Value = "" Then ' Combine values from columns A to G into a single string For i = 1 To 7 rowString = rowString & ws.Cells(cell.row, i).Value & " " Next i ' Activate Notepad and paste the rowString notepadApp.AppActivate "Untitled - Notepad" SendKeys rowString & vbCrLf rowString = "" End If End If Next cell Call Module57.ClearClipboard Application.EnableEvents = True Me.Protect Password:="edit"End Sub
- Assistant
To modify the VBA code to separate each cell value in the row with a comma before pasting it into Notepad, you can update the code as shown below:
Sub SearchPendingQuotes() If ActiveCell.Column <> 1 Or ActiveSheet.Name <> "PREQUEST" Then Application.EnableEvents = True Me.Protect Password:="edit" Exit Sub End If Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim notepadApp As Object Dim i As Integer Dim qt As String Dim rowString As String ' Activate Notepad Set notepadApp = CreateObject("WScript.Shell") notepadApp.Run "notepad.exe" ' Set workbook and worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("QUOTES") qt = ActiveCell.Value ' Get the range to search Set rng = ws.Range("A5:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) Application.EnableEvents = False ' Loop through each cell in the range For Each cell In rng If cell.Value = qt Then If ws.Cells(cell.Row, 13).Value = "" Then ' Combine values from columns A to G into a single string with comma separators For i = 1 To 7 rowString = rowString & ws.Cells(cell.Row, i).Value & "," Next i ' Remove the last comma rowString = Left(rowString, Len(rowString) - 1) ' Activate Notepad and paste the rowString notepadApp.AppActivate "Untitled - Notepad" SendKeys rowString & vbCrLf rowString = "" End If End If Next cell Call Module57.ClearClipboard Application.EnableEvents = True Me.Protect Password:="edit" End SubIn this updated code, each cell value in the row is concatenated with a comma separator, and then before pasting into Notepad, the last comma is removed. This way, each cell value will be separated by a comma when pasted into Notepad.