lookup column, Value match and add to sheet

[Hazel56]

Hi All.

I have a form on the attached. For userform1, I need some guidance on the code.

- data has been entered in most fields (Mandatory) naming textbox1, combobox1, Textbox3 and textbox4.
- I've got 2 additional in textbox5 and textbox6. A unique number will be entered in them. Only a minimum of one of these txtbox needs to be filled up eg. 1 out of 2.
- press submit (cmdsubmit) button
- require a code to search through sheets namely Sheet2 and Sheet3 which has a column called Unique number. If the unique number matches
- for Sheet2 need to input the name in column F, ID in Column G, Colour in Column H and Dep in column I
- for sheet3 need to input the name in column F

If the trigger event for start activates the userform, the code should overwrite these details, when the about routine is called.

I've put up the samples in the file.

[mehmetcik]

Please Login or Register  to view this content.

[Hazel56]

Hi mehmetcik

I have a few questions if I may ask to understand the code provided.

1. Will this section which i have wrapped below differentiate the sheets that it needs to look into the find the unique number? As not all sheets will have the unique number column and in some sheets the unique number column is in diferent column numbers. Eg. sheet2 is in J and sheet3 is in H
2. Can i understand why we need to count up to 16 in this case? or it like set as a default

Please Login or Register  to view this content.

I pasted the code on to my form with the formulas that i have for cmd buttons to do reset and return and i got an error message. Able to advice by looking at the form I pasted in which section I had done it incorrectly to correct the flow of the procedures. I can compare and understand how it needs to be correctly done.

Hazel

[mehmetcik]

1. I used your sample.

We are looking at Sheet2 and Sheet3 So:

Please Login or Register  to view this content.

Looks at sheets 2 and 3


I may have made a mistake on the second part

In your example Sheet 2 column 10 was the column containing your unique ID So 14 - 2* 2 = Column 10 = J and 14 - 3*2 = Column 8 = H

Try

Please Login or Register  to view this content.

[Hazel56]

Hi mehmetcik

It worked. Thank you. I understood the count part and how it works for this excersixe. Can i check a few further questions as I was implementing the code to understand it clearer

1. Currently we are cycling through sheet2 and sheet3. For the code that u had provided, if i the sheet name is changed to Sheet 4 and sheet 5 instead, which sections of this code should i amend?
2. If i were to add a new sheet6, on top of sheet4 and sheet5 which has similar functions to perform, on which section of the code should i amend?

3. Can i also clarify what does this section of the code does?

Please Login or Register  to view this content.

[mehmetcik]

Please Login or Register  to view this content.

[Hazel56]

Hi Mehmetcik. Thank you for the detailed explanation. This really helps me to understand the if this happens what it does and if it is done by another similar scenario how the code is amended to work. These are the questions that usually come up to me.

I have a few more questions here.

In the attached file, I added a sheet4, to perform the same function to search through the unique number from sheets 2 to sheet 4 I changed the count as
For Count = 2 to 4. It didn’t work on the new sheet. There are 2 other sections on the code which has the count function. Should I change them from 3 to 4 as well.

I noticed that when the unique value is not found, upon entry it submit in a new line of entry which doesn’t have the unique number. Instead how do I say to prompt a message box to “check the unique number entered in textbox4” or “check the unique number entered in textbox5”. I can add a label on top of the textbox to differentiate. Similarly how can I make the form display a textbox message “Data transferred” upon successfully transferring the data as well.

The second thing for example I entered the unique number in textbox4 only and left Textbox5 empty.
‘Entering number 25880 in textbox4 which is found in sheet 2. The current code transfers the data in sheet 2 correctly which in row 3. However it also inputs the name in Sheet 3 for unique number 55557 which is incorrect. Can I understand why the TX does that?

Currently the unique value on sheet 2 is located in column J. On sheet 3 it is located in column H. Which section of this code specifically tells this information to count the column. At the same the data transfer is different for both files. For example sheet 2 is transferring the TX array to return at “F4:I4” and sheet 3 is returning the data at F4 alone. Which section in the code here is specifically telling that to do?

