Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing from MS Project -- problems with commas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing from MS Project -- problems with commas
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. "David McRitchie" wrote in message ... 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 "Martin" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing into excel PROBLEMS | Excel Worksheet Functions | |||
Importing Alan Beban's code on Arrays; Importing a module or a project | Excel Worksheet Functions | |||
Importing problems | Excel Discussion (Misc queries) | |||
importing list with commas from WORD, to individual .xls cells? | Excel Discussion (Misc queries) | |||
Problems Importing from Access | Excel Discussion (Misc queries) |