Home What is the VBA formula to copy and paste the Output from a User Form into multiple rows
Reply: 2

What is the VBA formula to copy and paste the Output from a User Form into multiple rows

RCF
1#
RCF Published in 2017-12-07 14:20:47Z

I have created a User Form in Excel that follows the following VBA code:

Private Sub CommandButton1_Click()

If TextBox1.Value = "" Then
    If MsgBox("Form is not complete. Continue?", vbQuestion + vbYesNo) <> vbYes Then
    Exit Sub
    End If
End If

ActiveCell = TextBox1.Value

ActiveCell.Offset(20, 0).Select

Call resetForm

End Sub

Sub resetForm()

TextBox1.Value = ""
UserForm1.TextBox1.SetFocus

End Sub

This VBA code provides me the result generated in the User Form in the active cell of the WorkSheet.

I want to copy and paste this result into 18 rows below and then the active cell should be offset to the row below this cell.

The idea being if a User generates another result through the User Form (command button) will copy and paste this result 18 rows below and then the active cell should be offset to the row below this cell.

Vityata
2#
Vityata Reply to 2017-12-07 14:38:29Z

After ActiveCell = TextBox1.Value write:

ActiveCell.Offset(18, 0) = ActiveCell
ActiveCell.Offset(1, 0).Activate

The first line generates value 18 rows below the active cell and the second line moves the activecell with one row, thus exactly I want to copy and paste this result into 18 rows below and then the active cell should be offset to the row below this cell.

FunThomas
3#
FunThomas Reply to 2017-12-07 15:35:59Z

Try

Const NumberOfRows = 18
' ...
Range(ActiveCell, ActiveCell.Offset(NumberOfRows - 1, 0)) = TextBox1.Value
ActiveCell.Offset(NumberOfRows, 0).Activate
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310139 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO