VBA - Search macro to retrieve/copy data from row and populate specific targeted cells. Reverse data entry

Question

I am looking to reverse lookup data entered via a form. I have a button to transfer data from the form into a table, VBA code as below, if there is a way of adapting this code:

Sub data_input()

ws_output = "Shelf Stock Data"

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("staff_name").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("supplier").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("signed").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("po_number").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("roll_length").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("roll_width").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("shelf_location").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("date").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("in_out").Value
Sheets(ws_output).Cells(next_row, 10).Value = Range("notes_comments").Value
  
MsgBox "Submitted - Please clear the workbook before you save"
  
End Sub

I am now trying to retrieve this information that is stored in rows and repopulate the form fields, to enable quick editing of stock movements, by searching the "po_number".

The end-users are very computer illiterate, so a simple button and search bar is the best solution.

The data table will potentially have multiple entries for the same "po_number", so would it be possible to only display the latest entry for the search result?

Thank you for any help you can offer.

Hi, there is a way to do that, you would need to set range of your data. and then just find your number starting from bottom, so just set range, and reverse [.find](https://stackoverflow.com/questions/22464631/perform-a-find-within-vba-from-the-bottom-of-a-range-up) . You can then set search find row, and get rest of data.. — p77u77n77k, Jul 23 '21 at 12:27
Hi, thanks for your reply. Can you please show me an example of this with my dataset? I'm only just wrapping my head around VBA. — Damien, Jul 23 '21 at 17:57

score 1 · Accepted Answer · answered Jul 24 '21 at 11:06

Option Explicit

Sub data_search()

    Dim wb As Workbook, rng As Range
    Dim ws_output As Worksheet, ws_input As Worksheet
    Dim iLastRow As Long, r As Long, po As String

    Set wb = ThisWorkbook
    Set ws_input = wb.Sheets("Input Form")

    ' po to search
    po = Trim(Range("po_number").Value)
    If Len(po) = 0 Then
        MsgBox "Search term empty", vbExclamation
        Exit Sub
    End If

    ' search column D for po
    Set ws_output = wb.Sheets("Shelf Stock Data")
    iLastRow = ws_output.Cells(Rows.Count, "D").End(xlUp).Row
    Set rng = ws_output.Range("D2:D" & iLastRow).Find(po, _
          LookIn:=xlValues, _
          lookat:=xlWhole, _
          SearchDirection:=xlPrevious)

    ' result of search
    If rng Is Nothing Then
        MsgBox po & " not found", vbExclamation
    Else
        r = rng.Row
        With ws_output
            Range("staff_name").Value = .Cells(r, 1).Value
            Range("supplier").Value = .Cells(r, 2).Value
            Range("signed").Value = .Cells(r, 3).Value
            Range("roll_length").Value = .Cells(r, 5).Value
            Range("roll_width").Value = .Cells(r, 6).Value
            Range("shelf_location").Value = .Cells(r, 7).Value
            Range("date").Value = .Cells(r, 8).Value
            Range("in_out").Value = .Cells(r, 9).Value
            Range("notes_comments").Value = .Cells(r, 10).Value
        End With
    End If
  
End Sub

VBA - Search macro to retrieve/copy data from row and populate specific targeted cells. Reverse data entry

1 Answers1