CODEWITHSUNDEEP
excelvbacompilation
user2216130
user2216130

Reputation: 5

Receiving a Complie Error method or data member not found

I'm creating a shared worksheet - In column O, when they click I have a User form created asking 3 questions. I need those answers to be put into a hidden worksheet. I've written the code, but when I run it, I get the compile error method. Since I'm so new to VBA, I'm sure I'm missing something I don't understand. There is prob all kinds of errors I'm missing. This is my first one that I haven't just copied and pasted. So any help would be greatly appreciated! IN this case, I googled and found a form that was about what I was looking for and tried to adjust it to fit what I needed it to be. Probably my first mistake!!

It is highlighting the 

iRow = ws.Cells.Find(What:="*", SearchOrder:=x1Rows, _
        SearchDirection:=x1Previous, LookIn:=x1Values).Row + 1

Here is my entire code. Please please help! 

Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("WebLeadInfo")

    'find first empty row database
   iRow = ws.Cells.Find(What:="*", SearchOrder:=x1Rows, _
        SearchDirection:=x1Previous, LookIn:=x1Values).Row + 1

    'check for a contact
    If Trim(Me.txtContact.Value) = " " Then
        Me.txtContact.SetFocus
        MsgBox "Please enter info"
        Exit Sub
    End If

    'copy the data to the database
    'use protect and unprotect lines,
    '   with your password
    '   if worksheet is protected
    With ws
    '   .Unportect Password:="sunway12"
       .Cells(lRow, 1).Value = Me.txtContact.Value
       .Cells(lRow, 3).Value = Me.txtFind.Value
       .Cells(lRow, 4).Value = Me.txtSearch.Value
       .Protect Password:="sunway12"
    End With

    'clear the data
    Me.txtContact.Value = " "
    Me.txtFind.Value = " "
    Me.txtSearch.Value = " "
    Me.txtContact.SetFocus


End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Upvotes: 0

Views: 3651

Answers (2)

gembird
gembird

Reputation: 14053

In documantation for Find method you can see that parameter SearchOrder can be one of the following XlSearchOrder constants: 

xlByRows
xlByColumns

And SearchDirection can be one of the following XlSearchDirection constants: 

xlNext  
xlPrevious

Finally LookIn parameter should be set to:

xlValues

So in your case it would be:

'find first empty row database
Dim findResult As Range
Set findResult = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues)

iRow = 1 ' in case that the sheet contains no data the first empty row is the first one
If (Not findResult Is Nothing) Then iRow = findResult.Row + 1

' ...

With ws
'   .Unportect Password:="sunway12"
   .Cells(iRow, 1).Value = Me.txtContact.Value
   .Cells(iRow, 3).Value = Me.txtFind.Value
   .Cells(iRow, 4).Value = Me.txtSearch.Value
   .Protect Password:="sunway12"
End With

Upvotes: 0

Daniel Möller
Daniel Möller

Reputation: 86650

x1Rows (and the others as well). They are using a "one" character in there. But they should be spelled with "L" XLROWS, not X1ROWS

Upvotes: 2

Related Questions