Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to analyze data?
ooo,i see..
thanks for that,James you are really helpful;-) -- Regards, Linda "Zone" wrote: Hi Linda, This code is intended to run on 2 sheets. I wouldn't try to run it one sheet or you'll surely run into problems. James "linda" wrote in message ... James, where i need to change my code if i want to place the raw data and the analyze table in the same worksheet?assuming that i have 1 worksheet.raw data in column O and P,while the analyzed data will be place start from cell A1.i change your code as below and it run ok,but then the raw data was also deleted.how to avoid that?cause i want the raw data to be there. Sub CustModTable() Dim FromRow As Long, FromCol As Integer, c As Range, c2 As Range Dim ToRow As Long, ToCol As Integer, This As Variant, This2 As Variant Worksheets(1).Activate ' Range("A1:G30").Select Cells.ClearContents ToRow = 2: ToCol = 1: FromCol = 15 With Worksheets(1) For FromRow = 1 To .Cells(1, "O").End(xlDown).Row This = .Cells(FromRow, FromCol) Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then Cells(ToRow, ToCol) = This ToRow = ToRow + 1 End If Next FromRow [a1] = "Cust" ToRow = 1: ToCol = 2: FromCol = 16 For FromRow = 1 To .Cells(1, "P").End(xlDown).Row This = .Cells(FromRow, FromCol) Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then Cells(ToRow, ToCol) = This ToCol = ToCol + 1 End If Next FromRow For ToRow = 2 To Cells(1, "a").End(xlDown).Row For ToCol = 2 To Cells(1, 1).End(xlToRight).Column Cells(ToRow, ToCol) = 0 Next ToCol Next ToRow For FromRow = 1 To .Cells(1, "P").End(xlDown).Row This = .Cells(FromRow, "O") This2 = .Cells(FromRow, "P") Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole) Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1 Next FromRow End With End Sub thanks=) -- Regards, Linda "Zone" wrote: Hi Linda, Just happened to run across your post. What is the error message? When the code chokes, rest the mouse over the word This and see what it is. James "linda" wrote in message ... Hi James! its me again,Linda=) i dont know what happen but i cannot reuse your code anymore.the 1 that already in use run perfectly but if i want to do it again,the code doesn't run till end. the error highlighted on this line: Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) can u help me if i'am wrong somewhere? thanks in advanced~ -- Regards, Linda "Zone" wrote: Glad it worked for you, Linda. Cheers, James "linda" wrote in message ... million of thanks,James!its work! thank you very much;-) -- Regards, Linda "Zone" wrote: Linda, yes, I see the problem. Change the last part of the code like this: For FromRow = 1 To .Cells(1, "b").End(xlDown).Row This = .Cells(FromRow, "a") This2 = .Cells(FromRow, "b") If This < "" And This2 < "" Then Set c = Cells.Find(This, LookIn:=xlValues, lookat:=xlWhole) Set c2 = Cells.Find(This2, LookIn:=xlValues, lookat:=xlWhole) Cells(c.Row, c2.Column) = Cells(c.Row, c2.Column) + 1 End If Next FromRow End With End Sub This is a rather crude patch but it does seem to fix the problem. James "linda" wrote in message ... its true i need to use the if statement: =IF(Sheet1!A1<"",Sheet1!A1,"") even the cell contain null value,but then deep inside its actually keep a function.therefore,when i run CustModTable,the cell contain null value return '1'. cust mod1 mod2 mod3 1 1 1 1 1 1 1 1 1 1 1 1 1 c1 2 c2 3 c3 2 1 i dont know why its just happen to mod List even both column cust & mod actually contain function. -- Regards, Linda "Zone" wrote: Hi Linda, If I understand correctly, the problem is that =Sheet1!A1 returns a 0 if Sheet1!A1 is empty. To avoid this, use =IF(Sheet1!A1<"",Sheet1!A1,"") James "linda" wrote in message ... Hi James! i just discover a problem using the copy function from your last post.my 2 columns,cust & mod,contain that copy function because i got the data from other worksheet before it is been analyze. therefore,when i run macro CusModTable,its include the cell without value since the cell still contain formula(=Sheet1!A1).do you have any idea to solve this? thanks! -- Regards, Linda "Zone" wrote: Glad it works for you, Linda. As a matter of curiosity, how many Custs and Mods did you have? It should accommodate any reasonable number of these. On your question about copying columns, if you want the copy to automatically update everytime the original changes, you'll want to use references instead of the actual values. Say your original data begins in cell A1 of Sheet1 and you want your copy to begin in cell A1 of Sheet3. In A1 of Sheet3, type in =Sheet1!A1 then drag down the column as far as needed. In B1 of Sheet3, type in =Sheet1!B1 and drag down the column as far as needed. I don't see why you would need a macro for this unless the length of the columns is changing when you refresh the data. If so, post back and we'll tackle that. Cheers! James "linda" wrote in message ... Wow,its really amazing!Great job!Your help is really appreciated,Thanks James=) can i ask you some more?how to copy data to another worksheet using macro? i have a worksheet that contains all the raw data[column A-O] that i get from database.i need to copy column B & O to another worksheet and from there the data will be analyze and display it in a chart. the raw data will be change everytime i update it and so it will update copied data in the second worksheet and thus update the chart.can it be done? -- Regards, Linda "Zone" wrote: Hi Linda, This macro assumes that: 1. The original table begins in cell A1 of the first worksheet and has at least one blank row after it and at least one blank column to the right of it. 2. The workbook has a second worksheet available, and the new table will be the only thing on the second worksheet. If these assumptions are correct, 1. Open the workbook in Excel 2. Show the code editor by pressing Alt-F11 3. If the workbook doesn't have a regular code module, insert one by clicking Insert on the menubar, then Module. 4. Copy the code below and paste it in the module. 5. Press Alt-F11 to return to the spreadsheet. 6. To run the macro, click Tools on the menubar, then Macro, then Macros, and select CustModTable from the list, then Run. Post back and let me know if the code works for you. If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
who to analyze data with more than 30,000 rows in excel | Excel Discussion (Misc queries) | |||
Analyze Data | Excel Worksheet Functions | |||
How do I statistically analyze data across multipe worksheets? | New Users to Excel | |||
Analyze & Filter data in a notepad file | Excel Discussion (Misc queries) | |||
How do I analyze data from a survey in excel | Excel Worksheet Functions |