Please Login or Register  to view this content.

The database that is being created here as almost a thousand rows in each sheet. Can I understand will the creating an array like TX to transfer the data saves calculation time?

[mehmetcik]

Hi


First. I would add Data Validation in cell B3 and change your sheet specific code to:

Please Login or Register  to view this content.

Please see the amended workbook attached.

Then I would change this area of code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Hazel56]

Hi Mehmetcik,

Thank you very much for the 3 different variations. I read and understood how it works differently by testing them out. I think the area where I missed out was indicating the column formula in sheet 4 at the bottom of the table. It would be correct to say sheet 2 is treated different as from sheet 3 and sheet 4. So it would be as the code given for the first variation.

- However I realised that when I type a unique number in both textbox 4 and textbox 5 in form1, the data only transfer what I typed in textbox 4 not in textbox 5.

- When the unique number is not found in the column array, I realised a new line is created in sheet 2-4. I would rather a message box popped out to say that the number is not found for field 1/field 2 (As applicable) and does not submit the entry.

- On sheet 1, can I learn how to input a message box after I press the submit button to say data entered, clears the fields and bring cursor back to textbox1.

Can I understand what does this section of the code does? I have a thought that it may have to do with something on the data validation of the events.

Please Login or Register  to view this content.

[mehmetcik]

Please Login or Register  to view this content.

The code mimics manually typing Alt and Down

Data Validation Types

XLDVTYPE ENUMERATION (EXCEL)

7 xlValidateCustom Data is validated using an arbitrary formula.
4 xlValidateDate Date values.
2 xlValidateDecimal Numeric values.
0 xlValidateInputOnly Validate only when user changes the value.
3 xlValidateList Value must be present in a specified list.
6 xlValidateTextLength Length of text.
5 xlValidateTime Time values.
1 xlValidateWholeNumber Whole numeric values.


I have updated your code a bit for you.

However I find this code to be very messy:

Please Login or Register  to view this content.

Try:

Please Login or Register  to view this content.

[Hazel56]

Hi Mehmetcik,

When I tried the attach file, and keyed in unique ID located in Column J (Sheet2), Column H (Sheet3) and Column H (Sheet4) the error message prompted as Unique ID not found though the numbers are in the column. Can assist to advice.

[mehmetcik]

I see no attachement

[Hazel56]

I see no attachement

Hi mehmetcik. Sry. I meant the attachment that u have attached for me. The testbook4.

[Hazel56]

Hi

Great learning on the data validation part. I can use this code for my lists. For sheet1 with the cell using the data validation list, I did not see the drop down list when I selected cell B3. It was supposed to show the 3 types of event. Need some advice here.

For the userform2, I did some tests with the testbook4 and realized that there could have been a miscomm because of the word “ID” in the form which was in textbox2. Previously it was looking up the ID in textbox2. I have amended the code as needed to Textbox4. I’ll need the advice to include the unique ID indicated in textbox5 too as shown in the screenshot in the file to search and input.

One more thing that happened when I was running the userform was before I transferred the data the first time the combolist array showed but after I transferred the data, the combolist didn’t show. Had to type in manually.

Please Login or Register  to view this content.

[mehmetcik]

1. There was no Data Validation in B3. I inserted Data validation using List: "Start, End, Item"

2. You could have just changed the positions of textbox 2 and textbox 4. Then the code would not need to change. [ However tabbing sequence would be off ].

3. I see you are now using two ID numbers. I take it that you want to run the Macro for each ID in turn.

4. Try this code:

Please Login or Register  to view this content.

[Hazel56]

Hi mehmetcik. Thank you for the explanation. It works prefect. Can i check after i submit once, the array below does not show not show on the second entry. I had to retype manually. Able to let me know how do i continue to get the array to be shown for the second entry.

Please Login or Register  to view this content.

[mehmetcik]

Change the Clear Sub to:

Please Login or Register  to view this content.

[Hazel56]

Thanks You very much