(Solved) – How can i Paste to Different Row after Getting search Results

  • by
(solved)-–-how-can-i-paste-to-different-row-after-getting-search-results

I need help on how can i paste a result on Column “G” Row “2” after searching an entire Column A for the word “Country Code: ” . Below is my Code for the process.

Currently My Code is able to copy the specific word and paste it .
But it only paste on Column A which overlap the existing data.

 Sub SearchForString()

   Dim LSearchRow As Integer
   Dim LCopyToRow As Integer
   Dim LCopytoColumn As String


   'On Error GoTo Err_Execute

   'Start search in row 1
    LSearchRow = 1

   'Start copying data to column G in Database
    LCopytoColumn = 7

   'Start copying data to row 2 in Database and Loop results
    LCopyToRow = 2


   While Len(Range("A" & CStr(LSearchRow)).Value) > 0

          'If value in column E = "Mail Box", copy entire row to Sheet2
           If InStr(1, Range("A" & CStr(LSearchRow)).Value, "Country Code:") > 0 Then

         'Select row in Sheet1 to copy
          Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
          Selection.Copy

          'Paste row into G2 in next row
           Range("G2").Select
          'Columns(CStr(LCopytoColumn) & ":" & CStr(LCopytoColumn)).Select
           Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
           ActiveSheet.Paste


           'Move counter to next row
            LCopyToRow = LCopyToRow   1

           'Go back to Database to continue searching
           Sheets("Database").Select

        End If

        LSearchRow = LSearchRow   1

   Wend

   'Position on cell G2
    Application.CutCopyMode = False
    Range("G2").Select

    MsgBox "All matching data has been copied."

   Exit Sub

'Err_Execute:
'   MsgBox "An error occurred."

 End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *