ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   how to count/sum by function/macro to get the number of record to do copy/paste in macro (https://www.excelbanter.com/excel-programming/313623-how-count-sum-function-macro-get-number-record-do-copy-paste-macro.html)

tango
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

Bob Phillips[_6_]
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


All times are GMT +1. The time now is 09:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com