[Guest2]

I have a user form with two combo boxes, an add button and a close button. I would like the users choices from the combo boxes to be added to the worksheet "ReqM" starting with the first blank row on or after row 85, the 2nd choice on row 86 and so on until the users clicks the close button. The following code currently adds the data to the last blank row in the worksheet.

I appreciate any help:

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Req M")

'Need help with this section:

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
lPart = Me.cboEmployee.ListIndex

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboEmployee.Value

.Cells(lRow, 3).Value = Me.cboPosition.Value
End With

'clear the data
Me.cboEmployee.Value = ""
Me.cboPosition.Value = ""
Me.cboEmployee.SetFocus

End Sub

[Ziggy1]

what are you saying? That initially when it first runs, row 85 is empty and that the first empty row ( or cell) is further up? Looking at the code I would say you want Row 85 to get filled in first, and then after that it would automatically fill in.

So I would wrap an IF around that portion to get it started.


I'll just use Column 1 as a reference...

if Cells(85,1)= "" then

lRow = 85

else

''find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


end if

[Guest2]

Not Exactly...I know the code is incorrect as written. Currently it returns the data to the first empty row in the spreadsheet (which is somewhere around row 145).

The user will choose an employee from the first combo box and their position/title from the second combo box. They then will click the add button. I would like the result to be displayed on row 85. They then have the option of choosing additional employees/positions or closing the form. I would like their second choice to appear on line 86, the 3rd choice to appear on line 87 and so on.

[Ziggy1]

Use this example,

I have to run so I could not fully adapt to your example as I was focused on getting the loop working in sync.

Public Sub test()

Static Y As Double
Dim X As Double

X = 85

    '
    If Cells(X, 1) = "" Then
        
        Y = 85
    
    End If


        Do Until Cells(X, 1) = ""
        X = X + 1
        Loop



    If Y = 0 Then
    
    
    ' Put your Add items here
        Cells(X, 1) = "TEST"
        Y = X + 1
    
    Else
            ' repeat here because the first one is only on the first pass (could call a sub)
        ' Put your Add items here
        Cells(X, 1) = "TEST"
        Y = Y + 1
    
    End If



End Sub

[Guest2]

I can't get this quite right...the first choice is being returned to line 87 and any subsequent choice is as well, overriding the first...

[Ziggy1]

if you take exactly what I have, and just run it, it starts on line 85 and carries on?

post your adapted code and I will look.

[Guest2]

I didn't save when i tried it earlier. My original code is above. Can you suggest where to insert?

[Ziggy1]

ok here ya go, I put it together for you.

[Guest2]

Thanks for trying

[Ziggy1]

so that's a... No it doesn't work? You would get better result if you posted a sample what your data looks like ( no personal info).

I think my example does exactly what you asked?

[Guest2]

Your example works perfectly. When i entered into my workbook i received an error message. I think it may be because i want the data to be entered on a different worksheet??? I'm just not familiar enough to work it out. On to a different project for a week or so then i may be able to pick it up again. Thanks for taking the time to get me this far

[Ziggy1]

Originally Posted by Guest2

Your example works perfectly. When i entered into my workbook i received an error message. I think it may be because i want the data to be entered on a different worksheet??? I'm just not familiar enough to work it out. On to a different project for a week or so then i may be able to pick it up again. Thanks for taking the time to get me this far

actually I should have thought of that... when you get back to it upload a sample and I can tailor to your book.

[Guest2]

One sleepless night it came to me...just move the user form to the same worksheet and Voilà, success!
Thanks again

[Ziggy1]

np...cool.... now go get some sleep