Lookup / Paste Multi Values VBA

[Mav]

Hello,

I'm trying to create a tool to auto populate a form from data in a log on Sheet1 to a Form on Sheet2.

Steps I would like to take:
1) Press "Transfer" button
2) Enter Transfer number
3) Form populates with all the values found for that transfer number. Even multi rows if the number appears multiple times.

I've created a sample Workbook of the general layout.

Thanks in advance!

[Mav]

Note: I know hot to populate one row with a VLookUp function. I'm having trouble populating more than one row.

[NoSparks]

What's the difference between Transfer Number and ID Number ?
Is the date for a given Transfer Number on the transfer log always the same ?

I ask because I wonder why the Transfer Form has both Transfer Number and ID Number and only one location for date.

If you don't require dates when copying your data, you may want to use range.find and .findnext rather than vlookup.
If the dates could be different, and you want them with the rest of the data, you could just filter and copy the transfer log.

[Mav]

Sorry, Transfer Form wasn't suppose to have Transfer ID in column A. I've deleted it and reattached. This is a basic sample of what I need. The real form is a corporate form that has more detail complicated looking. I can use VLookUp for most because the information is the same for all rows of the Transfer Number. The Transfer Number and Date are the same for all lines of the transfer and are placed in a different section of the form. This can be done with a VLookUp.

I need to learn how to copy over the part information for all instances of the Transfer Number in the Transfer Log.

[NoSparks]

To find locations of the Transfer Number you might want to use range.find and .findnext
See https://msdn.microsoft.com/en-us/lib.../ff839746.aspx

Here's how it could be used in your Transfer sub

Sub Transfer()
    
TransferNumber = InputBox(Prompt:="Transfer Number?", Title:="Number?")
With Sheets("Transfer Log").Range("A:A")
    Set fndNum = .Find(TransferNumber, LookIn:=xlValues)
    If Not fndNum Is Nothing Then   '<~~ the transfer number was found
        firstAddress = fndNum.Address
        Do
            With Sheets("Transfer Form")
                'find the row to write to
                writeRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                .Cells(writeRow, 1).Resize(1, 4).Value = fndNum.Offset(0, 2).Resize(1, 4).Value
            End With
            Set fndNum = .FindNext(fndNum)
        Loop While Not fndNum Is Nothing And fndNum.Address <> firstAddress
    End If
End With

End Sub

[Mav]

After looking at this for a few hours lol. I think I'm following it. Is this the line of code that pastes in the data?

.Cells(writeRow, 1).Resize(1, 4).Value = fndNum.Offset(0, 2).Resize(1, 4).Value

It looks like it grabs all of the data right of the Offset. It needs to be very specific on what is copied. There is other info in the real log to the right that doesn't go into the form. If the Transfer Number is XX00002345 then the copied data needs to be specifically C4:F4.

Learning a lot. Thanks in advance

[NoSparks]

Yes, that's the line but it doesn't actually paste. It makes one range equal to the other range which (apparently) is more efficient than copy and paste, also doesn't leave cells selected or highlighted or have the "marching ants" or anything that might need to be dealt with.

You can change that line to what ever is necessary. Had the "copy to cells" not been contiguous it would need to have been broken down to as many as 4 separate statements.

Point of the response was you can use .find and .findnext to get multiple rows.
fndNum is a range and from there you can use .offset(rows,columns) or like Range("C" & fndNum.Row) to pick out which ever cells you require.