Using Visual Basic to make an excel database - inserting data in existing row.

Question

Good Afternoon / Evening folks,

I have an excel / VB query I was looking for help with. I have been constructing an excel database, and have the following requirements.

a. One form for a user to fill out information, and insert into a blank excel row.

b. One form for a user to fill out, and insert information into blank fields but on an already existing row.

I have achieved "a" with the help of a tutorial from contextures.com (http://www.contextures.com/xlUserForm01.html), but am struggling with my second requirement.

The form itself is no problem, but I am having issues finding out how to do the following.

Insert the data from form "b" into the row where the value in column A equals a figure given in form b.

To further explain this, let me give an example. Form A is used when a stock item is purchased. Information such as purchase price and stock number are given, this is inserted into a blank row.

When an item is sold, form b is used. The user inserts the stock number into the form, and the other data such as sale price and sale date is inserted into a blank cell further along the row from the existing information filled in with form A.

The tutorial for finding the next blank row has given the following VB code to look it up:

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

Is there a way to alter this, or any other means of inserting data from form b into a row where column x == value y?

Best Regards

Answer 1

You need to scan the entire column for finding the matching value something like

For li = firstRow To ws.Range("A1").End(xlUp).Row

   If ws.Cells(li, 1).Value = frmb.Textbox1.Text Then

        'Now that you have found the row number  populate required cells in this row

        ' If matching values are unique then use Exit For here otherwise let the loop scan the entire row range

    End If

Next

---

Best regards

Answer 2

When it comes to programming there are many ways of ending up with the same results. As a programmer we need to decide which method is faster, more reliable, easy for maintenance, robust and so forth , this decision of course directly relates to our programming knowledge , prior experience , the scope of the project ,…

You can of course use the “find” function , but I prefer my method because it’s more flexible , for example if you have more than one matching record you can still use it or if the records are sorted based on the ID value you can improve its speed greatly…  

>This Returns the row number of the text box "ID", and displays it in the text box "Description". Whilst this is far from >what I eventually need, it does open a lot of further experimentation up to me. Is the above code something you would >consider acceptable, or would the code you posted above work much better?

When you have the matching row number the rest is straight forward

ws.Cells(JRow,somecolumn).value=something

---

Best regards

Answer 3

Thank you very much thus far.

I have been fiddling around tonight, and have come up with the following:

With Worksheets("Database").Range("A1: A65535")

jRow = .Find(Me.ID.Value, LookIn:=xlValues).Row

End With

Me.Description.Value = jRow

This Returns the row number of the text box "ID", and displays it in the text box "Description". Whilst this is far from what I eventually need, it does open a lot of further experimentation up to me. Is the above code something you would consider acceptable, or would the code you posted above work much better?