R1:C1 format

L

lwm

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
 
M

Mike H

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
 
L

lwm

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 said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top