Importing from MS Project -- problems with commas

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

I am importing information from MS Project to Excel due to some legacy
code. This information has to be modified a bit to work as planned in
Excel, and this "trimming" is done by the macro submitted in the end
of this message. This "trimming" of the imported information works
fine for integers, but none of the cells containing floats (e.g. 15,2)
is summed up like it should in the bottom of the sheet.

The mystery is that if I open one the cells containing a float and do
nothing the total sum picks up the value. So to get the correct result
I have to manually open all cells containing float in order to get the
right result. So the question becomes: How can I get this into my
macro?

Sub BC_TrimNumbers()
I = ActiveCell.CurrentRegion.Rows.Count + 8
sTmp = "=Sheet1!R10C1:R" & I & "C7"
ActiveWorkbook.Names.Add _
Name:="BC_XXXTmp", _
RefersToR1C1:=sTmp
ActiveCell.Offset(0, 2).Columns("A:D").EntireColumn.Select
Selection.Replace _
What:=" hrs", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False
Selection.Replace _
What:=" hr", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False

Application.Goto Reference:="BC_XXXTmp"
End Sub
 
Thanks for your reply!

I think my question was somewhat blurry, but the algorithm you
suggested gave me the clue I needed. Making some assumptions (no
numbers above 9 999, and only numbers in the selection) the solution
became:

Dim c As Range
Dim temp As Double

For Each c In Selection
If c.Value Like "* *" Then
c.Value = Trim(c.Value)
c.Value = Left(c.Value, 1) + Right(c.Value, 3)
End If
If c.Value Like "*,*" Then
c.NumberFormat = "General"
temp = c.Value
c.Value = temp
End If
Next
ActiveWorkbook.RefreshAll

This seems to work just fine for me.
 
Back
Top