VBA - Match formulae to enter data on a pre-existing row using a form entry

Hey all,

Really hoping someone can assist.

Basically I am running a spreadsheet to collate date through a variety of user forms. Now I have the challenge of adding to a pre-existing row using a unique value (in column A)

Specifically I am compiling a note, 25 columns across from a unique reference in my "Master" sheet within column A and then moving along one row at a time to add data. However I'm struggling to get going with this one.

Sheets("Master").Select
Range("A1").Select
If ActiveCell.Value = "" Then
Application.WorksheetFunction.Match(TextBox9.Value, Worksheets(1).Range("A4:A60000"), 0)
ActiveCell.Offset(0, 25).Select
ActiveCell.Value = TextBox4

Any help would be greatly appreciated

Thanks
Michael

Hi Michael,

Any chance we could see a sample workbook. It would make it far easier to understand your need here.

BSB

Hi BSB

I've attached a sample workbook for you, Book1. The idea is, columns A to U have been populated with a macro using a user form already, therefore I have a new user form to collate columns B to Z (In green) and I am inserting data based of the unique filed in column A. Two user forms are required.

Therefore I need help with a match formulae whereby details in, for example text box 4match to a unique ID in column A and I can program the response in the relevant column, x number of spaces along.

Many thanks
Michael

So you have a separate userform with fields that will be used to populate columns V - Z? And you want the row to update to be identified by matching a UID in a field on the same userform against column A?

BSB

Yes, you have it in one

I've added a simple userform to the attached for demonstration purposes. TextBox1 is for the Patient ID. TextBox2 - 5 is for the other details for columns V - Z.

Simple code that sits behind the Submit button on the form is:

Please Login or Register  to view this content.

Should be straightforward enough to adapt to your workbook.
Shout if you need more help.

BSB

Here is another method using OFFSET you may prefer:

Please Login or Register  to view this content.

BSB

Hey BSB

This really did work a treat! Just a quick one with this, if I have a list box, rather than a text box, how can I modify the above string to bring a ListBox in one cell? For example TextBox4 could be ListBox4?

Thanks
Michael

Is the listbox a single selection or multiple
selection?

Which of the fields does the listbox pertain to?

BSB

Morning BSB

Thanks for your assistance, the listbox is a multiple selection, my example didn't;t go into this much detail.
What I have built is two list boxes whereby one contains all the options the second the single or multiple selection. The idea is that the second ListBox contains the results for the PatID in one cell as a summary. Is this something that is possible on your string of code?

Many thanks
Michael

I'm not sure I understand. Could you post a sample workbook with your userform included so we can see exactly what you mean?

BSB

Originally Posted by Mlanger575

The issue I'm faced with is multiple selections which appear in one ListBox, and how to insert these into the right row based on the unique ID.

I assume you mean ListBox2 contents will be submitted to the sheet(?)
If so, in which columns?

There are currently a max of 4 items in the ListBox. Does each have a specific column to go to? i.e. option 1 will go to column A and option 2 will go to column B etc.? OR, would the first option selected go to column A and the second selected go to column B, even if these were options 3 and 4?

BSB

Hi BSB

I really do appreciate your help

The contents of ListBox2 ideally should go into one cell in one column (I'm not fussed which at this point). The cell will contain multiple items rather than a specific items and or source

Thanks
Michael

OK, I've amended the attached to include a multi entry ListBox. The code will loop through the list and if one or more entries are selected it will concatenate them into a string and paste that string into a cell on the relevant row.

Any use?

BSB

Thanks BSB

Amended.

The Macro stalls and halts at the

Please Login or Register  to view this content.

area. I'm really not too sure how to fix this. The macro does not finish and the entry does not post into the cell.

Thanks
Michael