Bẫy lỗi WorksheetFunction

[LEHOC]

Chào các anh/chị!
Em có code sau, em dung bắt lỗi bang GoTo nhưng vẫn bị lỗi "Unable to get the Search property of the WorksheetFunction class"

Sub CpySh7ToSh1()
Dim LrSh7, LrSh1 As Long
Dim tst
Set Sh1 = Sheet1
Set Sh7 = Sheet7
LrSh1 = Sh1.Range("AL2").End(xlDown).Row
LrSh7 = Sh7.Range("B7").End(xlDown).Row
If LrSh1 > 1 And LrSh1 < 1000 Then Sh1.Range("AL2:AM" & LrSh1).ClearContents
For i = 7 To LrSh7
    tst = True
    With Application.WorksheetFunction
        'Dang loi neu IsError = True
        On Error GoTo Loi
       [COLOR=rgb(184, 49, 47)] tst = IsError(.Search(" ", Sh7.Range("B" & i), 1)) 'Bi loi neu ham Search khong tim thay gia tri trong chuoi[/COLOR]
    End With
    If tst = False Then
        Sh1.Range("AL" & i - 5).Value = Sh7.Range("A" & i).Value
        Sh1.Range("AM" & i - 5).Value = Sh7.Range("C" & i).Value
    End If
Loi:
'    Else
    If tst = True Then
        Sh1.Range("AL" & i - 5 & ":AM" & i - 5).Value = Sh7.Range("B" & i & ":C" & i).Value
    End If
Next i
Sh1.Activate
End Sub

Em cảm ơn!

 

[HeSanbi]

Code lại 1 tí giúp bạn:

Sub CpySh7ToSh1()
  Dim Sh1 As Worksheet, Sh7 As Worksheet
  Dim LrSh7 As Long, LrSh1 As Long
  Set Sh1 = ThisWorkbook.Worksheets("Sheet1")
  Set Sh7 = ThisWorkbook.Worksheets("Sheet7")
 
  LrSh1 = Sh1.Range("AL2").End(xlDown).Row 'Nên dùng xlUp để không gặp 1 vài trường hợp'
  LrSh7 = Sh7.Range("B7").End(xlDown).Row
 
  If LrSh1 > 1 And LrSh1 < 1000 Then Sh1.Range("AL2:AM" & LrSh1).ClearContents
  
  For i = 7 To LrSh7
    If Sh7.Range("B" & i).Value Like "* *" Then
        Sh1.Range("AL" & i - 5 & ":AM" & i - 5).Value = Sh7.Range("B" & i & ":C" & i).Value
    Else
        Sh1.Range("AL" & i - 5).Value = Sh7.Range("A" & i).Value
        Sh1.Range("AM" & i - 5).Value = Sh7.Range("C" & i).Value
    End If
  Next i
  Sh1.Activate
End Sub

 

[LEHOC]

Code lại 1 tí giúp bạn:

Sub CpySh7ToSh1()
  Dim Sh1 As Worksheet, Sh7 As Worksheet
  Dim LrSh7 As Long, LrSh1 As Long
  Set Sh1 = ThisWorkbook.Worksheets("Sheet1")
  Set Sh7 = ThisWorkbook.Worksheets("Sheet7")

  LrSh1 = Sh1.Range("AL2").End(xlDown).Row 'Nên dùng xlUp để không gặp 1 vài trường hợp'
  LrSh7 = Sh7.Range("B7").End(xlDown).Row

  If LrSh1 > 1 And LrSh1 < 1000 Then Sh1.Range("AL2:AM" & LrSh1).ClearContents
 
  For i = 7 To LrSh7
    If Sh7.Range("B" & i).Value Like "* *" Then
        Sh1.Range("AL" & i - 5 & ":AM" & i - 5).Value = Sh7.Range("B" & i & ":C" & i).Value
    Else
        Sh1.Range("AL" & i - 5).Value = Sh7.Range("A" & i).Value
        Sh1.Range("AM" & i - 5).Value = Sh7.Range("C" & i).Value
    End If
  Next i
  Sh1.Activate
End Sub

Cảm ơn bác!