Thread: Search Sheet2 Column A For Value In Textbox On Sheet1 Then Offset Right By 1-5 Column

Having trouble with a a macro. I have a search function that works just fine. It pulls information from Sheet2 and places the data (which will always be integers) in 5 cells on Sheet1.

My problem is this: I have added buttons to the right (of the cells that hold the data on Sheet1) which needs to increment or decrement the left-most respective values up or down by one... which I can do but the hard part for me is...

I then need to search in column A to find the value in the textbox/searchbox field and when found, I need to offset to the right (columns B, C, D, E, or F depending on the "Service n"). When offset and the cell is found, I need to update this value with the newly updated value created by clicking the up or down button (basically adding or subtracting 1 from this cell).

Essentially, this is used to update a product SKU quantity. When one SKU is sold, I have the "Down" button there to update the inventory amount so I enter the SKU, Press Search, then find the service I want to decrement, press the "Down" button to the right when a SKU is sold, and it would decrement that SKU quantity on Sheet2 by 1 for that particular service.

I hope I've explained it thoroughly. I'll send $5 to the first person's Paypal that can figure this out. I'm sure it's simple and I'm overlooking something. I told my girlfriend I could figure this out but I need some help fellas (and ladies)!

If you have a minute, please have a peek at my attached Excel document for a clear idea of what I mean.

I believe that you can do this much more simply.

Your Down sub can be

Code:

Sub Down_Service1()
    Range("C9") = Range("C9") - 1
End Sub

On the Sheet1 tab in the VBA editor add the following. You will need to create the other up and down versions of this.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRow As Integer
If Application.CountIf(Sheets("Sheet2").Columns(1), TextBox1.Value) = 1 Then
    TargetRow = Sheets("Sheet2").Columns(1).Find(TextBox1.Value).Row
Else
    MsgBox "Unknown item"
    Exit Sub
End If
'repeat the next three lines to deal with the remaining columns
If Target.Address = "$C$9" Then
    Sheets("Sheet2").Cells(TargetRow, 2) = Target
End If




End Sub

I hope that this gets you started.

I've modified your workbook a little: