VBA code for dynamically selecting last active column

[DSP Varma]

Hi ,

I want a VBA code for dynamically selecting entire last active column.

Actual problem:-

I use the following index match formula

=IFERROR(INDEX('PLB - 7JY2 and L9AI'!BN:BN,MATCH('Summary Airlinewise'!$B:$B,'PLB - 7JY2 and L9AI'!$A:$A,0)),0)

Column BN:BN is the last active column and it may change from day to day. My VBA code always selects the BN column as it is not dynamic. I always want the last active column to be my return value. Can somebody help me to solve this?

 

[Hui]

Do you want a VBA solution or a Formula Solution ?

It always selects column BN as that is what you have asked it for in your formula

can you explain what you are trying to do as I suspect you want the last row with data in it based on Column BN?

Also the first element in the Index function requires a value not a range of values
=IFERROR(INDEX('PLB - 7JY2 and L9AI'!BN:BN,MATCH('Summary Airlinewise'!$B2,'PLB - 7JY2 and L9AI'!$A:$A,0)),0)

As always a sample file and better description will get you a more targeted answer

 

[DSP Varma]

Do you want a VBA solution or a Formula Solution ?

It always selects column BN as that is what you have asked it for in your formula

can you explain what you are trying to do as I suspect you want the last row with data in it based on Column BN?

Also the first element in the Index function requires a value not a range of values
=IFERROR(INDEX('PLB - 7JY2 and L9AI'!BN:BN,MATCH('Summary Airlinewise'!$B2,'PLB - 7JY2 and L9AI'!$A:$A,0)),0)

As always a sample file and better description will get you a more targeted answer

Dear Hui, Thanks for your reply.
Actually I want a VBA code for that one. If you can get me a formula as well for this it would be great.

I am actually uploading a file for your reference.
In EG summary Airlinewise Tab, Column C:C I want a formula.

I used the following formula thinking that there would Data in all the 31 days of August

=IFERROR(INDEX('PLB - T20I'!$BN:$BN,MATCH('EG Summary Airlinewise'!$B:$B,'PLB - T20I'!$A:$A,0)),0)

There is a data only for 2 days (4th and 29th Aug)

So my formula should be

=IFERROR(INDEX('PLB - T20I'!$H:$H,MATCH('EG Summary Airlinewise'!$B:$B,'PLB - T20I'!$A:$A,0)),0)

The Column reference will change based on the days in which there is data.

Can I get dynamic code for this one?