Excel sheet data Cell Format not rendered as expected - range value conversion issue

Question

I have a workbook with a sheet to display a bunch of data. The first line is a header line and each column is associated to a specific format. This sheet is updated automatically (by VBA macro code) from an external data source (file import).

After the data value update, the format is also updated (and applied so taht it applies to new rows). In the code extract following rColumn is the data column range below a header. rColumn start at row 2 while header is at row 1. sType is a type associated with a header :

        On Error GoTo ErrorHandler
        With rColumn
            .Validation.Delete
            Select Case sType
                Case "text"
                    .Value = CStr(.Value)
                    .NumberFormat = "@"
                Case "integer"
                    .Value = CLng(.Value)
                    .NumberFormat = "0"
                Case "float"
                    .Value = CDbl(.Value)
                    .NumberFormat = "0.0"
                Case "percentage"
                    For Each c In rColumn
                    .Value = CDbl(.Value)
                    Next c
                    .NumberFormat = "0.00%"
                Case "Image"
                    .NumberFormat = "@"
                Case "currency euro"
                    .Value = CDbl(.Value)
                    .NumberFormat = "#,##0.00 $"
                Case "currency USD"
                    .Value = CDbl(.Value)
                    .NumberFormat = "#,##0.00 [$USD]"
            End Select
        End With 'rColumn

when I check the cell format it's ok and I can see that the column format was set.

What's weird is that the cells are not rendered as expected. In the example below column 03ATT cur USD is expected to be rendered as a currency for US $ and 04ATT cur eur as a currency for euro ...

If I select a cell, say the one with value 3 in 03ATT cur USD column

and validate it (pressing enter key - note I do not change the value in the cell) then the expected format applies.

I have to mention that before updating the data, GUI and calculation refresh are disabled (to speed the process up) with the following code :

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual

but after the data update and format update they are reset so that the GUI is refreshed :

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True

here is the question why don't i get the proper format rendered when applying the format as show in the block code (upper part of this post). I use Excel 2016 on Win 10.

Considering the comment about this post it appear that the problem consist in converting a data cell value (actually range cells value) from one digital representation to another. The destination representation must be compatible with the source value. Applying a conversion like CStr() CLgn() etc raises error 13 "type mismatch" on the exact line where the conversion occurs. The description which speaks for itself, but I can't figure out what is the issue. For instance a value 9535 (which in did is an integer - actually Long in VBA is better) converted using CLng() miserably fails. What the heck ? I am wondering if the conversion can be applied to a Range Value ?

Does the cell contain actual "Number" data, or "Number formatted as Text"? — Chronocidal, Jun 28 '18 at 09:56
Those values you think are dates and numbers are text-that-looks-like-a-date-or-number. You can see that as soon as you enter the cell and exit it reverts to a true number and accepts the number format. The final proof is that as text, it is left-aligned by default. As soon as it becomes a true number, it is right-aligned as all numbers and dates are by default. — , Jun 28 '18 at 09:57
Use text-to-columns, fixed width, finish to quickly convert text-that-looks-like-a-date-or-number to a real date or number. — , Jun 28 '18 at 09:58
@Chronocidal some value might be formatted as text at some point and copied from one sheet to another then formatted — pascal sautot, Jun 28 '18 at 12:51
@Jeeped why does it appear as text if another format is applied. Should those be converted some how ? — pascal sautot, Jun 28 '18 at 12:52
it seems that this link might put me on right track **https://stackoverflow.com/questions/36771458/vba-convert-text-to-number** but I d like to apply the conversion to a range not to cells otherwise it would be too slow — pascal sautot, Jun 28 '18 at 12:56
There are 'markers' that are carried along, particularly when copying from a web page. I used to cycle through each column in a loop performing t2c, fw, f. — , Jun 28 '18 at 12:58

score 0 · Answer 1 · answered Jul 04 '18 at 09:37

closing the question after digging to find solution it appears the question should be reformulated which I'll do.

By the way I tried to Copy value 1 from some cell and then PasteSpecial with different options. I can get the expected format rendered but this has the drawback to set to 0 (zero) in cells which had no content (null or empty). A zero cell value or an empty cell has a different meaning in the content of my application. Also this solution does not work to render dates.

Excel sheet data Cell Format not rendered as expected - range value conversion issue

1 Answers1