Userform problem adding to existing rows of data

**glenness** · 10-18-2007, 07:52 PM

Hello Everyone,
I have a scheduling program with two userforms. One form adds five different pieces of data to Cells A to E on a row in a worksheet. This worksheet has many existing rows of data, each row as an unique set of info(There no duplicate entries of Date(Cell A) and Simulator session(Cell C), I have code to prevent this). This form works 100%.
Here is my trouble spot: The second form is for the schedulers to place names of Operators in these existing rows of data (cells H to J is where I want the data to go), What I want the second form to do is search an the existing rows on the worksheet, find a match based on two criteria; (Cell A)Date and (Cell C)Simulator Session. Then add the names of the operator(s) in the assigned columns in that same row.
Something to this effect:
If Userform(TxtDate) matches Cell A and UserForm(TxtSim) matches Cell C then add (Userform)TxtPilot to Cell 8 and userform(TxtNavs) to Cell 10 etc.

Here is the code I started but can’t get to work, When I click the submit add button the form runs and clears itself like I want but I can’t get the data to go where it should

 Private Sub cmdAdd_Click()
Dim iRow, row_count As Long
Dim i As Integer
Dim ws As Worksheet
Set ws = Worksheets("404ONLY")

For i = 2 To row_count
        If ws.Cells(iRow, 1,3).Value = Me.TxtDate.Value + Me.TxtSim.Value Then
        ws.Cells(iRow, 8).Value = Me.Txtpilot.Value
        ws.Cells(iRow, 10).Value = Me.TxtNavs.Value
        ws.Cells(iRow, 11).Value = Me.TxtAesops
        Exit Sub
    End If
Next i
'clear the data
Me.Txtpilot.Value = ""
Me.TxtNavs.Value = ""
Me.TxtAesops.Value = ""
Me.Txtpilot.SetFocus

End Sub

Any help would be greatly appreciated.
Glenness

**Ikaabod** · 10-18-2007, 08:09 PM

At first glance there are a couple things I noted.
1. That you use iRow yet you never give the variable a value.
2. I'm not familiar with the use of cells(iRow, 1, 3)
3. You never assign row_count a value either...

To solve these I:
1. Changed "i" to "iRow"
2. took out cells(iRow, 1, 3) and made it cells(iRow, 1) and cells(iRow, 3)
3. try something like row_count = ws.range("A" & rows.count).end(xlup).row

See code below. Hope this solves the issue.

Private Sub CommandButton1_Click()
    Dim iRow, row_count As Long
    Dim ws As Worksheet
    Set ws = Worksheets("404ONLY")

    row_count = ws.range("A" & rows.count).end(xlup).row    

    For iRow = 2 To row_count
        If ws.Cells(iRow, 1) = Me.TxtDate.Value And ws.Cells(iRow, 3) = Me.TxtSim.Value Then
            ws.Cells(iRow, 8) = Me.Txtpilot.Value
            ws.Cells(iRow, 10) = Me.TxtNavs.Value
            ws.Cells(iRow, 11) = Me.TxtAesops
            Exit Sub
        End If
    Next iRow
    'clear the data
    Me.Txtpilot.Value = ""
    Me.TxtNavs.Value = ""
    Me.TxtAesops.Value = ""
    Me.Txtpilot.SetFocus
    
End Sub

**glenness** · 10-18-2007, 08:36 PM

Hi Ikaabod,
First thank you for picking up my left over code from another issue, however the new code still will not add the data to the cell in the existing rows. On click the form pops up I input the data that I want to add hit submit, the form clears but nothing in the cells on the worksheet. I'm still scratching my head on this one.
Glenness

Attached is a sample of the file, I hope I attached this correctly...I apologize to all if I didn't.
[attach]sched oper sample.zip[/attach]

**Ikaabod** · 10-19-2007, 02:49 PM

Revisions are noted in code.

Private Sub UserForm_Initialize()
    Txtpilot.Value = ""
    TxtNavs.Value = ""
    TxtAesops.Value = ""
    End Sub
Private Sub cmdAdd_Click()
    Dim iRow, row_count As Long
    Dim ws As Worksheet
    
'Added=================
    Dim strDate As String
'======================
    
    Set ws = Worksheets("404INFO TESTPAGE")
    
'Added=================
    row_count = Range("A" & Rows.Count).End(xlUp).Row
    Me.TxtDate.Value = Format(Me.TxtDate.Value, "short date")
'======================
    
    For iRow = 2 To row_count
'Added=================
        strDate = ws.Cells(iRow, 1).Value
'======================

'Changed===============
        If strDate = Me.TxtDate.Value And UCase(ws.Cells(iRow, 3)) = UCase(Me.TxtSim.Value) Then
'======================

            ws.Cells(iRow, 8) = Me.Txtpilot.Value
            ws.Cells(iRow, 10) = Me.TxtNavs.Value
            ws.Cells(iRow, 11) = Me.TxtAesops.Value
            
'Changed===============
            Exit For
'======================

        End If
    Next iRow
    'clear the data
    Me.TxtDate.Value = ""
    Me.TxtSim.Value = ""
    Me.Txtpilot.Value = ""
    Me.TxtNavs.Value = ""
    Me.TxtAesops.Value = ""
    Me.Txtpilot.SetFocus
    
End Sub

Private Sub cmdClose_Click()
  Unload Me
End Sub

**glenness** · 10-20-2007, 08:40 AM

Hello Ikaabod,
Thanks again for responding, I thought this would be an easy one, however I still can't get this to work, your code runs but it still will not place the data in the applicable cells. I have attached the sample with your new code attached.
Glenness