Help with a Macro

[Bob Vance]

--
..Offset(15, 5) "-1"
then the next cell across copies 4 cells up and 1 across

Thanks in advance.........Bob Vance

 

[Don Guillett]

a bit more explanation might help.

 

[Bob Vance]

From a designated cell (AK73)I want to go down 15 cells then across 5 cell
rows and enter (AP88)"-1", then the next cell along(AQ88) in next row, copy
what data is in 4 cells above 1 cell across (AR84), so as the data in AR84
copies in to AQ88, But I want to be able to use this macro any where on my
sheet not relative to theses cells.................Thanks Bob

 

[Bob Vance]

From a designated cell (AK73)I want to go down 15 cells then across 5 cell
rows and enter (AP88)"-1", then the next cell along(AQ88) in next row, copy
what data is in 4 cells above 1 cell across (AR84), so as the data in AR84
copies in to AQ88, Then AR88 becomes the value of AP88*AQ88, But I want to
be able to use this macro any where on my sheet not relative to theses
cells.................Thanks Bob

 

[Steve Garman]

I'm not too sure exactly what you're after but, for the example you
describe, this seems to work:

Sub test()
Dim rng As Range
Set rng = ActiveSheet.Range("AK73")
rng.Offset(15, 5).Value = "-1" 'ap88
Set rng = rng.Offset(15, 6) 'aq88
rng.Value = rng.Offset(-4, 1).Value 'ar84
rng.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub

 

[Steve Garman]

I should, perhaps, have said that you can replace

Set rng = ActiveSheet.Range("AK73")
with
Set rng = ActiveCell

I'm not too sure exactly what you're after but, for the example you
describe, this seems to work:

Sub test()
Dim rng As Range
Set rng = ActiveSheet.Range("AK73")
rng.Offset(15, 5).Value = "-1" 'ap88
Set rng = rng.Offset(15, 6) 'aq88
rng.Value = rng.Offset(-4, 1).Value 'ar84
rng.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub

From a designated cell (AK73)I want to go down 15 cells then across 5
cell
rows and enter (AP88)"-1", then the next cell along(AQ88) in next row,
copy
what data is in 4 cells above 1 cell across (AR84), so as the data in
AR84
copies in to AQ88, Then AR88 becomes the value of AP88*AQ88, But I
want to
be able to use this macro any where on my sheet not relative to theses
cells.................Thanks Bob

 

[Bob Vance]

Brilliant...Steve Thanks

I should, perhaps, have said that you can replace

Set rng = ActiveSheet.Range("AK73")
with
Set rng = ActiveCell

I'm not too sure exactly what you're after but, for the example you
describe, this seems to work:

Sub test()
Dim rng As Range
Set rng = ActiveSheet.Range("AK73")
rng.Offset(15, 5).Value = "-1" 'ap88
Set rng = rng.Offset(15, 6) 'aq88
rng.Value = rng.Offset(-4, 1).Value 'ar84
rng.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub

From a designated cell (AK73)I want to go down 15 cells then across 5
cell
rows and enter (AP88)"-1", then the next cell along(AQ88) in next row,
copy
what data is in 4 cells above 1 cell across (AR84), so as the data in
AR84
copies in to AQ88, Then AR88 becomes the value of AP88*AQ88, But I
want to
be able to use this macro any where on my sheet not relative to theses
cells.................Thanks Bob

 

[Bob Vance]

What would I need for Offset Macro in B1 to copy A1
(-1,0).Copy?

Brilliant...Steve Thanks

I should, perhaps, have said that you can replace

Set rng = ActiveSheet.Range("AK73")
with
Set rng = ActiveCell

I'm not too sure exactly what you're after but, for the example you
describe, this seems to work:

Sub test()
Dim rng As Range
Set rng = ActiveSheet.Range("AK73")
rng.Offset(15, 5).Value = "-1" 'ap88
Set rng = rng.Offset(15, 6) 'aq88
rng.Value = rng.Offset(-4, 1).Value 'ar84
rng.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub


Bob Vance wrote:

From a designated cell (AK73)I want to go down 15 cells then across 5
cell
rows and enter (AP88)"-1", then the next cell along(AQ88) in next row,
copy
what data is in 4 cells above 1 cell across (AR84), so as the data in
AR84
copies in to AQ88, Then AR88 becomes the value of AP88*AQ88, But I
want to
be able to use this macro any where on my sheet not relative to theses
cells.................Thanks Bob

 

[Bob Vance]

Got it Thanks

Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Select
With ActiveCell.Offset(0, -18).Resize(6000, 8)

.Value = .Value
End With
End Sub

What would I need for Offset Macro in B1 to copy A1
(-1,0).Copy?

Brilliant...Steve Thanks

I should, perhaps, have said that you can replace

Set rng = ActiveSheet.Range("AK73")
with
Set rng = ActiveCell


Steve Garman wrote:

I'm not too sure exactly what you're after but, for the example you
describe, this seems to work:

Sub test()
Dim rng As Range
Set rng = ActiveSheet.Range("AK73")
rng.Offset(15, 5).Value = "-1" 'ap88
Set rng = rng.Offset(15, 6) 'aq88
rng.Value = rng.Offset(-4, 1).Value 'ar84
rng.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub


Bob Vance wrote:

From a designated cell (AK73)I want to go down 15 cells then across 5
cell
rows and enter (AP88)"-1", then the next cell along(AQ88) in next row,
copy
what data is in 4 cells above 1 cell across (AR84), so as the data in
AR84
copies in to AQ88, Then AR88 becomes the value of AP88*AQ88, But I
want to
be able to use this macro any where on my sheet not relative to theses
cells.................Thanks Bob