Find certain string and offset the cell in filtered data

[orangeballoons]

Hi there, I'm working on an userform and some workbooks. I was nearly done with my work, but I can't get this problem solved even after 2 days

I have a workbook containing around 1600 rows and columns from A to IK. I need to filter a column according to some unique numbers so the excel only show the rows containing the unique numbers I need (I figured this out already). After I get the filtered data, I need to find a header cell named "Cost Center" and get the filtered data in that column. I tried to code this, but it give me the hidden value, not the filtered value.

Here's the code I'm using right now :

Please Login or Register  to view this content.

I filtered the data, so the rows left are, for example, row 1 (the header. always there and have to be there), row 3 and row 75. Then I tried to find the "Cost Center" string in the header, then offset it one row below to get the value from row 3. But the code I'm using gives me row 2's value.

Here's the database workbook and the userform workbook. I filter according to the GGI number.

Any help is appreciated! Thanks

[ajryan88]

Hi,

Try inserting this line of code after the first "rng" declaration:

Please Login or Register  to view this content.

Please note, I have not tested this as I am not at my computer in front of Excel at the moment, but it should at the very least go close to working for you.

If this doesn't work, please let me know and I'll take a look at your workbook next time I'm at my computer.

Thanks, hope this helps

[orangeballoons]

Hi ajryan88! Thanks for your reply. But apparently it's not working, it only gives the value of cells (1,1) and when I changed it to cells (2,2), it still gives me the same value.
What I want to achieve is, if after the filter, the rows left is row 1, 3, 7 and 9, I will need to get each value from those row under the column header "Cost Center" (row 1).

Thanks for replying, though. I think I can use your code somewhere else in my program. ^^

[ajryan88]

Ah yes, I see what I did wrong now. Because the range is (potentially) disjointed due to the hidden rows, the .Cells function won't provide the desired result. Try this instead (in place of the code from my first response):

Please Login or Register  to view this content.

This will give you the value of each VISIBLE cell in the column.

Hope this is better

[orangeballoons]

Thanks again ajryan88 for replying
This code works for giving the visible cells' value but I didn't need all of them. I only need some values in certain columns, and I tried to find the column I need with

Please Login or Register  to view this content.

But your code gives me an idea! I'll try to put the Find inside your For-Next code. But maybe somebody can give me another way to do this? Just in case it doesn't work.

[ajryan88]

Sorry orangeballoons, but to me it already looks like "rng" in your original code already points to the correct column...

[orangeballoons]

it did point to the right column, but the row is wrong. if I offset one row from rng, it will give me the value of row 2, which is hidden by the filter. What I need is the value from row 3, which is the data shown after the filter.

[ajryan88]

Sorry, but I think I'm lost...

If your assignment of "rng" points to the right column, and my For Each...Next loop gives you access to only the visible cells, then what is wrong here?

If you only need some values and not all those that are visible, use an If...End If statement to filter out the ones you don't need...

Hope this helps

If this is still unclear, can you please explain to me how to use your workbooks (as I had absolutely no idea what to do when I opened them) and what you are trying to do, and also the expected result if everything works perfectly. Then I may be able to debug your code to get the result that you are looking for.