Home |
Search |
Today's Posts |
#1
|
|||
|
|||
text to number graph
hello all,
i cam here with a little problem of mine: i have a series of letters that are linked to a time but i want to to translate this data into a column graph. my data looks like this: 7.10 BM 7.72 DM 10.61 AM 11.01 CM 17.33 AM 18.44 CM 22.72 CI 27.35 AM 32.22 CM 34.39 CM 35.02 AI 41.56 AM 47.01 CM 48.62 CI 64.04 CM 66.35 BI 71.16 CM 76.43 CM 80.54 DI 84.55 BM 92.20 AI and this gos on for a while but my idea was to make a macro for every result alone (A,B,C and D) and form a line on which vertically the time is shown and horizontally is shown if its an M or an I , so in my line of thinking i need to get excel to change every M into exsample a 1 and every I into a 2 , so i get a line on which i have bigcolums(I) and small colums(M) and that for every number separate(a,b,c and D) if not possible they can all mix together... so thats my idea and i've been trying everything i know i can do with excel but i cant figure it out can someone help me? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text to number graph
Hi
This will change your M's into 1's, and your I's into 2's. Sub ChangeI() Dim RowNum As Integer RowNum = 2 Range("B2").Select Do Replacement = ActiveCell.Value Range("B" & RowNum).Select Selection.Replace What:="I", Replacement:=2, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False RowNum = RowNum + 1 Range("B" & RowNum).Select Loop Until ActiveCell.Value = "" End Sub Sub ChangeM() Dim RowNum As Integer RowNum = 2 Range("B2").Select Do Replacement = ActiveCell.Value Range("B" & RowNum).Select Selection.Replace What:="M", Replacement:=1, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False RowNum = RowNum + 1 Range("B" & RowNum).Select Loop Until ActiveCell.Value = "" End Sub .................................................. ............... As for your Graph, I wasn't sure as to what style of vraoh your were wanting, so I played around with a Pivot table that does the following: Now, I have used 00.00 to represent blank cells as it give a better alignment below. A1 00.00 00.00 10.61 00.00 17.33 00.00 00.00 27.35 A2 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 B1 07.10 00.00 00.00 00.00 00.00 00.00 00.00 00.00 B2 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 C1 00.00 00.00 00.00 11.01 00.00 18.44 00.00 00.00 C2 00.00 00.00 00.00 00.00 00.00 00.00 22.72 00.00 D1 00.00 07.72 00.00 00.00 00.00 00.00 00.00 00.00 D2 00.00 00.00 00.00 00.00 00.00 00.00 00.00 00.00 If this is kin of what you are looking for, I have a Recorded Macro below, which does the job and creates the above on a new sheet. One of the other aptly qualified contributors may have a neater option for you. HTH Mick. Sub PivotMyData() Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _ "Sheet1!R1C1:R22C2", Version:=xlPivotTableVersion14).CreatePivotTable _ TableDestination:="Sheet6!R3C1", TableName:="PivotTable3", DefaultVersion _ :=xlPivotTableVersion14 Sheets("Sheet6").Select Cells(3, 1).Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables("PivotTable3").PivotFields ("YourCode") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable3").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable3").PivotFields("YourVal"), "Sum of YourVal", xlSum With ActiveSheet.PivotTables("PivotTable3").PivotFields ("YourVal") .Orientation = xlColumnField .Position = 1 End With Range("A1").Select End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text to number graph
Apart from needing to proof read things before I flippantly hit the send
button, I also neglected to mention that you will have to alter the ranges to suit yours in order for it to run properly. Cheers Mick. |
#4
|
|||
|
|||
Well this is pretty Munch what i was looking for thanks!
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text to number graph
Anytime
The feedback is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Graph while ignoring text but not changing text to #NA | Charts and Charting in Excel | |||
How do I set up a graph to show a number of different criteria? | Excel Discussion (Misc queries) | |||
page number on graph | Charts and Charting in Excel | |||
Graph: number of curves | Charts and Charting in Excel | |||
Select number of rows in a graph? | Excel Programming |