| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Data in Specific way
On 3 Nov, 11:30, Joel wrote:
I added some comments to code below and copied formula instead of value. Sub SortColumns() With Sheets("Sheet1") * *'copy columns A - c from sheet 1 to sheet 2 * *.Columns("$A:$C").Copy _ * * * Destination:=Sheets("Sheet2").Columns("$A") * *'row.count is Last row on worksheet * *'Search Up column A from Last Row until data is found * *LastRow = .Range("A" & Rows.Count).End(xlUp).Row * *NewRow = LastRow + 1 * *'Move column D,E and then column F,G * *For ColCount = 4 To 6 Step 2 * * * RowCount = 1 * * * 'Loop until no data is found in column D and F (Value of ColCount) * * * Do While .Cells(RowCount, ColCount) < "" * * * * *'Get Data from Sheet1 * * * * *Key = .Cells(RowCount, ColCount) * * * * *MyFormula = .Cells(RowCount, ColCount + 1).Formula * * * * *With Sheets("Sheet2") * * * * * * 'check if Key is in column A * * * * * * Set c = .Columns("A").Find( _ * * * * * * * *what:=Key, _ * * * * * * * *LookIn:=xlValues, _ * * * * * * * *lookat:=xlWhole) * * * * * * 'check if Key is found. *Will be nothing if not found * * * * * * If c Is Nothing Then * * * * * * * *'If column F check if data is in column D * * * * * * * *If ColCount = 6 Then * * * * * * * * * 'Look for Key in Column D * * * * * * * * * Set c = .Columns("D").Find( _ * * * * * * * * * * *what:=Key, _ * * * * * * * * * * *LookIn:=xlValues, _ * * * * * * * * * * *lookat:=xlWhole) * * * * * * * * * 'check if Key is found. *Will be nothing if not found * * * * * * * * * If c Is Nothing Then * * * * * * * * * * *'Write Key and fomula to sheet 2 (Col D,E or F,G) in New row * * * * * * * * * * *.Cells(NewRow, ColCount) = Key * * * * * * * * * * *.Cells(NewRow, ColCount + 1).Formula = MyFormula * * * * * * * * * * *'Increment Row to put data on Sheet2 * * * * * * * * * * *NewRow = NewRow + 1 * * * * * * * * * Else * * * * * * * * * * *'Write Key and fomula to sheet 2 (Col D,E or F,G) * * * * * * * * * * *'Write data to row where key was found in FIND function above * * * * * * * * * * *.Cells(c.Row, ColCount) = Key * * * * * * * * * * *.Cells(c.Row, ColCount + 1).Formual = MyFormula * * * * * * * * * End If * * * * * * * *Else * * * * * * * * * 'Write Key and fomula to sheet 2 (Col D,E or F,G) in New row * * * * * * * * * .Cells(NewRow, ColCount) = Key * * * * * * * * * .Cells(NewRow, ColCount + 1).Formula = MyFormula * * * * * * * * * NewRow = NewRow + 1 * * * * * * * *End If * * * * * * Else * * * * * * * *'Key was found in Column A * * * * * * * *'Write Key and fomula to sheet 2 (Col D,E or F,G) * * * * * * * *'Write data to row where key was found in FIND function above * * * * * * * *.Cells(c.Row, ColCount) = Key * * * * * * * *.Cells(c.Row, ColCount + 1).Formula = MyFormula * * * * * * End If * * * * *End With * * * * *RowCount = RowCount + 1 * * * Loop * *Next ColCount End With End Sub "K" wrote: Thanks lot Joel your code works perfectly fine. I just have two questions that i got formulas like (=1000-200) etc in column E and G. Is it possible that when macro copy data to Sheet 2 it should keep formulas in these columns instead of just values. *Like if i have formula in Sheet 1 column E cell which is (=1000-200) so when macro copy data to Sheet 2 it should keep this formula in column E as (=1000-200) instead of putting value (800). I know you can only see the result in cell but when you select that cell you can see its formula aswell in above formula bar so thats why i need formulas to be in cell. *And second question is that i really liked the way you solved my problem is it possilbe for you explain me bit more that how your code is doing its work, *just for my knowledge. *Thanks again for your help- Hide quoted text - - Show quoted text - Thanks lot joel |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HOW DO I SORT DATA USING A SPECIFIC CHARACTER IN A CELL | Excel Discussion (Misc queries) | |||
| HOw do i find and sort out data repeats for specific data set | Excel Discussion (Misc queries) | |||
| Data Sort Macro for specific cells | Excel Programming | |||
| sort data by specific word in cell? | Excel Discussion (Misc queries) | |||
| How can I find and sort specific data within a column? | Excel Discussion (Misc queries) | |||
![K[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
