VlookUp in a Textbox Error

[emina002]

I am trying to embed a vlookup formula into a textbox userform, and my columns are almost 300. I used a lookup formula and the returning column value is on Column 242 (Column IG). It works fine at first, but when I add a column in between and delete it afterward, it started to get messed, Instead of returning the value in Column IG, it returns the value from Col IH which is Column 243. I have another textbox that has the same code below but it works fine, just this textbox.

Please Login or Register  to view this content.

I have another option on using the index match formula instead. But I have no idea of how to embed it on a textbox.

[Norie]

How is cmbFrame populated?

[emina002]

@norie cmbframe is a combo box, It has a value already that created through name range. So whenever I choose a value in a combo box, all value in a textbox is auto-populated.

[Norie]

I realise it's a combobox, I'm asking how it's been populated as that might help with your current issue.

For example, you might be able to use the ListIndex property of the combobox to determine which row the currently selected item comes from and in turn use that to get the value for the textbox.

As for adding/deleting/moving columns being a problem you might be able to use Application.Match to find the column number.

For that to work you would need to have some sort of header row.