Userform range

[chloedee]

Hi

I have a userform set up on a spread sheet that I want to enter data in range A10:I107. I have other data below this part of the spread sheet and when I enter data from the user form it starts entering it from below that data instead of the first blank cells at the top. Is there a code I can use to do this?

Many thanks

 

[vaskov17]

There definitely is code that can do this but you should post whatever code you are using for the data entry right now and possibly explain how your sheet is set up.

 

[keepexploring]

Hi ,
Make A10 as the active cell .
Use the DO Until Loop to find the next empty cell to enter the data .
This shud resolve ur problem .
Regards ,
Sushil.

 

[chloedee]

Hi

Thank you for your replies. Here is my code at the moment. As I said I only want data to fill down to row 107 then after that I have formulas to copy the data above and make different calculations. The spreadsheet has to be set out in a certain way as it links to accounting software.


Private Sub ACTextBox_Change()

ACTextBox = 6150

End Sub

Private Sub Label2_Click()

End Sub

Private Sub Label5_Click()

End Sub

Private Sub Label6_Click()

End Sub

Private Sub UserForm_Initialize()

'Empty AmountTextBox
AmountTextBox.Value = ""

'Empty JobTextBox
JobTextBox.Value = ""

'Empty VATTextBox
VATTextBox.Value = "0.00"

'Empty CommentsTextBox
CommentsTextBox.Value = ""

'Empty ACTextBox
ACTextBox.Value = "6150"

'Empty CCTextBox
CCTextBox.Value = "ZZZ"

'Empty DeptTextBox
DeptTextBox.Value = "ZZZ"

'Empty ANTextBox
ANTextBox.Value = "SUNDRIES"

'Set Focus on AmountTextBox
AmountTextBox.SetFocus

End Sub

Private Sub OKButton1_Click()

'Make Sheet1 active
Sheet1.Activate


'Determine emptyRow
NextFreeRow = Application.Max(Range("A" & Rows.Count).End(xlUp).Row + 1, 10)


'Transfer information
Cells(NextFreeRow, 4).Value = AmountTextBox.Value
Cells(NextFreeRow, 6).Value = JobTextBox.Value
Cells(NextFreeRow, 9).Value = CommentsTextBox.Value
Cells(NextFreeRow, 1).Value = ACTextBox.Value
Cells(NextFreeRow, 2).Value = CCTextBox.Value
Cells(NextFreeRow, 3).Value = DeptTextBox.Value
Cells(NextFreeRow, 7).Value = ANTextBox.Value
Cells(NextFreeRow, 5).Value = VATTextBox.Value

Unload Me

UserForm1.Show


End Sub

 

[vaskov17]

try this

NextFreeRow = Worksheets("Sheet1").Range("A9:A107").Find(vbNullString).Row

as it is right now, your code finds the empty cell after the last row where you have data and that is obviously not what you need

 

[chloedee]

Works perfectly.

Thank you so much. This has been driving me mad.