Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify when Excel is displaying ### in a cell? (contentswidth)
I am pulling data from an automated report. I'm trying to test for every
possible condition, so that my VBA will continue to work for the rest of the year without additional tweaking. The target report is produced from an aspx web page (which has an "export to Excel" option). I do not have control over the source format. Some data in the exported Excel report is in merged cells. When numbers are increased (for example, to simulate the growth of YTD numbers) the display converts to "######" when the digits exceed the cell width for that font size. When I try to autosize columns by selecting the columns and double-clicking between the column letters (cursor looks like <-|- ) which normally autosizes the columns, nothing happens. I haven't tried to investigate the root cause; right now I'm assuming it is because of the merged cells. I need to make sure that the cell contents are visible, because my code takes ranges of cells and copies them as a picture to paste into a MS Word template, at various bookmark locations. Is there a way in code to either force the autosize in a way that actually works, or to simply determine whether there are any values being displayed as "###" so that I can loop and incrementally increase cell width until the actual value is visible? I hope that the source aspx page is smart enough to adjust the exported column widths to adjust for the cell contents, but I'd rather program conservatively and make sure my VBA handles all possible conditions (at least the ones I can think of). Many thanks, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify when Excel is displaying ### in a cell? (contentswidth)
Select a cell and run:
Sub PoundTester() Dim s1 As String, s2 As String s1 = ActiveCell.Text If Len(s1) = 0 Then Exit Sub s2 = Replace(s1, "#", "") If Len(s2) = 0 Then MsgBox " activecell is displaying pounds" End If End Sub -- Gary''s Student - gsnu201002 "ker_01" wrote: I am pulling data from an automated report. I'm trying to test for every possible condition, so that my VBA will continue to work for the rest of the year without additional tweaking. The target report is produced from an aspx web page (which has an "export to Excel" option). I do not have control over the source format. Some data in the exported Excel report is in merged cells. When numbers are increased (for example, to simulate the growth of YTD numbers) the display converts to "######" when the digits exceed the cell width for that font size. When I try to autosize columns by selecting the columns and double-clicking between the column letters (cursor looks like <-|- ) which normally autosizes the columns, nothing happens. I haven't tried to investigate the root cause; right now I'm assuming it is because of the merged cells. I need to make sure that the cell contents are visible, because my code takes ranges of cells and copies them as a picture to paste into a MS Word template, at various bookmark locations. Is there a way in code to either force the autosize in a way that actually works, or to simply determine whether there are any values being displayed as "###" so that I can loop and incrementally increase cell width until the actual value is visible? I hope that the source aspx page is smart enough to adjust the exported column widths to adjust for the cell contents, but I'd rather program conservatively and make sure my VBA handles all possible conditions (at least the ones I can think of). Many thanks, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying cell contents | Excel Discussion (Misc queries) | |||
Displaying Cell Contents | Excel Discussion (Misc queries) | |||
Cell Contents to identify source Filename | Excel Discussion (Misc queries) | |||
In Excel, how do I identify the contents of a cell as numerical o. | Excel Discussion (Misc queries) | |||
VBA Code to identify contents of a cell | Excel Programming |