[timotz]

i made this program some time ago. it finds all numerics in an excel program and changes their formatting and it works ok.

this is the meat of the code:

Dim bound0 As Integer = array.GetUpperBound(0)
                    Dim bound1 As Integer = array.GetUpperBound(1)
                    

                    For j As Integer = 1 To bound0
                        For x As Integer = 1 To bound1
                            
                            If IsNumeric(array(j, x)) Then
                                Dim cell As String
                                cell = Chr(Asc(x) + 16) & Asc(Chr(j))
                                r = sheet.Range(cell)
                                r.NumberFormat = "#,##0.00"
                                array(j, x) = FormatNumber(array(j, x), 2)
                                array(j, x) = String.Format("{0:F}", array(j, x))
                                r = sheet.UsedRange
                                
                            End If

                        Next

                    Next

                End If
                r = sheet.UsedRange
                r.Value = array

my problem is, there are some alphanumeric values where i need to change the formatting of the number part of the cell example:

"Test Value: 123456.639"
that i need to change to:
"Test Value: 123,456.64"

the array(,) is declared this way:

Dim array(,) As Object = r.Value(XlRangeValueDataType.xlRangeValueDefault)

so what i thought is use something like this:

If array(j, x).ToString().Contains("Test Value: ") Then
'remove the Test Value: part and format the remaining
End If

but it gives me an "Object reference not set to an instance of an object." error
is there another way to solve my problem? thanks in advance

[DimitriV]

There mustn't be objects at that postition. Does the code you have return a value at all (the 2nd code snippet from the bottom). Does array(,) have a value? Try testing this.

This post has been edited by DimitriV: 03 May 2012 - 08:02 PM

[timotz]

thanks for your reply DimitriV(as always) but i have found a way to do it (crudely)

Dim temp(array.GetUpperBound(0), array.GetUpperBound(1)) As String
                    For j As Long = 1 To array.GetUpperBound(0)
                        For x As Long = 1 To array.GetUpperBound(1)
                            If array(j, x) <> Nothing Then
                                temp(j, x) = array(j, x).ToString
                            End If
                        Next
                    Next

and this code somewhere inside the for loops

If Not IsNothing(temp(j, x)) Then
     If temp(j, x).Contains("Test Value: ") Then
        temp(j, x) = temp(j, x).Replace("Test Value: ", "")
        Dim temp2 As Double
        temp2 = CDbl(temp(j, x))
        array(j, x) = "'" & "Test Value: " & temp2.ToString("#,##0.00")
      End If
End If

[DimitriV]

Cool!