Helps with Macros

  • Thread starter Thread starter lennymos123
  • Start date Start date
L

lennymos123

I have a datafeed that comes to me everyday i need to format one column
of arounf 1000 rows so it multiples the total column but a 1000 so
getting rid of the decimal point i.e £100.00 becoming 10000 , i also
need to do this using a macro. can anyone help
 
One way of doing this manually is to find an empty helper cell, put 100 in that
helper cell.

Then copy that helper cell.
Select your range to adjust.
edit|paste special|and check the multiply operation.

Then clean up that helper cell.

In code, you could do the same thing like this:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myHelperCell As Range

With ActiveSheet
Set myRng = .Range("c4", .Cells(.Rows.Count, "C").End(xlUp))
Set myHelperCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

With myHelperCell
.Value = 100
.Copy
myRng.PasteSpecial operation:=xlPasteSpecialOperationMultiply
.Clear
End With

End Sub

(I used C4 through the last used cell in column C. Adjust if necessary.)
 
One way for column A

Sub changenums()
Set mr = Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row)
With mr
.Formula = .Formula * 100
.NumberFormat = "0"
End With
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
I have a datafeed that comes to me everyday i need to format one column
of arounf 1000 rows so it multiples the total column but a 1000 so
getting rid of the decimal point i.e £100.00 becoming 10000 , i also
need to do this using a macro. can anyone help
 
Hi Don ive tryed to use your code and having problems with line

..Formula = .Formula * 100

could you help

Regards

Paul
 
Ihave tryed to use both and keep getting errors i used yours again Don
with the 1 value and it works perfectly , is ther a way of adjusting it
to work. The rows that come from the feed every day are always between
800-900 items , so if i just changed the value manually everyday thats
not a problem.
 
Back
Top