Importing from MS Project -- problems with commas

[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").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

 

[Martin]

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.

Hi Martin,
Try including another substitution of Chr(160) by ""
since you are trying to get a number, do this before
your other substitutions that are looking for a blank (space character).

CHR(160) is the nonbreaking space character,   in HTML.
In RTF or Word it is possibly referred to a Required Blank, at least
that's what it is in some text formatters (i.e. Script/VS)..

Don't know what you mean by float. I assume the comma is for
tenths. I'd suggest trying the macro TrimALL
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
to see if that fixes your problem for a selection of cells. You can
modify a version for a specific range it that works out.
You would have to include your substitutions to remove " hrs"
whether that be a space or a chr(160),

If will convert text '1.2 to a number 1.2 (yours with commas)

Possibly F2 then enter for each cell solves your problem per
cell, I have macros for that as well, but the TrimALL would handle
what I think your question is, automatically.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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").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