VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not set
Hello
I'm getting an error message run-time error '91': Object variable or With block variable not set
PHP Code:
Dim sStart As String
Dim lTargetRow As Long
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rSourceCell As Range
Dim LastRow As Long
Dim searchRange As Range
'Setup
Application.ScreenUpdating = False
Set wsTarget = Sheets("Sub-Analysis OUTPUT Desc")
Set wsSource = Sheets("Sub-Analysis Core")
Do While Not IsEmpty(rSourceCell)
With wsSource
LastRow = Range("B65536").End(xlUp).Row
End With
Set searchRange = Sheets("Sub-Analysis Core").Range("B5:B" & LastRow)
'Get last used row in Target Sheet
With wsTarget
lTargetRow = .Range("B65536").End(xlUp).Offset(1, 0).Row
End With
'Save start address for comparison
sStart = ActiveCell.Address
Do
'copy find result to target sheet
ActiveCell.Copy Destination:=wsTarget.Cells(lTargetRow, 3)
ActiveCell.Offset(0, -1).Copy Destination:=wsTarget.Cells(lTargetRow, 2)
ActiveCell.Offset(0, 2).Copy Destination:=wsTarget.Cells(lTargetRow, 5)
ActiveCell.Offset(0, 3).Copy Destination:=wsTarget.Cells(lTargetRow, 10)
'Set destination = plus one Row
lTargetRow = lTargetRow + 1
'Find next item (til done)
Cells.FindNext(After:=ActiveCell).Activate
Loop Until ActiveCell.Address = sStart
Set rSourceCell = rSourceCell.Offset(1, 0) <<<<<< ERROR HERE
Loop
Basically I'm searching for specific text (Subname), picked up via user input thorugh InputBox, in a single column (column B in wsSource), copying that to wsTarget then going back to pick up other data in other columns on the same row, copying that data to wsTarget as well. Then loop to find next instance of Subname and repeat the copying priocess.
I'm experiencing a problem whereby the Find function is searching the whole sheet and not just column B.
Re: VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not
ok sorted the error message, I hadn't set the rSourceCell. Should have inserted
PHP Code:
Set rSourceCell = Range("B5")
after With wsource. Nevertheless I'm still experiencing the issue whereby the Find function searches the whole page but I only want to search Column B. Any ideas?
Thanks
Bookmarks