Help with a Macro

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

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

Thanks in advance.........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
 
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
 
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
 
I should, perhaps, have said that you can replace

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


Steve said:
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 said:
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
Click to expand...
Click to expand...
 
Brilliant...Steve Thanks


Steve Garman said:
I should, perhaps, have said that you can replace

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


Steve said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
What would I need for Offset Macro in B1 to copy A1
(-1,0).Copy?

Bob Vance said:
Brilliant...Steve Thanks


Steve Garman said:
I should, perhaps, have said that you can replace

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


Steve said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
Bob Vance said:
What would I need for Offset Macro in B1 to copy A1
(-1,0).Copy?

Bob Vance said:
Brilliant...Steve Thanks


Steve Garman said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top