Button to add data from 1st Sheet to 2nd Sheet based on criteria-CodePudding

I am rather new at VBA. Mostly learning it via youtube video.

I need a little guidance for VBA code that would allow me,

When I click the "UPDATE RECEIPT" button, it adds / updates value marked as A (in the RECEIPT sheet) to ROW C in the DATABASE sheet based on corresponding Invoice number marked as B

I found a previous posting by TheInternet on April 6, 2018, and tried using it as based but failed miserably.

Sub RecordReceipt()

x = 1

'this will find the column that matches the date and stores that as the copy location.
While Sheets("Sheet10").Cells(1, x).Value <> Sheets("Sheet9").Range("J15")
    x = x   1
Wend


'this portion copies the data to the designated coordinates found by the first portion and delete the information from L8 and L11.
Sheets("Sheet10").Cells(2, x).Value = Sheets("Sheet9").Range("Receipt!H5").Value
Sheets("Sheet10").Range("").Value = ""


End Sub

Really need your help :)

CodePudding user response：

Believe you want to update your 'RECEIPT NO' column in your sheet 'DATABASE' on change of Invoice number from another sheet. Recommend to use the built in functions you can trigger in 'code behind sheet' (Double click the sheet your project explorer ) - and parse this code to do you thing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim INVOICE_NO As String
Dim RECEIPT_NO As String

If Target.Address = "$H$8" Then
    INVOICE_NO = Target.Value
    RECEIPT_NO = Target.Offset(-3, 0).Value
    
    Set MyRange = ThisWorkbook.Worksheets("DATABASE").Range("F:F")
    With MyRange
    Set c = .Find(What:=INVOICE_NO, _
                                            After:=.Cells(1), _
                                            LookIn:=xlValues, _
                                            LookAt:=xlPart, _
                                            SearchOrder:=xlByRows, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=False)

    If Not c Is Nothing Then
       c.Offset(0, 3).Value = RECEIPT_NO
    Else
        MsgBox ("Can`t find Invoice " & INVOICE_NO & " in DATABASE")
    End If
    End With
End If
End Sub

This the first IF makes shure code only runs when you change content in cell $H$8, takes the offset value -3 rows up as the RECEIPT_NO. Then it searches in Range F - and when it finds the first instance it puts your RECEIPT_NO into the 3 columns offset of where it was found. No hit will return a message to the user.

CodePudding user response：

Yes. The Private Sub Worksheet_Change(ByVal Target As Range) is a built in trigger on worksheet change. You can also call it from the click of your button. And parse the Target as Range. Or just rewrite the code to read the two values from your sheet.