I am a new self-taught coder looking for some help on a project. I have a worksheet that has several columns and rows of data which change often through the day. I am wanting to get a Userform so that a user can input a value and it will search a worksheet and return the column header value(i.e column name) AND the data in the cell just to the right of the found value. I have it working so that it can pull up the data to the right cell but can't seem to figure out how to have it also return the value in the column header. Both returns should come back to separate textboxes. Can anyone help me out on this please? Here is a screenshot of the code I am using and the worksheet it references.
Help with Userform return values
- Sam261
- Thread is marked as Resolved.
-
-
-
You need to attach an example of your workbook
-
Sorry about that. Here it is. Thank you for the help.
-
I just re-read my original post and I want to clarify that the columns won't change, just the data in the rows. I appreciate any help someone can offer.
-
I'll take a look later.
-
Is this what you mean?
Code
Display MorePrivate Sub CommandButton1_Click() Dim Inp, Outp Dim Rng As Range Inp = TextBox1.Value With Sheets("Completed").Range("C:C,G:G,K:K,O:O,S:S") Set Rng = .Find(What:=Inp, after:=.Cells(.Rows.Count, 1), lookin:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Outp = Rng.Offset(0, 1).Value MsgBox Rng.End(xlUp).Value TextBox3.Value = Outp End If End With End Sub -
That worked perfectly!! Thank you so much. I had been working on this problem for several days.
Would you be willing to solve the only other problem I am having with this? I am trying to figure out how to have data deleted from the "Received" worksheet as soon as it appears on the "Completed" worksheet. Is there a code that will allow a search to run along with the codes when the any of the subs are run on Module 2?
-
Do you want delete the whole row?
-
This is where it gets complicated. So if a match is found in column B then delete that row in columns B-E, if found in column G then delete that row in columns G-J, and if found in column L then delete that row in columns L-O.
Basically the data in column B, G, and L are raw data's which through a series of formulas gets "trimmed" down into formatted data as shown in columns E,J, and O. I was unsuccessful in combining the "trimming" formulas so I had to spread them out in 3 steps(columns).
-
You would probably have to search both worksheets
-
Thank you so much for all the help. This is going to save so much time. You are amazing!
-
So if a match is found, search the second sheet the use resize to delete the cells and shift up. The Macro Recorderwill help ypu get the code but it will need amending. Post back if you need further help.
-
I am still having trouble. So what I would like to do is include a step in the module that controls the "Outgoing Sample Log" area of the sheet that deletes the cells with matching data on the "Received" sheet. For example, when data is typed into the K4:K36 cells and the button "Manual" is clicked it will copy those values onto the Completed sheet. I would like to add that step of deleting them from the Received sheet at the same time. Is there a code line that could be inserted to do that?
-
I'm not sure exactly what you mean, but I do think your problems are mainly caused by the way the workbook i s designed.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!