I have a userform and I want one of the text boxes to be populatated using vlookup

[Tig]

I have tried a few different codes and still can't get the vlookup to work. The code is not all my own work, some was suggested by "Sixthsense" and it works.

Please Login or Register  to view this content.

However this always references cell F3 and I want it to reference F4 and F5 as each entry is made into the spreadsheet using the userform.

I have also tried

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I get either a #N/A or FALSE displayed.

Any ideas?

Regards

Tig

[davesexcel]

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Tig]

Hi,

Its a user form with an 'OK' button to transfer the data into the spreadsheet so therefore there would not be an opportunity to press CTRL+SHIFT+ENTER.

What am I missing?

[Kaper]

the syntax of second and third line shown is wrong.
you cannot achieve multiple assignment in one line of code

try writing in a cell

Please Login or Register  to view this content.

if A1<>A2 you will get TRUE as result

excel first evaluates A1=A2 (gets false) and then evaluates false=false, which is true - and this is final result.

so ... my vision ;-) of what you try to obtain in second line of your post is:

Please Login or Register  to view this content.

In simple words: first look for content of SapPartTextBox in Sheets2 column A, and copy the value from coresponding row in column B into textbox2 and also to current sheet in Cells(emptyRow, 7).

[Tig]

Hi Kaper,

I see what you mean, but each time I put the code you suggest into my code it throws up an error.

Regards

Tig

[Kaper]

1) what error? (number and description,
2) where it occurs - press debyg and see yellow line
3) what is in SapPartTextBox.Value
and ... many others so probably best advice is:
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[davesexcel]

Hi,

Its a user form with an 'OK' button to transfer the data into the spreadsheet so therefore there would not be an opportunity to press CTRL+SHIFT+ENTER.

What am I missing?

Hmm... I was going to ask you to supply a sample workbook, not tell you about array formula.

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tig]

Hi Kapur,

I have attached the workbook.

I have desensitised the data.

The error is Run-time error '9'
Subscript out of range

The line where the error occurs is the line

Please Login or Register  to view this content.

The data in the SapPartTextBox would be number between 1 and 14, this should use a vlookup to determine the colour in the 'TextBox2'.

I have filled in the 1st 3 lines to give an idea of what the filled sheet would look like.

Regards

Tig

[Tig]

Sorry,

davesexcel, I put the wrong name at the top of the sheet.

Please accept my apology for that.

Regards

Tig

[davesexcel]

=DEC2HEX(3563)
has found the answer for ya

[Debraj Roy]

Try something like this..

Please Login or Register  to view this content.

as in a user form, each frame contain different group.. you need to call them using their name..

[Tig]

To all the users who commented on this post and supplied answers, Thankyou. I am relatively new to Vba and so all your code has been a learning curve for me.

Regards


Tig