Please I need help to make this macro smarter-Offset problem


Posted by Julia P. on July 19, 2000 8:39 PM

I have this small macro:

Sub addtest()
Dim myselection As range
Set myselection = Selection
On Error Resume Next
Dim column As range
For Each myc In myselection
myc.Offset(0, -2).Value = myc.Value

Next
End Sub

My selection is Col L so myc.offset will be J. I need to use in Offset formula not
-2 but reference to column J so if myselection is other column than L say AB automaticaly will refere always to Offset Col J.

I tried:

Sub addtest()
Dim myselection As range
Set myselection = Selection
On Error Resume Next
Dim column As range
For Each myc In myselection
myc.Offset(0, column(J)).Value = myc.Value

Next
End Sub

but doesn't work. Thanks



Posted by Ivan Moala on July 19, 0100 9:30 PM

Julia
Here is one way you can do it.

Sub addtest()
Dim myselection As Range

Set myselection = Selection

On Error Resume Next
Dim myc, myrg As Range, x As Integer

Set myrg = Range(Cells(ActiveCell.Row, 10), Cells(ActiveCell.End(xlDown).Row, 10))
x = 1
For Each myc In myselection
myrg.Cells(x).Value = myc.Value
x = x + 1
Next

End Sub

Ivan