PDA

View Full Version : [SOLVED] Copy InputBox



ddh
09-13-2005, 11:46 AM
This code works when I put 1 in the InputBox but if I put 2 or 3 in it will stop at SRg = Range(Rg.Item(0), Rg.Item(i).Offset(0, 16)).Address.
Thank you for your help.


Sub PasteRowAX1T()
' PasteRowAX1 Macro
Application.ScreenUpdating = False
Dim Rg As Range
Dim SRg As String
Dim LRg As String
RowIns = InputBox("Enter number of rows required")
For i = 1 To RowIns
Set Rg = Selection
SRg = Range(Rg.Item(0), Rg.Item(i).Offset(0, 16)).Address
Range(SRg).Select
LRg = Range(Rg.Item(0).Offset(1, 16), Rg.Item(i).Offset(0, 16)).Address
Selection.Copy Destination:=Range(SRg & ":" & LRg)
Cells(ActiveCell.Row, 1).Resize(1, 17).Copy _
Destination:=Cells(ActiveCell.Row + 1, 1).Resize(RowIns, 1)
Next i
Cells(ActiveCell.Row + 2, 1).Select
Set Rg = Nothing
Application.ScreenUpdating = True
End Sub

Bob Phillips
09-13-2005, 11:53 AM
Looking briefly at it, that suggests to me that your selection is a single cell. so it doesn't have a 2nd or 3rd item to work on.

ddh
09-13-2005, 11:59 AM
xld - Thank you for your help.
What the code does is copy down the line above it from Column A to Column Q.
It copies down when I put 1 in the InputBox but not when I put in 2 or 3 ?

Bob Phillips
09-13-2005, 12:20 PM
It doesn't work for me at all, with 1, 2 or 3.

The reason is that you reference item(0), and there is no item(0).

This worked, but n copies depending upon the input.



Sub PasteRowAX1T()
' PasteRowAX1 Macro
Application.ScreenUpdating = False
Dim Rg As Range
Dim SRg As String
Dim LRg As String
RowIns = InputBox("Enter number of rows required")
For i = 1 To RowIns
Set Rg = Selection
SRg = Range(Rg.Item(1), Rg.Item(i).Offset(0, 16)).Address
Range(SRg).Select
LRg = Range(Rg.Item(1).Offset(1, 16), Rg.Item(i).Offset(0, 16)).Address
Selection.Copy Destination:=Range(SRg & ":" & LRg)
Cells(ActiveCell.Row, 1).Resize(1, 17).Copy _
Destination:=Cells(ActiveCell.Row + 1, 1).Resize(RowIns, 1)
Next i
Cells(ActiveCell.Row + 2, 1).Select
Set Rg = Nothing
Application.ScreenUpdating = True
End Sub

ddh
09-13-2005, 01:54 PM
xld - Thank you it works perfect.
I just was not smart enough to figure that out about the 1 and 0.
I have a lot of trouble understanding VBA.
I learn more each time I have trouble and I receive help.
Thank you very much for your help.

sheeeng
09-14-2005, 07:26 AM
xld, you are a great helper here...
I learn from you too.
thx.