Userform help!!

[Young_Money]

Hi guys..
I currently have a userform that has buttons. Here is the code for one of the buttons. I want to be able to click the button and have my highlighted data copy and paste into "Sheet2". However, I am getting a run time error "1004"
Application defined or object defined error. Below is my code for that button. When I debug it, it says Set rngPaste = Sheets("Sheet2").Cells(i, 1) is wrong.

Sub CommandButton1_Click()

Dim rngCopy As Range
Dim rngPaste As Range
Dim i As Integer

Set rngCopy = Selection
Set rngPaste = Sheets("Sheet2").Cells(i, 1)
rngCopy.Copy rngPaste

End Sub

Would anybody be able to point out what is wrong please?

 

[AlphaFrog]

The variable i is zero by default because you don't set a value for it. You can't reference Cells(0, 1)

 

[Young_Money]

ohhhhh i see
thanks again alpha frog!

One other question
How come when I use the same code in a sub it works?
like this

Sub Messagebox()


Dim rngCopy As Range
Dim rngPaste As Range

i = msgbox("Format New Orders of Last Week? (Carriers)" & vbCrLf & "You need to highlight the cells you want to format and rerun the program", vbOKCancel)

If i = vbOK Then

Set rngCopy = Selection
Set rngPaste = Sheets("New_Orders").Cells(i, 1)
rngCopy.Copy rngPaste

 

[AlphaFrog]

You set the variable i equal to vbOk. The vb constant vbOk is equal to the value 1. So the variable i equals 1. You can reference Cells(1,1)

The real question is what cell do you want to reference?

 

[Smitty]

One other question
How come when I use the same code in a sub it works?

Because your message box will return integer values. Try Debug.Print i after the MsgBox and see. Although I don't know if you really want to use those for the destination?

HTH,

 

[Young_Money]

thanks for the help guys!
problem solved