R1:C1 format
 

Hi all

I am trying to make a small modification to this recorded macro. The macro
works fine as is. The problem is that we want to replace
Selection.AutoFill Destination:=Range("H2:H2000")

with the ability to dynamically change the range. In other words sometimes
it will be 100 rows and sometime it will be 15000 rows or 50 rows.

X= 25

i.e.
Selection.AutoFill Destination:=Range(RC!: RC +X)

Thanks for the help


Sub CREDITS_NEGATIVE()
' CREDITS_NEGATIVE Macro

' Macro recorded 8/16/2007
'
Dim x As Integer
x = 25

'
Selection.AutoFilter

Columns("H:H").Select

Selection.Insert Shift:=xlToRight

Range("H2").Select

ActiveCell.FormulaR1C1 = "=IF(RC[1]=""c"",-RC[-1],RC[-1])"

Range("H2").Select

Selection.AutoFill Destination:=Range("H2:H2000")

Range("H2:H2000").Select

Calculate

Selection.Copy

Range("G2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Columns("H:H").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Range("H10").Select

End Sub

R1:C1 format
 

Hi,

In principal not difficult but how is it decided what the size of the fill
range is. For example if it were to the same row as the adjacent column G

lastrow = Cells(Rows.Count, "G").End(xlUp).Row
would find the last used row so the fill range could be

Selection.AutoFill Destination:=Range("H2:H" & Lastrow)

Mike
"lwm" wrote:

Hi all

I am trying to make a small modification to this recorded macro. The macro
works fine as is. The problem is that we want to replace
Selection.AutoFill Destination:=Range("H2:H2000")

with the ability to dynamically change the range. In other words sometimes
it will be 100 rows and sometime it will be 15000 rows or 50 rows.

X= 25

i.e.
Selection.AutoFill Destination:=Range(RC!: RC +X)

Thanks for the help


Sub CREDITS_NEGATIVE()
' CREDITS_NEGATIVE Macro

' Macro recorded 8/16/2007
'
Dim x As Integer
x = 25

'
Selection.AutoFilter

Columns("H:H").Select

Selection.Insert Shift:=xlToRight

Range("H2").Select

ActiveCell.FormulaR1C1 = "=IF(RC[1]=""c"",-RC[-1],RC[-1])"

Range("H2").Select

Selection.AutoFill Destination:=Range("H2:H2000")

Range("H2:H2000").Select

Calculate

Selection.Copy

Range("G2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Columns("H:H").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Range("H10").Select

End Sub

R1:C1 format
 

Mike

Thanks

I did not have the las row in there because I was working without actual data.

The H" & Lastrow) was exactly what I neede.

Thanks again

"Mike H" wrote:

Hi,

In principal not difficult but how is it decided what the size of the fill
range is. For example if it were to the same row as the adjacent column G

lastrow = Cells(Rows.Count, "G").End(xlUp).Row
would find the last used row so the fill range could be

Selection.AutoFill Destination:=Range("H2:H" & Lastrow)

Mike
"lwm" wrote:

Hi all

I am trying to make a small modification to this recorded macro. The macro
works fine as is. The problem is that we want to replace
Selection.AutoFill Destination:=Range("H2:H2000")

with the ability to dynamically change the range. In other words sometimes
it will be 100 rows and sometime it will be 15000 rows or 50 rows.

X= 25

i.e.
Selection.AutoFill Destination:=Range(RC!: RC +X)

Thanks for the help


Sub CREDITS_NEGATIVE()
' CREDITS_NEGATIVE Macro

' Macro recorded 8/16/2007
'
Dim x As Integer
x = 25

'
Selection.AutoFilter

Columns("H:H").Select

Selection.Insert Shift:=xlToRight

Range("H2").Select

ActiveCell.FormulaR1C1 = "=IF(RC[1]=""c"",-RC[-1],RC[-1])"

Range("H2").Select

Selection.AutoFill Destination:=Range("H2:H2000")

Range("H2:H2000").Select

Calculate

Selection.Copy

Range("G2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Columns("H:H").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

Range("H10").Select

End Sub