userform textbox2 entry to copy paste into worksheet with a match (from textbox1) & offset

[EmXLS]

I am a basic VBA user and require:
userform textbox2 entry to copy paste into worksheet with a match (from textbox1) & offset

I've failed below trying a lookup so feel a match would assist here?

Please Login or Register  to view this content.

Thanks everyone.

[6StringJazzer]

You just have it backwards. You are trying to copy the contents from textbox2 to the lookup value. That's illegal.

If I understand what you want, try this:

Please Login or Register  to view this content.

If I've misunderstood please give a more complete description of what you want to do. Remember we can't see your file or userform. Also you can attach the file to make it clearer.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[jolivanes]

Or maybe

Please Login or Register  to view this content.

[EmXLS]

Thank you both for your prompt replies and support as a new user. I have now attached my file to assist.

My aim is to paste the hand-keyed entry in TextBoxAMEdit, into Sheet1 column J when ID key Sheet1 column A matches TextBoxID

[jolivanes]

Please Login or Register  to view this content.

If I understand your naming right, you need to change following
TextBox1 = TextBoxID
TextBox2 = TextBoxAMEdit
TextBox1 has the data to look for in Column A
TextBox2 has the data that will be placed in Column J in the same Row where the data in Column A was found.

Note: Data entered in TextBox1 has to be identical to the data to be searched for in Column A.
One of the common problems is that sometimes data has leading or trailing spaces which makes it different.

[EmXLS]

Hi jolivanes

I'm just missing how 2 final concepts can be used with the successfull code you provided.
If TextBox2 has a blank/no entry, then dont want to paste the blank, leave the original data in column J.
Want to hide sheet1

Please Login or Register  to view this content.

Thanks for this final assistance.

[jolivanes]

Change TextBox Names as required again

Please Login or Register  to view this content.

[EmXLS]

Thank you so much for the clarity here.
It "sometimes" works.
Two errors can appear;
could not set the value property. type mismatch
or
object variable or With block variable not set

Have uploaded my current file version with all of your coding for reference if needed.
I appreciate your help.

[jolivanes]

Please Login or Register  to view this content.

Why is above in both the code windows of Sheet1 and ShiftPopup (=UserForm)?

What is this suppose to do? I have never seen this before.

Please Login or Register  to view this content.

Your best bet might be to explain exactly what you want to achieve. I find it very difficult to fix code that I can't run

[6StringJazzer]

Please Login or Register  to view this content.

Why is above in both the code windows of Sheet1 and ShiftPopup (=UserForm)?

It will have absolutely no effect in the UserForm code. It looks like a mistake.

What is this suppose to do? I have never seen this before.

Please Login or Register  to view this content.

It creates a string from the value in column A of the active cell's row, the value in row 3 of the active cell's column, and the value in column B of the active cell's row, and separates the three values with the "|" character.

[jolivanes]

Thanks 6StringJazzer
Cute way of doing that. Never too old to learn and see new things.

[EmXLS]

Thank you both 6StringJazzer & jolivanes.

The "|" is just how the business define/concatenate an ID of unique products. I am assuming cute means a strange way (lol)... you may notice that I seem to be applying my excel formula methods to VBA as a newbie. May be looking like strange coding to you, so I appreciate your help so much!
1) have removed the double-up of the double-click
2) error still present: object variable or With block variable not set
3) debug points to---------> .Find

Please Login or Register  to view this content.

You guys are awesome!

[jolivanes]

Is Sheet1 Visible when you run the macro?
Is the exact Text (Value) of TextBoxID in Column A of Sheet1? No extra leading or trailing spaces?

[EmXLS]

The error is present if Sheet1 is hidden or visible.
I have just found the trigger for the error If Sheet1 has filters with selected data, the error appears.

[jolivanes]

OK, you found your problem and you're rectifying it I assume.
I was going to post the following so I will anyway.

If you change the code to this, what is the result?

Please Login or Register  to view this content.

Note: Check references and change where/if required because I ran this on a test book

BTW, this would be my preference but to each his own. Many ways lead to Rome.

Please Login or Register  to view this content.

[EmXLS]

Thank you for this additional code. But, I realise this solution is probably not in the right location of code. Because the userform uses data in Sheet 1, it errors before it can "show" because it can't populate the userform information anyway.
I have pinpointed the exact problem which you raised before: what to do if the exact text is not in Column A? I believe (with my little experience), the solution will be best placed in the double-click function, whereby if the active cell the user is double-clicking is "0" then a message box needs to alert and the userform doesn't even show. This is the last error I hope.

Please Login or Register  to view this content.

This forum and you guys are the best!

[EmXLS]

I'm hoping for assistance on this final step
Please, from anyone