macro upgrade

  • Thread starter Thread starter Sam
  • Start date Start date
S

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
 
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
 
Back
Top