stuck with Auto column fit

[vimalg]

i want to search ######## values in a sheet. if found then i have to apply
auto column fit. (## values have appeared coz the width of the column is
smaller than the value)these cells actually containg value but showing ###
coz of width.. need a vba code for this

url:http://www.ureader.com/gp/1037-1.aspx

 

[JLGWhiz]

This is from VBA help file:

Example
This example changes the width of columns A through I on Sheet1 to achieve
the best fit.

Worksheets("Sheet1").Columns("A:I").AutoFit

This example changes the width of columns A through E on Sheet1 to achieve
the best fit, based only on the contents of cells A1:E1.

Worksheets("Sheet1").Range("A1:E1").Columns.AutoFit

 

[Per Jessen]

Hi,

Maybe this is what you need:

Sub Macro1()
Dim col As Range
For Each col In ActiveSheet.UsedRange.Columns
col.EntireColumn.AutoFit
If col.ColumnWidth < 8.43 Then
col.ColumnWidth = 8.43
End If
Next
End Sub

 

[Charles Williams]

If you want find the ##### values in VBA you have to look at Range.Text
which gives you the formatted values.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[vicky]

hey i know about auto column fit.. but my only constraint is of
identifying cells which has ### values. i guess it has something to do
with range.text or range.formula .... if anyone could provide a
snippet of "identifying" such cells would be really helpful. i have
written this code but not doing the task i am expecting .

For Each cell In ThisWorkbook.Sheets(1).UsedRange
If IsError(cell) Then
cell.Select
Selection.Columns.AutoFit
cell.Interior.ColorIndex = 8
End If
Next cell

 

[vicky]

hey i got it . range.text solves my problem. thanks a lot

 

[Dave Peterson]

First, here's a post that I saved.

It could mean a few things.

1. The columnwidth is too narrow to show the number.

Widen the column or change the font size of that cell. Or change the
numberformat to General.

2. You have a date/time in that cell and it's negative

Don't use negative dates. If excel was helping you, it may have
changed the format to a date. Change it back to General (or some
other number format).

If you need to see negative date/times:
Tools|options|Calculation Tab|and check 1904 date system
(but this can cause trouble--watch what happens to your dates
and watch what happens when you copy|paste dates to a different
workbook that doesn't use this setting)

3. You have a lot of text in the cell, the cell is formatted as Text.

Format the cell as general.

4. You really have ###'s in that cell.

Clean up that cell.

5. You have # in a cell, but it's format is set to Fill.

Change the format
(format|cells|alignment tab|horizontal box, change it to General.

============

This code will try to fix problems 1 and 3. If you have others, then the
program will continue to find the offending cell, fail to fix it and end up in
an endless loop.

So you may want to add more checks to this code...


Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim FoundCell As Range
Dim FirstAddress As String

Set wks = ActiveSheet

With wks
Set FoundCell = .Cells.Find(what:="#", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If FoundCell Is Nothing Then
MsgBox "None found!"
Exit Sub
End If

FirstAddress = FoundCell.Address

Do

If Replace(FoundCell.Text, "#", "") = "" Then
If Application.IsNumber(FoundCell.Value2) Then
FoundCell.EntireColumn.AutoFit
Else
If FoundCell.NumberFormat = "@" Then
If Len(FoundCell.Value) > 255 _
And Len(FoundCell.Value) < 1025 Then
FoundCell.NumberFormat = "General"
End If
End If
End If
End If

Set FoundCell = .Cells.FindNext(after:=FoundCell)

If FoundCell Is Nothing Then
Exit Sub
End If

If FoundCell.Address = FirstAddress Then
Exit Sub
End If

Loop
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Rick Rothstein]

Here is some alternate code for you to consider (it does *not* iterate
through **every** cell in the worksheet)...

Dim X As Long, CW As Double
......
......
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For X = 1 To .UsedRange.Count
CW = .Columns(X).ColumnWidth
.Columns(X).AutoFit
If .Columns(X).ColumnWidth < CW Then .Columns(X).ColumnWidth = CW
Next
End With
Application.ScreenUpdating = True

This code will expand only those columns where the entry doesn't fit. This,
of course, covers your cases where the cell is displaying # signs. However,
it does have one "side effect" that you may not want to occur. If you have
an entry in cell like the number 123456789, but the column is not wide
enough to display the all of the digits making up the number, then Excel
will convert the displayed value to E-notation. For example, the value
123456789 might be displayed as 1.2E+08 or as 1.23E+08 all depending on the
width of the column. The "side effect" for the code above is that it will
autofit the column so all the digits are displayed.