Resizing Column

[austin350s10]

Does anyone know a good formula to use to resize a column based on the number of characters in a cell. For example:

i = Len(test)

g = i (some formula to adjust i)

Columns("X:X").ColumnWidth = round(g,0)

 

[MrKowz]

You could combine all three of those statements into one. What is your formula that adjusts i?

 

[austin350s10]

Thats what I am trying to figure out. It seems as if there is no way to do this cleanly. Example:

If len = 2 g = i * 0.1
If len = 10 g = i * 0.34
If len = 32 g = i * 0.74


Any ideas would be great!

 

[MrKowz]

So basically...

If len = 2 then g = 0.2
if len = 10 then g = 3.4
if len = 3 then g = 23.68

If this is correct, this can be done in one line, I do believe.

 

[MrKowz]

This should give ya a good start:

Columns("X").ColumnWidth = Application.Lookup(Len(test), Array(1, 10, 32), Array(0.2, 3.4, 23.68))

 

[austin350s10]

how so?.....I would love that. I'm been messing around with it all day and have not been able to find a solution.

 

[austin350s10]

Dang that would work....I like that. What if I wanted to test a range for the largest len and then adjust based off that?

Dim rg, c As Range
Dim i As Long
Set rg = Range("Diag1, Diag2, Diag3, Diag4, Diag5, Diag6")
i = 1
For Each c In rg
    If Len(c) > i Then
        i = Len(c)
    End If
Next c
If i >= 22 Then
    [COLOR=Red]Columns("X").ColumnWidth = Application.Lookup(i, Array(1, 10, 32), Array(0.2, 3.4, 23.68))[/COLOR] [COLOR=Red]<=== would this work[/COLOR]
Else
    Columns("X:X").ColumnWidth = 2
End If

 

[MrKowz]

In theory, yes it would work. However you have given my conflicting logic. In your recent VBA example, you only want to adjust x if i is greater than or equal to 22, yet in the example where you gave me three different possibilities, there was only one possibility greater than 22.

We can also consolidate that entire IF block at the end down into the LOOKUP function as well.

Tell me EXACTLY what your bounds are for i.

So far, in your VBA example, I can see:

If I < 22, then width = 2
If I >= 22 and <32, then width = 10
If I >= 32 then width = 23.68

 

[austin350s10]

Well i am going through right now and getting all the widths based on len.

I am using the character "w" to fill my test range.

So far:

If Len = 24 Then Width = 2
If Len = 26 Then Width = 4
If Len = 28 Then Width = 6
If Len = 30 Then Width = 9
If Len = 32 Then Width = 11
If Len = 34 Then Width = 14
If Len = 36 Then Width = 17
.......

 

[MrKowz]

Gotcha, then follow these instructions on how to use the Lookup function in the VBA example I've given.

Here is the syntax for LOOKUP:

LOOKUP(value, array to look in, array to return)

So basically, you have the value already (this is the variable i in your example). So all you need to do is build the array to look in and the array to return.

The array to look in will be the list of possible lengths: 24, 26, 28, 30, 32, 34, 36, etc...

The array to return will be the list of possible widths: 2, 4, 6, 9, 11, 14, 17

So basically, you need to adjust the function to look like:

Columns("X").ColumnWidth = Application.Lookup(i, Array(*LIST OF POSSIBLE LENGTHS*), Array(*LIST OF POSSIBLE WIDTHS*))
Hope this helps in your programming!