vba that would search, copy, and paste onto the same worksheet

[bobsburgers]

Hi! I need help with a VBA for a search input box that would copy the searched value and paste it to a different column in the same row.

Currently, we use a barcode scanner to enter items into the spreadsheet.

This would be to track items leaving the store that eventually come back.

I have some excel files to share if that helps - thank you!

 

[Logit]

Re: I need help with a vba that would search, copy, and paste onto the same worksheet please!

Being able to review your workbooks and any existing code is a big plus.

 

[bobsburgers]

Re: I need help with a vba that would search, copy, and paste onto the same worksheet please!

how could I send you those?

 

[bobsburgers]

Re: I need help with a vba that would search, copy, and paste onto the same worksheet please!

Below is a version I found online that I've been trying to modify.

The biggest difference between this Sub and the function I want is the paste destination of the copied value. I need it copy/pasted to the same worksheet, but a different column, rather than Worksheet 2.

Sub SearchForString()


Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

'Start search in row 4
LSearchRow = 4

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

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

'If value in column E = LSearchValue, copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

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

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

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

End If

LSearchRow = LSearchRow + 1

Wend

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

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

 

[Logit]

.

copy the searched value and paste it to a different column in the same row

What column ? Will it always be the same location ?

 

[NoSparks]

See if you can work with something along the lines of this...

Sub Testing_1()

    Dim scanstring As String
    Dim foundscan As Range

scanstring = InputBox("Please enter a value to search for", "Enter value")

With Sheets("Sheet1").Columns("A")
    
    Set foundscan = .Find(What:=scanstring, LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False)
                      
    If Not foundscan Is Nothing Then
        foundscan.Offset(0, 10).Value = scanstring '<~~ same row, 10 columns to right
    Else
        MsgBox scanstring & "  was not found"
    End If
    
End With
                        
End Sub

 

[bobsburgers]

.


What column ? Will it always be the same location ?

It would always be from column D to column H.

 

[bobsburgers]

This seems to be right on track; however, I'm at home and don't have the barcode scanner with me right now. Would that be the reason the search comes back with nothing if I enter it manually? Because that keeps happening. Let me know - thank you!!

 

[NoSparks]

Did you change the sheet name to what you have and the column to "D" ?
The offset requirement should change to (0, 4) , that's same row, 4 columns over from "D" to get to "H".

 

[bobsburgers]

Did you change the sheet name to what you have and the column to "D" ?
The offset requirement should change to (0, 4) , that's same row, 4 columns over from "D" to get to "H".

This seems to work perfectly - thank you so much!!

Would it be possible to add a button to the worksheet to start the macro and prompt the input box?