MrExcel Publishing
Your One Stop for Excel Tips & Solutions

importing from a unix database


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

Hi,
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?
Thanks
Jonathan


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


Jonathan,

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.

Regards,
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
Next

Application.ScreenUpdating = True

End Sub