• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

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
0
Fordraiders
Asked:
Fordraiders
  • 3
  • 3
1 Solution
 
NorieData ProcessorCommented:
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
 
NorieData ProcessorCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
NorieData ProcessorCommented:
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
 
FordraidersAuthor Commented:
Thank  you very much !
Will get me in the right direction.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now