2 Replies Latest reply: May 15, 2014 10:08 AM by jodes _ RSS

    negatives in excel become (Null)

    jodes _

      I have Monarch Pro 10.5.  When I use Open Database to bring in an Excel file, negative amounts get translated as (Null).  Eg, below, 50,000- becomes (Null) once imported into Monarch.  How do I get these to pull in correctly?

       

      41601          Building-Code enforcement               

      41601-1101   Salaries and Wages     322,200          50,000-

      41601-1131   Salaries- Part-time     71,400          50,000

       

      I have tried formatting the amounts in Excel beforehand as both text and numbers before importing, and it still doesn't work.

       

      Any feedback would be appreciated.

       

      Thanks!

        • negatives in excel become (Null)
          Bill Watson

          I don't have 10.5 but I am presuming it is reading values with the trailing minus as Text in a number field and thus pulling through the values as Null because they do not match the field type. If you can change your negative values to leading minus should solve the problem. Of course version 10 may have an option to do this for you..

           

          The following macro can be used to automate the process of changing trailing minus to leading minus

           

          Sub FORMAT_FIXNEGATIVES()

          Dim Cell As Range, V As Variant

          Application.ScreenUpdating = False

          Application.Calculation = xlCalculationManual

          For Each Cell In ActiveSheet.UsedRange

            With Cell

              V = .Value

              If TypeName(V) = "String" Then

                If Right$(V, 1) = "-" Then

                  If IsNumeric(Left(V, 1)) Then

                    Cell.Value = Left$(V, Len(V) - 1) * -1

                  End If

                End If

              End If

            End With

          Next Cell

          Application.Calculation = xlCalculationAutomatic

          Application.ScreenUpdating = True

          End Sub

          /code