Finding the nth non empty cell in a row and returning value to text box vba

Hey everyone,

I have a userform that has 7 fields that are connected to an autofilter. On the click of a button it filters the data. I also have 16 other text boxes that will be used to reference data from the resultant row from the filter.

The following image displays the data prior to the filter.
Notes_Grid1.PNG

This image shows the userform with the autofilter data field in. The combo boxes on the left are linked to the filter. The text boxes on the right are linked to the non-contiguous non blank cells adjacent to column G.

NotesPic.PNG

Also attached is an image showing the filtered data if that helps.

The text box with WC1 as the label will be the column header of the data in the first non blank cell and the data of the first non blank cell will be in the text box below that one. The text box with WC2 as the label will contain the column header of the second non blank cell and the text box below it will contain the data from the 2nd cell with data, and so on.

I've researched several approaches, but they all seem to be using the index function which is not compatible with VBA. If you need the actual file or current code, let me know.

In a way, it would be treated like an Access database and linking the remaining relative data in the row.

Thanks in advance,
Jimalya

jimalya,

Its hard to say without seeing your code, but hopefully you can adapt something like this. Note that the code assumes your textboxes have a naming convention. The 'WC' textboxes that will hold the header are assumed to be named 'txtWC#' where # is 1, 2, ...8, and that the textboxes that will hold the notes are named 'txtWC#Note' where # is again 1, 2, ...8

Please Login or Register  to view this content.

jimalya,

In your userform you have a textbox that display what record the user is currently viewing ('1 of 3', '2 of 3', etc). You also have Previous and Next buttons which either are not yet coded or you did not provide their codes. I have assumed that the textbox displaying what record the user is currently viewing (the 1, 2, etc) is named txtActiveRecord, so you'll need to adjust the code to the correct name for that textbox.

With that adjustment, this should work for you (this is the CommandButton1_Click code):

Please Login or Register  to view this content.

jimalya,

They would be very simple.
btnPrevious:

Please Login or Register  to view this content.

btnNext:

Please Login or Register  to view this content.

You're very welcome