UserForm for data entry

Hello,

Try the following code

Private Sub Enterdata_Click()
Dim ws As Worksheet
Dim R As Range
Dim var
Dim i&
'check for a Ticker
If Trim(Me.CombTicker.Value) = "" Then
  Me.CombTicker.SetFocus
  MsgBox "Please enter a Ticker"
  Exit Sub
End If
'correspondence with "Ticker" and "#of Stock"
Set ws = Worksheets("TRADERSSPREADSHEET")
Set R = ws.Range(ws.Cells(1, 3), ws.Cells(ws.[a65536].End(xlUp).Row, 5))   '"C1:Ex"
var = R
For i& = 4 To UBound(var, 1)
  If var(i&, 1) = Trim(Me.CombTicker.Value) And _
      var(i&, 3) = Trim(Me.CombNoofStock.Value) Then
    'copy the data to the database
    With ws
      .Cells(i&, 13).Value = Me.txtExitDate.Value
      .Cells(i&, 14).Value = Me.txtHighofDay.Value
      .Cells(i&, 15).Value = Me.txtLowofDay.Value
      .Cells(i&, 16).Value = Me.txtExitPrice.Value
      .Cells(i&, 17).Value = Me.txtcommission.Value
    End With
    Exit For
  End If
Next i&
'clear the data
Me.CombTicker.Value = ""
Me.CombNoofStock.Value = ""
Me.txtExitDate.Value = Format(Date, "Medium Date")
Me.txtExitPrice.Value = ""
Me.txtcommission = 7
Me.txtHighofDay.Value = ""
Me.txtLowofDay.Value = ""
Me.CombTicker.SetFocus
End Sub

Best regards.

PMO
Patrick Morange

hi
PMO
Patrick Morange

thanks for looking in the prob. i DL the file u attached but when i enter the exit data its not populated the corresponding row also it don't give me any error msg. but i can see that u populated the same but it not work whith me is that posible since i am using Excel 2003
VB 6.3 so pl help sorry to be pain.
thanks

Hello,

Change

  If var(i&, 1) = Trim(Me.CombTicker.Value) And _
      var(i&, 3) = Trim(Me.CombNoofStock.Value) Then

by

  If Trim(CStr(var(i&, 1))) = Trim(Me.CombTicker.Value) And _
      Trim(CStr(var(i&, 3))) = Trim(Me.CombNoofStock.Value) Then

Best regards.

PMO
Patrick Morange

Originally Posted by PMO

Hello,

Change

  If var(i&, 1) = Trim(Me.CombTicker.Value) And _
      var(i&, 3) = Trim(Me.CombNoofStock.Value) Then

by

  If Trim(CStr(var(i&, 1))) = Trim(Me.CombTicker.Value) And _
      Trim(CStr(var(i&, 3))) = Trim(Me.CombNoofStock.Value) Then

Best regards.

PMO
Patrick Morange

thanks a lot it work like charms
thanks again