Populate data in specific columns based on criteria - VBA

[sidt87]

Hi,

I am a creating an inventory database with about 100+ items. I am using a userform to select the ITEM PART via combobox, PO# via textbox and QUANTITY via textbox then enter it into my inventory database. What I would like it to do is search for the item number that was selected in the combobox in columns A1:AA1, then find the last empty row in that column and input the PO number and quantity.

My inventory database looks something like this, where as row 1 is the ITEM PARTS

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item1[/TD]
[TD]Item2[/TD]
[TD]Item3[/TD]
[TD]Item4[/TD]
[TD]Item5[/TD]
[TD]Item6[/TD]
[TD]Item7[/TD]
[TD]Item8[/TD]
[TD]Item9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I would like it to do is if I select and enter the following

ITEM PART: Item5
PO#: 12345
QUANTITY: 1000

it would find the column with Item5 and populate as below


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item1[/TD]
[TD]Item2[/TD]
[TD]Item3[/TD]
[TD]Item4[/TD]
[TD]Item5[/TD]
[TD]Item6[/TD]
[TD]Item7[/TD]
[TD]Item8[/TD]
[TD]Item9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PO# 12345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help is much appreciated!

Thanks!

 

[Norie]

Perhaps this will help you get started.

Dim Res As Variant

    Res = Application.Match(cmbItems.Value, Rows(1), 0)

    If Not IsError(Res) Then
        Cells(Rows.Count).End(xlUp).Offset(,1).Resize(2).Value =Application.Transpose(Array(txtPONo.Value, txtQuantity.Value))
    End If

 

[sidt87]

Alright, so here's what I ended up doing

Private Sub CommandButton1_Click()


Dim ws As Worksheet
Dim iRow As Long
Dim Lastrow As Long
Dim Found As Range


Set ws = Worksheets("Inventory Log")


Lastrow = ws.Range("C:WD").Find("*", , , , xlByRows, xlPrevious).Row


If Me.itemnumber.Value = "" Then
    MsgBox "select item number please"
    Else
        Set Found = ws.Range("C:WD").Find(What:=Me.itemnumber.Value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByColumns, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
                                                       
        If Found Is Nothing Then
                MsgBox "Not Found"
        Else
            For iRow = 15 To Lastrow
                ws.Cells(iRow, Found.Column).Value = Me.ponumber.Value
                ws.Cells(iRow, Found.Column).Offset(1, 0).Value = Me.quantity.Value
            Next iRow
        End If
 End If
 
With ws
.Cells(iRow, Found.Column).Value = "PO#:" & "" & Me.ponumber.Value
.Cells(iRow, Found.Column).Offset(1, 0).Value = Me.quantity.Value
End With




End Sub

My first entry turns out ok but when I do a second entry, it populates into the same cells plus into the next two cells its suppose to. Any idea how to fix this?

Thanks!

 

[Zanmato]

Is it me or do you have the code doing the same thing twice here? Correct me if I'm wrong please:

 For iRow = 15 To Lastrow
                ws.Cells(iRow, Found.Column).Value = Me.ponumber.Value
                ws.Cells(iRow, Found.Column).Offset(1, 0).Value = Me.quantity.Value
            Next iRow

With ws
.Cells(iRow, Found.Column).Value = "PO#:" & "" & Me.ponumber.Value
.Cells(iRow, Found.Column).Offset(1, 0).Value = Me.quantity.Value
End With

Sorry that's not a solution I know, I'm just trying to understand as much as I can on this site today.

 

[sidt87]

Is it me or do you have the code doing the same thing twice here? Correct me if I'm wrong please:

 For iRow = 15 To Lastrow
                ws.Cells(iRow, Found.Column).Value = Me.ponumber.Value
                ws.Cells(iRow, Found.Column).Offset(1, 0).Value = Me.quantity.Value
            Next iRow

With ws
.Cells(iRow, Found.Column).Value = "PO#:" & "" & Me.ponumber.Value
.Cells(iRow, Found.Column).Offset(1, 0).Value = Me.quantity.Value
End With

Sorry that's not a solution I know, I'm just trying to understand as much as I can on this site today.

I'm still a noob myself, but from what I understand whenever I use For and Next, I have to follow it with With and End With in order for the data to actually input. Maybe someone else can chime in on your question.