Using VBA to Find and Replace certain cells based on value in Column A

DJMXM

New Member
Joined
Jun 19, 2013
Messages
45
Hello All - I am trying to create a userform that pulls information from a list of liquors (Column "A") .... Once I input the data, I would like to find and replace the the original data in the corresponding row (but only in certain cells) (i.e. Column "I', "K", "L", "M", "N", "O", "Q", "R") and leave the other data intact. This form is being used to allow a user to barcode scan bottle (Being set up to refer to Column "A" Range) and then input required data (i.e. Number of bottles added, Current Bottles in House... ect.... ) and when I click update button (Not Installed Yet - You can add if you want to) it will update information in that row. I hope I am making sense. I have included the userform code below. Thank You in advance for your help!!

Mike

Code:
Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Worksheets("Liquor & Wine Inventory").Range("A5:A205").Value
        With ComboBox2
            .AddItem "0"
            .AddItem "1"
            .AddItem "2"
            .AddItem "3"
            .AddItem "4"
        End With
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox3 = vbNullString Then Exit Sub
            If IsNumeric(Me.TextBox3.Value) Then
        Me.TextBox3.Value = Format(Me.TextBox3.Value, "Currency")
    End If
End Sub

Private Sub TextBox_Exit()
    TextBox1.Value = Format(TextBox1.Value, "#,##0.000")
End Sub

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me
        If IsEmpty(.TextBox4) Then Exit Sub
        .TextBox6.Value = (Val(.TextBox4.Value) + Val(.TextBox5.Value)) * 1
    End With
End Sub

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Set wsSource = Worksheets("Liquor & Wine Inventory")
wsSource.Activate

    LiquorInventory.TextBox1.Value = WorksheetFunction.VLookup(ComboBox1.Value, Range("A5:R205"), 2)
    LiquorInventory.TextBox2.Value = WorksheetFunction.VLookup(ComboBox1.Value, Range("A5:R205"), 4)
    LiquorInventory.TextBox3.Value = WorksheetFunction.VLookup(ComboBox1.Value, Range("A5:R205"), 5)
    LiquorInventory.TextBox4.Value = WorksheetFunction.VLookup(ComboBox1.Value, Range("A5:R205"), 9)
End Sub
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top