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

please correct errors in this code: Sub ReminderToNote() 'Only run the code if a single…

  1. User

    please correct errors in this code: Sub ReminderToNote()

    'Only run the code if a single cell is selected If Selection.Cells.count = 1 Then

    'Copy values from columns D, E, F, and G of the active row Dim noteText As Range Set noteText = Range(“D:G” & Selection.row)

    noteText.Copy

    Shell “notepad.exe”, vbNormalFocus SendKeys “^V” SendKeys “^{HOME}”

    MsgBox “Values copied to note!”

    Else MsgBox “Please select a single cell in the row you want to copy.” End If

    End Sub

  2. Assistant

    Sub ReminderToNote()

    'Only run the code if a single cell is selected If Selection.Cells.Count = 1 Then

    'Copy values from columns D, E, F, and G of the active row Dim noteText As Range Set noteText = Range("D" & Selection.Row & ":G" & Selection.Row)

    noteText.Copy

    Shell "notepad.exe", vbNormalFocus SendKeys "^V" SendKeys "^{HOME}"

    MsgBox "Values copied to note!"

    Else MsgBox "Please select a single cell in the row you want to copy." End If

    End Sub

  3. User

    That worked great. Is it possible to put each of the cells from D to G into a single line in the text document

  4. Assistant

    Yes, it is possible to put each of the cells from D to G into a single line in the text document. You can use the Join function to achieve this. Here's how you can modify the code:

    Sub ReminderToNote()

    'Only run the code if a single cell is selected If Selection.Cells.Count = 1 Then

    'Copy values from columns D, E, F, and G of the active row Dim noteText As Range Set noteText = Range("D" & Selection.Row & ":G" & Selection.Row)

    Dim strNote As String strNote = Join(Application.Transpose(noteText.Value), ", ")

    Shell "notepad.exe", vbNormalFocus SendKeys strNote SendKeys "^{HOME}"

    MsgBox "Values copied to note!"

    Else MsgBox "Please select a single cell in the row you want to copy." End If

    End Sub

    In this code, the Join function combines the values into a single string with a comma separator. The Transpose function is used to convert the range into a vertical array so that Join can work its magic. The strNote variable holds the final string that is sent to Notepad.

  5. User

    I am getting an invlid procedure call or argument error

  6. Assistant

    The error is probably caused by the Transpose function. If your range has only one row, transpose will not work. You can modify the code to handle this case by checking the number of rows in the range:

    Sub ReminderToNote()

    'Only run the code if a single cell is selected If Selection.Cells.Count = 1 Then

    'Copy values from columns D, E, F, and G of the active row Dim noteText As Range Set noteText = Range("D" & Selection.Row & ":G" & Selection.Row)

    Dim strNote As String If noteText.Rows.Count = 1 Then strNote = Join(Application.Transpose(noteText.Value), ", ") Else strNote = "Range must have only one row" End If

    Shell "notepad.exe", vbNormalFocus SendKeys strNote SendKeys "^{HOME}"

    MsgBox "Values copied to note!"

    Else MsgBox "Please select a single cell in the row you want to copy." End If

    End Sub

    In this code, the If statement checks if the range has only one row. If it does, the Join function is used to combine the values into a single string with a comma separator. If the range has more than one row, the strNote variable holds an error message.