Column width based on Heading Value

[Barieq]

hi,

How can I adjust the column width based on cell value which is in row 8. I have the below value in Column A:Last Column. Header name will be the same but column not fixed

Category Emp Code Employee Name Beneficiary Name Bank Account Number SWIFT Code Country Code Net - USD Net - SAR Net - USD


If cell value is "Category" then Column width = 20
If cell value is "Emp Code" then Column width = 8
and so on


Thanks

[bebo021999]

Assume there were 8 columns. s is array of 8 width.

Option Explicit 
Sub test() 
Dim s, i& 
s = Array(20, 8, 10, 11, 12, 13, 14, 15) ' assume there were 8 widths of 8 columns 
    For i = 0 To UBound(s) - 1 
        Cells(1, i + 1).ColumnWidth = s(i) 
    Next 
End Sub  

[Barieq]

Thanks bebo, as I have explained sometimes column header values will be in different columns.

Example if "Country Code" is in Column G, next time it will be in F or some other columns.

[sintek]

If this does not work then only way is to see sample sheet...
Assumes headers in Row 8 and they exist in Array
Add as many headers as required...Change red numbers to header column width requirement...

Please Login or Register  to view this content.

[Barieq]

@sintek,

Thanks, I am getting Type mismatch error in the below line

Please Login or Register  to view this content.

[sintek]

That's obvious...
See post 4
Assumes headers in Row 8 and they exist in Array

Add as many headers as required...Change red numbers to header column width requirement...

You did not follow instructions...
Where is other Headers and their corresponding required column widths...
Bank Account Number SWIFT Code Country Code Net - USD Net - SAR

[Artik]

The following macro will only widen defined columns. If other columns appear, their width will not be changed.

Please Login or Register  to view this content.

Artik

[sintek]

Add like this...By the way your Net Headers have leading spaces...

Please Login or Register  to view this content.

[Barieq]

That's obvious...
See post 4
Assumes headers in Row 8 and they exist in Array


You did not follow instructions...
Where is other Headers and their corresponding required column widths...
Bank Account Number SWIFT Code Country Code Net - USD Net - SAR

Sorry, you are right. I have forgot to add the values in Array.

Thanks again for a excellent solution.

[sintek]

Glad I could contribute...Tx for rep +

[Barieq]

@Artik,

Thanks, Excellent. Its working.

[sintek]

As per Artik ...

If other columns appear, their width will not be changed.

Code amendment...

Please Login or Register  to view this content.

[Barieq]

@ Sintek,

Thanks for the amendment