macro upgrade

[Sam]

i have this piece of code....

it gets the seeen columns of data from column A and pastes it into column K...
the length of data in column A changes , so this macro can do that ...

here it is ..

Sub select_data()

Sheets("sheet1").Select
Range("k1:k500").Select
Selection.ClearContents

Dim ulhc As String 'upper left hand corner
Dim lrhc As String 'lower right hand corner
Dim wdth As Integer 'true width in columns

ulhc = "$a$6"
wdth = 1



Application.Goto Range(ulhc) 'goto upper left hand corner
ActiveCell.Offset(0, wdth - 1).Select 'move to edge of data (column)
lrhc = Selection.End(xlDown).Address 'get address of lrhc

Range(ulhc & ":" & lrhc).Select
Selection.Copy


Sheets("sheet1").Select
Range("$k$1").Select
' Range("$ad$6").Select
'ActiveCell.Select
ActiveSheet.Paste
Application.CutCopyMode = False


End Sub


now what i want to do is this .....

to this macro i want to a piece of code that will take the data from column K
and add to that 0.01%

can some one help me with the code pls.....

sam

 

[Jan Karel Pieterse]

Hi,

First of all, there is no need to select things to work
with them.

This is one way to do what you want:

Sub select_data()
Dim ulhc As String 'upper left hand corner
Dim wdth As Integer 'true width in columns
Dim oRange As Range
Dim ocell As Range
ulhc = "$a$6"
wdth = 1
Sheets("sheet1").Range("k1:k500").ClearContents
With ActiveSheet
.Range(.Range(ulhc), _
.Range(ulhc).Offset(0, wdth - 1).End(xlDown)).Copy
_
Destination:=Sheets("sheet1").Range("$k$1")
Application.CutCopyMode = False
End With

With Sheets("sheet1")
Set oRange = .Range(.Range("$k$1"), .Range
("$k$1").End(xlDown).Offset(, wdth - 1))
End With

For Each ocell In oRange.Cells
ocell = ocell * 1.01
Next
End Sub




Regards,

Jan Karel Pieterse
Excel TA/MVP