MrExcel Publishing
Your One Stop for Excel Tips & Solutions

importing from a unix database

Posted by Jonathan on December 12, 2001 3:13 AM

When I import figures into excel from a unix database using a macro, negative figures are expressed as 50- which is not interpreted by excel. How can I create a macro which automatically corrects this?

Posted by Dank on December 12, 2001 3:50 AM


This code assumes your numbers are in column 3, starting a row 2. It's not as quick as it could possibly be but it works fine.

Dan (sat in W1 also)

Sub CorrectFigures()
Dim rngeUsed As Range, rngeCl As Range

Application.ScreenUpdating = False 'Turn off screen updating to speed up macro

'Change this line accordingly. Refers to column 3 and that the figures start
'in row 2 i.e. assumes you have a column header.
Set rngeUsed = Range(Cells(2, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3))

For Each rngeCl In rngeUsed.Cells
If Right(rngeCl, 1) = "-" Then rngeCl = Val(Left(rngeCl, Len(rngeCl) - 1)) * -1

Application.ScreenUpdating = True

End Sub