| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count/sum by function/macro to get the number of record to do copy/paste in macro
dear all,
how to do the count and sum automatically in macro? Branch Cust Def Bank Acc No Amount 14213 0091 000188 11218900112373 50.00 14213 0091 000188 15402200005114 120.25 14213 0091 000188 16218400030391 95.00 14213 0091 000188 11218900096440 56.60 14213 0091 000188 11105200044172 30.50 The problem is like amount I need to have function to convert to text using this =TEXT(E2*100,"000000000000000") later followed by paste special to value. I want to create in macro so will be automated the task. Currently my macro is like this as I dun know how to count the total record before I do the copy and paste special. Can help me how to use function/macro to count and sum and using this data to accurately copy and paste special the correct number of record. Sub Macro() ' Range("M2").Select ActiveCell.FormulaR1C1 = "=TEXT(RC[-8]*100,""000000000000000"")" Range("M2").Select Selection.AutoFill Destination:=Range("M2:M50"), Type:=xlFillDefault Range("M2:M50").Select ActiveWindow.ScrollRow = 22 ActiveWindow.ScrollRow = 20 ActiveWindow.ScrollRow = 18 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 15 ActiveWindow.ScrollRow = 14 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Selection.Copy Range("N2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("O16").Select ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 15 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 18 ActiveWindow.ScrollRow = 19 ActiveWindow.ScrollRow = 20 ActiveWindow.ScrollRow = 22 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 24 ActiveWindow.ScrollRow = 25 ActiveWindow.ScrollRow = 26 ActiveWindow.ScrollRow = 27 ActiveWindow.ScrollRow = 28 ActiveWindow.ScrollRow = 29 ActiveWindow.ScrollRow = 31 ActiveWindow.ScrollRow = 32 ActiveWindow.ScrollRow = 33 ActiveWindow.ScrollRow = 34 ActiveWindow.ScrollRow = 35 ActiveWindow.ScrollRow = 36 ActiveWindow.ScrollRow = 35 ActiveWindow.ScrollRow = 33 ActiveWindow.ScrollRow = 32 ActiveWindow.ScrollRow = 31 ActiveWindow.ScrollRow = 29 ActiveWindow.ScrollRow = 28 ActiveWindow.ScrollRow = 27 ActiveWindow.ScrollRow = 25 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 22 ActiveWindow.ScrollRow = 19 ActiveWindow.ScrollRow = 18 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 15 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to count/sum by function/macro to get the number of record to do copy/paste in macro
Sub Macro()
Dim cLines as Long Dim myRng as Range cLines = Cells(Rows.Count,"M").End(xlUp).Row Set myRng = Range("M2:M" & cLines) With Range("M2") .FormulaR1C1 = "=TEXT(RC[-8]*100,""000000000000000"")" .AutoFill Destination:=myRng,Type:=xlFillDefault myRng.Copy myRng.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks :=False, Transpose:=False -- HTH RP "tango" wrote in message om... dear all, how to do the count and sum automatically in macro? Branch Cust Def Bank Acc No Amount 14213 0091 000188 11218900112373 50.00 14213 0091 000188 15402200005114 120.25 14213 0091 000188 16218400030391 95.00 14213 0091 000188 11218900096440 56.60 14213 0091 000188 11105200044172 30.50 The problem is like amount I need to have function to convert to text using this =TEXT(E2*100,"000000000000000") later followed by paste special to value. I want to create in macro so will be automated the task. Currently my macro is like this as I dun know how to count the total record before I do the copy and paste special. Can help me how to use function/macro to count and sum and using this data to accurately copy and paste special the correct number of record. Sub Macro() ' Range("M2").Select ActiveCell.FormulaR1C1 = "=TEXT(RC[-8]*100,""000000000000000"")" Range("M2").Select Selection.AutoFill Destination:=Range("M2:M50"), Type:=xlFillDefault Range("M2:M50").Select ActiveWindow.ScrollRow = 22 ActiveWindow.ScrollRow = 20 ActiveWindow.ScrollRow = 18 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 15 ActiveWindow.ScrollRow = 14 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 Selection.Copy Range("N2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("O16").Select ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 15 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 18 ActiveWindow.ScrollRow = 19 ActiveWindow.ScrollRow = 20 ActiveWindow.ScrollRow = 22 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 24 ActiveWindow.ScrollRow = 25 ActiveWindow.ScrollRow = 26 ActiveWindow.ScrollRow = 27 ActiveWindow.ScrollRow = 28 ActiveWindow.ScrollRow = 29 ActiveWindow.ScrollRow = 31 ActiveWindow.ScrollRow = 32 ActiveWindow.ScrollRow = 33 ActiveWindow.ScrollRow = 34 ActiveWindow.ScrollRow = 35 ActiveWindow.ScrollRow = 36 ActiveWindow.ScrollRow = 35 ActiveWindow.ScrollRow = 33 ActiveWindow.ScrollRow = 32 ActiveWindow.ScrollRow = 31 ActiveWindow.ScrollRow = 29 ActiveWindow.ScrollRow = 28 ActiveWindow.ScrollRow = 27 ActiveWindow.ScrollRow = 25 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 22 ActiveWindow.ScrollRow = 19 ActiveWindow.ScrollRow = 18 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 15 ActiveWindow.ScrollRow = 13 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 8 ActiveWindow.ScrollRow = 6 ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HELP with macro for copy and paste | Excel Discussion (Misc queries) | |||
| Solver Record Macro Function | Excel Programming | |||
| Macro to Copy/Paste then Paste to Next Line | Excel Programming | |||
| need a function (not macro with ontime) to record a snapshot of a changing cell | Excel Programming | |||
| Macro Won't Copy/Paste | Excel Programming | |||
Linear Mode
