Run-Time Error 438 Object Doesn't Support this property

[FuNeS13]

I'm bumping to this error with this code... Anyone know how can it be fixed? I tried several different options but none seem to work...

Private Sub TextBox1_AfterUpdate()



With Me


Dim sht1 As Worksheet, sht2 As Worksheet, wb As Workbook


Set wb = ActiveWorkbook
Set sht1 = wb.Worksheets("Data")
Set sht2 = wb.Worksheets("Entries")


Dim I As Long
I = Worksheets(2).Range("A1048576").End(xlUp).Offset(1, 0).Row




    
    .TextBox2 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), wb.sht1.Range("A2:G835"), 2, 0)  '<<-- Error appears here... 
    .TextBox3 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), wb.sht1.Range("A2:G835"), 3, 0)
    .TextBox4 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), wb.sht1.Range("A2:G835"), 4, 0)
    .TextBox5 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), wb.sht1.Range("A2:G835"), 5, 0)
    
        
    .TextBox8 = Time()


End With

End Sub

 

[Fluff]

Remove the wb. from the vlookup so it's just sht1.Range...

 

[DanteAmor]

Remove the wb object:

Private Sub TextBox1_AfterUpdate()
  With Me
    Dim sht1 As Worksheet, sht2 As Worksheet, wb As Workbook
    Set wb = ActiveWorkbook
    Set sht1 = wb.Worksheets("Data")
    Set sht2 = wb.Worksheets("Entries")
    Dim I As Long
    I = Worksheets(2).Range("A1048576").End(xlUp).Offset(1, 0).Row
    .TextBox2 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), [COLOR=#0000ff]sht1[/COLOR].Range("A2:G835"), 2, 0)
    .TextBox3 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), sht1.Range("A2:G835"), 3, 0)
    .TextBox4 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), sht1.Range("A2:G835"), 4, 0)
    .TextBox5 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox1), sht1.Range("A2:G835"), 5, 0)
    .TextBox8 = Time()
  End With
End Sub

 

[DanteAmor]

Defeated

 

[FuNeS13]

After I do that I now get Run-Time Error 1004 - Unable to get the Vlookup property of the worksheetfunction class...

 

[DanteAmor]

Some recommendations.
- If you use a single userform it is not necessary Me.
- Use the Find method, that way only perform a single search.
Try this:

Private Sub TextBox1_AfterUpdate()
  Dim sht1 As Worksheet, sht2 As Worksheet, wb As Workbook
  Dim I As Long, f As Range
  Set wb = ActiveWorkbook
  Set sht1 = wb.Worksheets("Data")
  Set sht2 = wb.Worksheets("Entries")
  I = sht2.Range("A1048576").End(xlUp).Offset(1, 0).Row
  Set f = sht1.Range("A2:A835").Find(CLng(TextBox1), , xlValues, xlWhole)
  If Not f Is Nothing Then
    TextBox2 = f.Offset(, 1)
    TextBox3 = f.Offset(, 2)
    TextBox4 = f.Offset(, 3)
    TextBox5 = f.Offset(, 4)
    TextBox8 = Time()
  End If
End Sub

 

[Fluff]

That means it can't find the textbox value, try it like

Private Sub TextBox1_AfterUpdate()
   Dim sht1 As Worksheet, sht2 As Worksheet, wb As Workbook
   Dim Res As Variant
   
   Set wb = ActiveWorkbook
   Set sht1 = wb.Worksheets("Data")
   Set sht2 = wb.Worksheets("Entries")
   
   With Me
      Res = Application.VLookup(CLng(Me.TextBox1), wb.sht1.Range("A2:G835"), 2, 0)
      If Not IsError(Res) Then .TextBox2 = Res
      Res = Application.VLookup(CLng(Me.TextBox1), wb.sht1.Range("A2:G835"), 3, 0)
      If Not IsError(Res) Then .TextBox3 = Res
           
       .TextBox8 = Time()
   End With

End Sub

My turn to be too slow

 

[FuNeS13]

Thank you both, Fluff options gives the 438 error, and Dante's IF statement just goes to the END IF part... It was working before... not sure where in the process this stop working...

 

[Fluff]

I forgot to remove the wb. part, but it doesn't change the fact that your textbox value is not being found in A2:A835 on the data sheet.

 

[RoryA]

Change the wb.sht1 in Fluff's code to just sht1