Need help creating UPDATE Macro for my Excel Database

[tommyb]

Hi all.

Im creating a basic database in Excel, which gives a user the ability to add, update, and search data within that sheet.

So far, I have created macros that lets the user search and add data, however Im stuck on how to update existing records.

I've got 2 tabs, one for Search, and one for Add, and Im planning to let the user search for a record, then be able to update the record they have searched for.

Below is the code that I have for adding a record, and Im hoping to amend it slightly so it can update the record that equals a primary ID (I hope that makes sense!)

Sub Button11_Click()

Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    'cells to copy from Input sheet
    myCopy = "C7,C9,C11,C13,C15,C17,C19,C21,C23,C25,C27,C29,C31,C33,C35,C37,C39,C41,C43"

    Set inputWks = Worksheets("Search Stock")
    Set historyWks = Worksheets("Stock Data")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy)

        If Application.CountA(myRng) <> myRng.Cells.Count Then
            MsgBox "TEST!"
            Exit Sub
        End If
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "dd/mm/yyyy hh:mm"
        End With
        oCol = 2
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

The primary ID is in cell C7.

Any help is much appreciated!

 

[tommyb]

Anyone

 

[tommyb]

 

[tommyb]

Can anyone help? Desperately need this done asap

 

[tommyb]

Helpppp

 

[xenou]

It depends on how your input is structured. Just find the cell with the primary key and write the values from the user input to the appropriate columns in the same row.