VBA Code to update records on one Worksheet with another

[GazNicki]

Hi

That may take a bit of explaining, so I will link in my previous thread that this is relating to.

I have a Workbook with a Log Sheet and a Form. With the click of a button, the contents of my form are transferred to the log sheet, and save this information in the next available row. This works well, and my thread on this can be seen here: VBA to submit contents of one page to another

On the Log Sheet, in the A Column, there is a code that generates a reference ID for each issue that is logged through the form. This also works, and on another form I am able to click the reference from a drop down, and populate the cells with the information in the log. Again, this works well.

My question is in regards to this second form. When I populate the information, there are some cells which are blank as they don't have information in. Form 2 is intended to put that information in place.

Where would I start with the VBA code to transfer the information from Form 2 into the log, but taking into account the row it needs to populate is determined by the unique code in the A column?

In my head, it is a case of VLookup to find the unique code, that identifies the row, then enter the text into the relevant cells on that row.

But I have no idea how this is done in VBA.

I assume it is simply amending the code that finds the "next empty line" with one that finds the correct line based on the contents of A.

Any pointers on where I should start?

 

[GazNicki]

UPDATE:

Below is my code. I have identified the reference cell, then set iRow as that. But now I am stuck. I need to search my table (NCLog) in the A column to find that reference, and then update the cells with the information.

Can anyone please help me with this next step?

Private Sub CommandButton1_Click()

    Dim NCLog As Worksheet
    Dim NCForm As Worksheet
    Dim iRow As Long
    
    'Confrimation Message code STARTS
    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to update the record?", vbYesNo + vbInformation, "Confirmation")
    
    If msgValue = vbNo Then Exit Sub
    'Confrimation Message code ENDS
       
    'Lets define the workbooks
    Set NCLog = ThisWorkbook.Sheets("NC Log")
    Set NCForm = ThisWorkbook.Sheets("NC Submission Form")
    
    'Set the filename for the PDF document to include the date of printing, and set the output folder to the the correct folder.
    Dim ReportFileName As String
    Dim ReportID As String
    FName = Sheet3.Range("C7").Text
    ReportFileName = ("\\DC0\Bakery2\TECHSPEC\Technical\Manuals\36. Non Conformance Reports\NC Report - " & ReportID & ".pdf")
    ChDir "\\DC0\Bakery2\TECHSPEC\Technical\Manuals\36. Non Conformance Reports\Reports\"
    
    'Export the document as  PDF, save it and open it up for immediate reading.
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ReportFileName, OpenAfterPublish:=False
    
    'Lets save the contents of the report to the Non-Conformance Log
    iRow = Sheet3.Range("C7").Text 'This is the reference I need to match to within NCLog in Column A
    
    iRow = NCLog.Cells(Rows.Count, "B").End(xlUp).Row + 1 'THIS IS THE CODE I NEED TO AMEND I THINK
        
    With NCLog
        .Cells(iRow, 11) = NCForm.Range("F20").Value
        .Cells(iRow, 12) = NCForm.Range("A23").Value
        .Cells(iRow, 13) = NCForm.Range("H25").Value
        .Cells(iRow, 14) = NCForm.Range("C25").Value
        .Cells(iRow, 16) = NCForm.Range("F29").Value
        .Cells(iRow, 17) = NCForm.Range("C31").Value
        .Cells(iRow, 18) = NCForm.Range("H31").Value
        
    End With
    
    'Finally, lets clear the Non Conformance Log for next time
    NCForm.Range("C7,F20,A23,H25,C25,F29,C31,H31").Value = ""
    
    MsgBox "The Corrective Action has been logged and closed off. Please check the NC Log for information. Should you wish to print this record, please use the 'NC Report' tab."
    
End Sub

 

[GazNicki]

I have also asked the same question over on StackOverflow: Use VBA to copy information to another sheet, based on a cell value

 

[Fluff]

Also posted here Using VBA to copy from one sheet to the next with the row pre-determined

 

[GazNicki]

Thanks Fluff.

I'm not sure if I have asked the question correctly as I can't seem to find any answer - even googling

 

[GazNicki]

**UPDATE**

I have resolved this issue with a work-around.

Instead of looking to amend the information to the existing record, I now add the information to the Log on a different row. As the reference in the A column is repeated, I then have separate code which then searches the log for duplicates and merges the row (big thanks to @offthelip for that).

I have then amended the new code with an additional set of VBA which then sorts the table, then looks for and deletes any empty rows.