Update data based on serial number

Question

I have a UserForm that enables users to enter data into a worksheet.

A serial number is created for each row of data based on 2 ComboBox selection and 0001 at the end.

For example, MAPR0001 where MA comes from a ComboBox and PR from another one and at the end 0001 is added and is incremented for another selection of MA and PR. (MAPR0002)

Then I have a second UserForm that should allow me to update my database.
Upon selection of a serial number from a ComboBox the second UserForm pulls back the data from the worksheet to some TextBoxes. Till here everything works fine.

But I fail to add data to a specific serial number.

My code for the command button:

Private sub Commandbuttonclick ()
    If Me.ComboBox1.Value = "" Then
        MsgBox "Request No. Can Not be Blank", vbExclamation, "Request No."
        Exit Sub
    End If

    requestno = Me.ComboBox1.Value
    Sheets("DASHBOARD").Select

    Dim rowselect As Double
    rowselect = Me.combobox1.Value
    rowselect = rowselect + 1
    Rows(rowselect).Select

    Cells(rowselect, 2) = Me.TextBox1.Value
    Cells(rowselect, 3) = Me.TextBox2.Value
    Cells(rowselect, 4) = Me.TextBox3.Value

Looking good to me, I'm just the wrong person to answer it ;) — Cherusker, Jan 22 '19 at 09:47
Not looking good to me ;) ① [Avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) ② `Dim rowselect As Double` must be `Dim rowselect As Long` — Pᴇʜ, Jan 22 '19 at 10:15
Use the [WorksheetFunction.Match method](https://learn.microsoft.com/en-us/office/vba/api/Excel.WorksheetFunction.Match) to find your serial number that you want to update. `Match` returns the row number that you could use instead of `rowselect` to write your data. — Pᴇʜ, Jan 22 '19 at 10:19

score 0 · Accepted Answer · answered Jan 22 '19 at 10:25

Use the WorksheetFunction.Match method to find your serial number that you want to update. Match returns the row number that you could use instead of rowselect to write your data.

For example something like this:

Dim MySerial As String
MySerial = "MAPR0001" 'adjust to your needs

Dim MyLookupRange As Range
Set MyLookupRange = Sheets("DASHBOARD").Range("A:A") 'adjust to where your serials are

Dim RowToUpdate As Long
On Error Resume Next 'next line throws error if serial not found
RowToUpdate = WorksheetFunction.Match(MySerial, MyLookupRange, 0)
On Error Goto 0 'always re-enable error reporting!

If RowToUpdate > 0 Then
    'serial found, update here eg …
    'Sheets("DASHBOARD").Cells(RowToUpdate, 2) = Me.TextBox1.Value
Else
    MsgBox "Serial " & MySerial & " was not found."
End If

score 0 · Answer 2 · answered Feb 05 '19 at 13:30

There are a few ways to do this. Here is one option for you to try.

'Private Sub Worksheet_Change(ByVal Target As Range)

Sub ImportDataFromExcel()
    Dim rng As Range
    Dim r As Long
    Dim conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        "C:\Users\Ryan\Desktop\Coding\Integrating Access and Excel and SQL Server\Access & Excel & SQL Server\" & _
        "EXCEL AND ACCESS AND SQL SERVER\Excel & Access\Select, Insert, Update & Delete\Northwind.mdb"
    Set conn = New ADODB.Connection
    conn.Open strConn

    With Worksheets("Sheet1")
        lastrow = .Range("A2").End(xlDown).Row
        lastcolumn = .Range("A2").End(xlToRight).Column
        Set rng = .Range(.Cells(lastrow, 1), .Cells(lastrow, lastcolumn))
    End With

        'therow = 1

        For i = 2 To lastrow
            'r = rng.Row
            'If r > 1 Then
                strSQL = "UPDATE PersonInformation SET " & _
                    "FName='" & Worksheets("Sheet1").Range("B" & i).Value & "', " & _
                    "LName='" & Worksheets("Sheet1").Range("C" & i).Value & "', " & _
                    "Address='" & Worksheets("Sheet1").Range("D" & i).Value & "', " & _
                    "Age=" & Worksheets("Sheet1").Range("E" & i).Value & " WHERE " & _
                    "ID=" & Worksheets("Sheet1").Range("A" & i).Value
                conn.Execute strSQL
            'End If
            'r = r + 1
        Next i


    conn.Close
    Set conn = Nothing
End Sub

This is for illustration purposes only. Please change to suit your specific needs.

Update data based on serial number

2 Answers2