How to save column values contains decimal,zero,blank into same datatype

[_nithish (Nithish Dhanabal)]

Hi Forum,
Actually i have a datatable with all coulmn data type as String,
But if i write that value in excel a green triangle appearing for some columns(inbuilt Background check)which has values like decimal(latitude),zero,blanks. I tried to convert the column datatype as Double but due to some blanks i was unable to do, i want blank should be blank, decimal should be decimal ,zero should be zero,
How to convert it into same datatype ?
Also is there any option to disable that green triangle only for that particular workbook not globally, So whenever any user open the workbook on any machine it will not show the green triangle.

NOTE: i tried object,generic datatypes but still green triangle error coming
Kindly provide your suggestion

[ashokkarale]

@_nithish,

You can achieve this with VBA Macro.

Use Invoke VBA activity to tun this code and those triangles will go away.

Sub RemoveGreenTriangles()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        On Error Resume Next ' Continue on error
        ws.Cells.SpecialCells(xlCellTypeConstants).Errors.Item(xlNumberAsText).Ignore = True
        On Error GoTo 0 ' Re-enable error handling
    Next ws
End Sub

Before:

After:

[_nithish (Nithish Dhanabal)]

Hi @ashokkarale , thanks for your reply ,i have tried this script but still it is not removing green triangles in any sheets.

[ashokkarale]

@_nithish,

Try this one. This works but will be time consuming as it’s working cell by cell.

Sub IgnoreNumberStoredAsTextErrors()
    Dim ws As Worksheet
    Dim cell As Range
    
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If cell.PrefixCharacter <> "" Then
                cell.Errors.Item(xlNumberAsText).Ignore = True
            End If
        Next cell
    Next ws
End Sub

[_nithish (Nithish Dhanabal)]

Thanks @ashokkarale , It is working fine,