CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

Method or data member not found vba

I have an userform designed with three listbox. The list box are populated from sheets, List_Dev_Red,List_Man_Red,List_SQM_Red. in my userform activate , I get an error "Method or data member" not found in the below line

For xRow = 2 To Last(1, List_Dev_Red.Range("A:A"))

Could any one tel me what could be the reason for this error. 

Private Sub UserForm_Activate()
Dim xRow As Integer
Dim yRow As Integer
Dim zrows As Integer

For xRow = 2 To Last(1, List_Dev_Red.Range("A:A"))
    With LB1
        .AddItem List_Dev_Red.Cells(xRow, 3).Value
        If List_Dev_Red.Cells(xRow, 2) = True Then
            .Selected(xRow - 2) = True
        Else
            .Selected(xRow - 2) = False
        End If
    End With
Next xRow
LB1.Height = (xRow - 1) * 15

For yRow = 2 To Last(1, List_Man_Red.Range("A:A"))
With LB2
.AddItem List_Man_Red.Cells(yRow, 3).Value
If List_Man_Red.Cells(yRow, 2) = True Then
.Selected(yRow - 2) = True
Else
.Selected(yRow - 2) = False
End If
End With
Next yRow
LB2.Height = (yRow - 1) * 15


For zrows = 2 To Last(1, List_SQM_Red.Range("A:A"))
With LB3
.AddItem List_SQM_Red.Cells(zrows, 3).Value
If List_SQM_Red.Cells(zrows, 2) = True Then
.Selected(zrows - 2) = True
Else
.Selected(zrows - 2) = False
End If
End With
Next zrows
LB3.Height = (zrows - 1) * 15
End Sub

Function Last (from RondeBruin):

Function Last(choice As Long, rng As Range)
' 1 = last row
' 2 = last column
' 3 = last cell
    Dim lrw As Long
    Dim lcol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlWhole, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlWhole, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       LookAt:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0

    End Select


End Function

Upvotes: 0

Views: 1069

Answers (3)

Chris
Chris

Reputation: 1

Option Explicit
'***** VARIABLES *****
Public wbI As Workbook, wsChess As Worksheet
Public blackPlayer As cPlayer, whitePlayer As cPlayer
Public txtB_bPlayerName As TextBox, txtB_wPlayerName As TextBox

Public Sub initVars()

    Set wbI = ThisWorkbook
    Set wsChess = wbI.Sheets(1)

    'Works
    ThisWorkbook.Sheets(1).txtB_bPlayerName.Value = ""

    'Don't work
    wsChess.txtB_wPlayerName.Value = ""

End Sub

Upvotes: 0

Vityata
Vityata

Reputation: 43575

In general, concerning that you know that you are looking for either column or row, you may use simplified functions like these:

Option Explicit

Function LastRow(sh As Worksheet) As Long
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Function LastCol(sh As Worksheet) As Long
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

They are from the same site as the Variant function.

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33672

You need to change your syntax a bit (at the upper section of your code):

Dim xRow As Long
Dim yRow As Long
Dim zrows As Long
Dim LastRow As Long  ' <-- change all variables to Long (be on the safe side)

' get the last row by calling your Last function
LastRow = Last(1, List_Dev_Red.Range("A:A")) ' <-- you are passing a Range, and want to get a Long, representing the row number

' loop through your rows
For xRow = 2 To LastRow
    ' rest of your code

Next xRow

and make the change to your Function to return a Long (row number):

Function Last(choice As Long, rng As Range) As Long

Upvotes: 1

Related Questions