filling in worksheet from userform options

excel 2010 vba

userform
textbox1
textbox2
cmdButton

What I have:
I have a userform that contains  a cmdButton1
2 textboxes

What I need:
I need to copy values down ROWS ON A  a worksheet determined by a numeric value in the textbox1
Along with text from textbox2.

If i know my cell to start the posting say  "K4"
So in  Textbox1 - I type in "10"
I want to copy the following values for 10 rows from.  L4 (because L4 is adjacent column to K4)
Textbox2   - I type in "DO NOT CROSS THIS ITEM"
I press command button

L4 TO L14 WILL CONTAIN  "DO NOT CROSS THIS ITEM"

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Where should the entries start?

At the next empty row in column L?

Also, what will go in column K?

This code will put the value from TextBox2 in column L from the next empty row down to the no of rows in TextBox1.
If IsNumeric(TextBox1.Value) Then
    Range("K" & Rows.Count).End(xlUp).Offset(1).Resize(TextBox1.Value).Value = TextBox2.Value
End If

Open in new window

0
FordraidersAuthor Commented:
Imnorie,

Thanks
Q1.what will go in column K?
 "NODATA"  will go in Column K

Q2. Where should the entries start?
I will need to add another textbox(3) for starting cell location?
so If i type in  textbox(3) the start the posting say  "K4"


Q3. At the next empty row in column L?
yes

Thanks
fordraiders
0
NorieVBA ExpertCommented:
Q1 That shouldn't be a problem.
If IsNumeric(TextBox1.Value) Then
    With Range("L" & Rows.Count).End(xlUp).Offset(1).Resize(TextBox1.Value)
        .Value = "NODATA"
        .Offset(,1).Value = TextBox2.Value
    End With
End If

Open in new window


Q2 I'm not sure I understand, or perhaps I didn't ask the right question.

Once you've put data to L4:K14 as in your example, where should the next set of data go?

Should it overwrite the existing data, or should it be appended starting in row 15, ie the next empty row.

Q3 See above.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

FordraidersAuthor Commented:
Overwrite, the existing data..
But i think you have  answered my questions with the code presented.
You got me on the right track.

I will test it..
0
NorieVBA ExpertCommented:
To overwrite use this.
If IsNumeric(TextBox1.Value) Then

    ' clear existing data first
    Range("L4", Range("K" & Rows.Count).End(xlUp)).ClearContents

    With Range("L4").Resize(TextBox1.Value)
        .Value = "NODATA"
        .Offset(,1).Value = TextBox2.Value
    End With

End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thank  you very much !
Will get me in the right direction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.