rrosa1
04-19-2010, 04:08 PM
hi
i have two worksheet and one user form my code is working accepted deleting the row from worksheet "b" can somebody help me ?it give the error msg as
Run time error 438 object doesn't support property or method
it's newbie so pl be patience.
Private Sub Enterdata_Click()
Dim n As Long
Dim ws As Worksheet
Dim R As Range
Dim var
Dim i&
With Worksheets("B")
n = Application.Match((Me.CombT.Value), .Range("A:A"), 0)
.Cells(n, 4).Value = Me.noofs.Text
End With
'check for a Ticker
If Trim(Me.CombT.Value) = "" Then
Me.CombT.SetFocus
MsgBox "Please enter a T"
Exit Sub
End If
'correspondence with "T" and "#of S"
Set ws = Worksheets("H")
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If Me.CombT.Value = ws.Cells(i, 1).Value Then
If MsgBox("T already exists in records. Do you still want to add the data to the records?", vbYesNo) = vbNo Then
Exit Sub
Else
Exit For
End If
End If
Next i
'Find the next empty row to copy the data to
Set Rng = ws.Range("A2")
Set RngEnd = ws.Cells(Rows.Count, Rng.Column).End(xlUp)
NextRow = IIf(RngEnd.Row < Rng.Row, Rng.Row, RngEnd.Row + 1)
With Worksheets("B")
If Trim(.Cells(n, "A")) = Trim(CombT.Value) And _
Trim(.Cells(n, "D")) = Trim(noofs.Value) Then
'copy the data to the database
With ws
.Cells(NextRow, "A").Resize(1, 10) = Worksheets("B").Cells(n, "A").Resize(1, 10).Value
.Cells(NextRow, "K").Value = Me.TextBox1.Value
.Cells(NextRow, "L").Value = Me.txtHoD.Value
.Cells(NextRow, "M").Value = Me.txtLoD.Value
.Cells(NextRow, "N").Value = Me.txtExitP.Value
.Cells(NextRow, "O").Value = Me.txtcom.Value
End With
End If
End With
Dim LR As Long
With Worksheets("B")
LR = Application.Match((Me.CombT.Value), .Range("A:A"), 0)
.EntireRow.Delete
End With
'clear the data
Me.CombT.Value = ""
Me.TextBox1.Value = Format(Date, "Medium Date")
Me.txtExitP.Value = ""
Me.txtcom = 7
Me.txtHoD.Value = ""
Me.txtLoD.Value = ""
Me.CombT.SetFocus
End Sub
Private Sub CombT_Change()
Dim vreg As String
Dim drow As Integer
Dim c As Range
If bEdit Then Exit Sub
vreg = CombT.Value
With Sheets("B").Range("Ticker")
Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then drow = c.Row
End With
With Me
.noofs.Value = Sheets("B").Cells(drow, 4).Value
End With
End Sub
i have two worksheet and one user form my code is working accepted deleting the row from worksheet "b" can somebody help me ?it give the error msg as
Run time error 438 object doesn't support property or method
it's newbie so pl be patience.
Private Sub Enterdata_Click()
Dim n As Long
Dim ws As Worksheet
Dim R As Range
Dim var
Dim i&
With Worksheets("B")
n = Application.Match((Me.CombT.Value), .Range("A:A"), 0)
.Cells(n, 4).Value = Me.noofs.Text
End With
'check for a Ticker
If Trim(Me.CombT.Value) = "" Then
Me.CombT.SetFocus
MsgBox "Please enter a T"
Exit Sub
End If
'correspondence with "T" and "#of S"
Set ws = Worksheets("H")
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If Me.CombT.Value = ws.Cells(i, 1).Value Then
If MsgBox("T already exists in records. Do you still want to add the data to the records?", vbYesNo) = vbNo Then
Exit Sub
Else
Exit For
End If
End If
Next i
'Find the next empty row to copy the data to
Set Rng = ws.Range("A2")
Set RngEnd = ws.Cells(Rows.Count, Rng.Column).End(xlUp)
NextRow = IIf(RngEnd.Row < Rng.Row, Rng.Row, RngEnd.Row + 1)
With Worksheets("B")
If Trim(.Cells(n, "A")) = Trim(CombT.Value) And _
Trim(.Cells(n, "D")) = Trim(noofs.Value) Then
'copy the data to the database
With ws
.Cells(NextRow, "A").Resize(1, 10) = Worksheets("B").Cells(n, "A").Resize(1, 10).Value
.Cells(NextRow, "K").Value = Me.TextBox1.Value
.Cells(NextRow, "L").Value = Me.txtHoD.Value
.Cells(NextRow, "M").Value = Me.txtLoD.Value
.Cells(NextRow, "N").Value = Me.txtExitP.Value
.Cells(NextRow, "O").Value = Me.txtcom.Value
End With
End If
End With
Dim LR As Long
With Worksheets("B")
LR = Application.Match((Me.CombT.Value), .Range("A:A"), 0)
.EntireRow.Delete
End With
'clear the data
Me.CombT.Value = ""
Me.TextBox1.Value = Format(Date, "Medium Date")
Me.txtExitP.Value = ""
Me.txtcom = 7
Me.txtHoD.Value = ""
Me.txtLoD.Value = ""
Me.CombT.SetFocus
End Sub
Private Sub CombT_Change()
Dim vreg As String
Dim drow As Integer
Dim c As Range
If bEdit Then Exit Sub
vreg = CombT.Value
With Sheets("B").Range("Ticker")
Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then drow = c.Row
End With
With Me
.noofs.Value = Sheets("B").Cells(drow, 4).Value
End With
End Sub