Find 1st Empty Cell in Column D Only If Column B On same Row as Empty Cell, is Not Empty

Hello,

I've been struggling with this problem for a few days now.

I have 2 Workbooks (WB1 and WB2). WB2 contains links copied from the same cells in WB1.

I need to write some code that finds the first empty cell in column D AND the cell in same row in column B has data (all cells have links). If the cell in the same row (as the empty D cell) in B is also empty, then the search should resume in the next sheet, and so on until a cell matching the conditions is met and the cell is copied.

The following code seems to work perfectly in WB1 which has no link cells. But as soon as it is tried in WB2 (with the link cells) it fails because the IsEmpty function sees the link address as data. I'm guessing I would need to use Find("", LookIn:=xlValues) so it is only searching the values of the link cells, rather than the link address itself.

Option Explicit

Sub Find_Next_Empty_In_Links1()
'
' Find_Next_Empty_In_Links Macro
'

'
Dim Rng1 As String
Dim i As Long
Dim shtCount As Long

shtCount = Sheets.Count

For i = 1 To shtCount

            If IsEmpty(Sheets(i).Range("D:D").Find("", LookIn:=xlValues).Offset(0, -2).Value) = False Then
                Sheets(i).Activate
                Sheets(i).Range("D:D").Find("", LookIn:=xlValues).Offset(0, -2).Copy
                Exit For
            End If

Next i
End Sub

The next code only works in WB2 sheet 2 or 3 where there are still, for example, an empty cell in D5 with B5 containing data. If it is used on WB2 sheet 1 then I get the Run-time error '91': Object variable or with block variable not set.

Sub Find_Next_Empty_In_Links2()
'
' Find_Next_Empty_In_Links Macro
'
 
'
 
Dim Rng1 As String

Rng1 = Range("B:B").Find("", LookIn:=xlValues).Offset(-1, 0).Address
Range("D2:" & Rng1).Find("", LookIn:=xlValues).Offset(0, -2).Select
End Sub

I understand the error is because, if it cannot find an empty cell in column D which also has data in the same row column B, then I need to tell it to search the next sheet and so on until a cell matching those conditions is found. Exactly how to do this though is where I'm falling down.

Any help you can give me would be amazing as I'm going bald fast trying to figure this out!