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