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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
|
Similar Threads
