VBA Copy

[BoJaNa]

Hello, I'm in hell because of this stupped thing. I want copy value from textboxes (UserForm) in to a Sheet. Every value from every textbox must be copyed to cell in that sheet on the next empty cell. For example value from first textbox must be copyed into a sheet in a next empty cell on colomn A and so on. I use this code:

Private Sub copy_Click()

Range("A1").End(xlDown).Offset(1, 0).Value = data.Value
Range("B1").End(xlDown).Offset(1, 0).Value = nomer.Value
........

End Sub

It's ok for first time. Second time was copyed only in A colomn.
Please help me!

 

[kpark91]

You could first start by referencing to the object you want to copy into and of.

Sheets("sheet1").Range("A1").End(xlDown).Offset(1,0).Value = Me.data.Value

 

[BoJaNa]

Thank you but this one is exact like mine

If you can please give me more code to try it.

 

[kpark91]

Private Sub copy_Click()

Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1, 0).Value = Me.data.Value
Worksheets("Sheet1").Range("B1").End(xlDown).Offset(1, 0).Value = Me.nomer.Value
'........

End Sub

What do you mean it's exact like yours?
Do you mean it gives you the same result as before?

Is there any error message popping up?

 

[mikerickson]

If you put the column of interest as the tag in each text box.
(i.e. in your example from post#1, data.Tag = "1" and nomer.Tag = "2")
Then you could do all the text boxes at once like.

For each oneControl in UserForm1.Controls
    With oneControl
        If IsNumeric(.Tag) Then
            ThisWorkbook.Sheets("Sheet1").Cells(1, Val(.Tag)).End(xlDown).Offset(1,0).Value = .Text
        End If
    End With
Next oneControl

 

[BoJaNa]

Private Sub copy_Click()

Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1, 0).Value = Me.data.Value
Worksheets("Sheet1").Range("B1").End(xlDown).Offset(1, 0).Value = Me.nomer.Value
'........

End Sub

What do you mean it's exact like yours?
Do you mean it gives you the same result as before?

Is there any error message popping up?

Yes give me the same result. In A column everithing is ok, but in B column copy and paste in one cell.

 

[BoJaNa]

If you put the column of interest as the tag in each text box.
(i.e. in your example from post#1, data.Tag = "1" and nomer.Tag = "2")
Then you could do all the text boxes at once like.

For each oneControl in UserForm1.Controls
    With oneControl
        If IsNumeric(.Tag) Then
            ThisWorkbook.Sheets("Sheet1").Cells(1, Val(.Tag)).End(xlDown).Offset(1,0).Value = .Text
        End If
    End With
Next oneControl

This do nothnig

 

[BoJaNa]

OK, I thing that I need code something like that:

Public Sub save_Click()
Dim c As Range

With Sheets("Opis").Range("B1:B1000")
For Each c In Range
Set c = Range("B1").End(xlDown).Offset(1, 0)
If c.Offset(0, 0).Value = nomer_sd.Value Then
MsgBox "Saved"
Else
MsgBox "Error"
End If
Next c

End With
End Sub

but in this line " For Each c In Range" I have Error "Argument not optional"

If someone can send me correct code would be nice!

 

[mikerickson]

Which range do you want c to loop through?

 

[BoJaNa]

I want c variable to be cell number. Next time c must be c+1 cell number and so on.