Nested If logic

[VegasNath]

I seem to have a floor in my logic here so would appreciate a little help.

Code: Select all

Set srch = rng4a.Offset(0, 1).Find(What:=ws1b.Range("T" & row), LookIn:=xlValues, LookAt:=xlWhole)
    If Not srch Is Nothing Then
        srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & row)
    ElseIf wb4.Worksheets.Count > 1 And wb4.Worksheets.Count < 3 Then
        Set srch = rng4b.Offset(0, 1).Find(What:=ws1b.Range("T" & row), LookIn:=xlValues, LookAt:=xlWhole)
            If Not srch Is Nothing Then
                srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & row)
            End If
    ElseIf wb4.Worksheets.Count > 2 Then
        Set srch = rng4c.Offset(0, 1).Find(What:=ws1b.Range("T" & row), LookIn:=xlValues, LookAt:=xlWhole)
            If Not srch Is Nothing Then
                srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & row)
            End If
    Else
        Set srch = rng6a.Offset(0, 1).Find(What:=ws1b.Range("T" & row), LookIn:=xlValues, LookAt:=xlWhole)
            If Not srch Is Nothing Then
                srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & row)
            End If
    End If

I need to search:

rng4a (ws4a)
rng4b (ws4b will not always exist)
rng4c (ws4c will not always exist)
rng6a (ws6a)

but cease the search at the earliest opportunity. Help......

wb4 can contain 1, 2 or 3 worksheets, hence my attempt to establish the worksheet count to ascertain if the 2nd and 3rd worksheets exist before searching.

[HansV]

You could also check whether ws4b is different from Nothing - I assume that the variable will not be assigned if the sheet doesn't exist:

If Not ws4b Is Nothing Then
...
End If

Similar for ws4c and ws6a.

[VegasNath]

My initial reaction to your post was "Of course, why did I not think of that?" But I may be mis-understanding you as that is not working.

Code: Select all

Set wb4 = Workbooks.Open(Filename:=strPath & "Data.XLS")

Set ws4a = wb4.Worksheets(1)
lr4a = ws4a.Cells(ws4a.Rows.Count, 2).End(xlUp).row
Set rng4a = ws4a.Range("s2:s" & lr4a)
rng4a.Formula = "=LEFT(F2,16)&"" - ""&I2"
rng4a.Offset(0, 1).Formula = "=TEXT(LEFT(A2,16),""000000"")&"" - ""&I2"

If wb4.Worksheets.Count > 1 Then
    Set ws4b = wb4.Worksheets(2)
    lr4b = ws4b.Cells(ws4b.Rows.Count, 2).End(xlUp).row
    Set rng4b = ws4b.Range("s2:s" & lr4b)
    rng4b.Formula = "=LEFT(F2,16)&"" - ""&I2"
    rng4b.Offset(0, 1).Formula = "=TEXT(LEFT(A2,16),""000000"")&"" - ""&I2"
End If

If wb4.Worksheets.Count > 2 Then
    Set ws4c = wb4.Worksheets(3)
    lr4c = ws4c.Cells(ws4c.Rows.Count, 2).End(xlUp).row
    Set rng4c = ws4c.Range("s2:s" & lr4c)
    rng4c.Formula = "=LEFT(F2,16)&"" - ""&I2"
    rng4c.Offset(0, 1).Formula = "=TEXT(LEFT(A2,16),""000000"")&"" - ""&I2"
End If

Set srch = rng4a.Offset(0, 1).Find(What:=ws1b.Range("T" & lrw), LookIn:=xlValues, LookAt:=xlWhole)
    If Not srch Is Nothing Then
        srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & lrw)
        ws1b.Range("U" & lrw) = "ws4a"  'Testing Output
    ElseIf Not ws4b Is Nothing Then
        Set srch = rng4b.Offset(0, 1).Find(What:=ws1b.Range("T" & lrw), LookIn:=xlValues, LookAt:=xlWhole)
            If Not srch Is Nothing Then
                srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & lrw)
                ws1b.Range("U" & lrw) = "ws4b"  'Testing Output
            End If
    ElseIf Not ws4c Is Nothing Then
        Set srch = rng4c.Offset(0, 1).Find(What:=ws1b.Range("T" & lrw), LookIn:=xlValues, LookAt:=xlWhole)
            If Not srch Is Nothing Then
                srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & lrw)
                ws1b.Range("U" & lrw) = "ws4c"  'Testing Output
            End If
    Else
        Set srch = rng6a.Offset(0, 1).Find(What:=ws1b.Range("T" & lrw), LookIn:=xlValues, LookAt:=xlWhole)
            If Not srch Is Nothing Then
                srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & lrw)
                ws1b.Range("U" & lrw) = "ws6a"  'Testing Output
            End If
    End If

[HansV]

Could you explain in what way it is not working?

Do you get an error message? If so, on which line, and what does it say?
Do you get unexpected/undesired results? If so, what exactly?

[VegasNath]

Hi Hans,

No error's, just not the desired result. There is no search result returned from ws4c.

[HansV]

The ranges rng4a etc. do not appear to be defined in the code...

[VegasNath]

It appears that neither ws4c or ws6a search results are returned.

I will update my previous code to show the full definitions...... Now updated

[VegasNath]

If I end each of the If statements (removing the ElseIf), then it works, but this makes no sense to me, and executes much slower. It seems pointless to evaluate once a succesful search result is found.

[HansV]

The problem is that you only test if the worksheet exists. If it does, the next test won't be performed, even if the search didn't succeed. Try this:

Code: Select all

...
Dim blnFound As Boolean
Set srch = rng4a.Offset(0, 1).Find(What:=ws1b.Range("T" & lrw), LookIn:=xlValues, LookAt:=xlWhole)
If Not srch Is Nothing Then
    srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & lrw)
    ws1b.Range("U" & lrw) = "ws4a"  'Testing Output
    blnFound = True
End If
If Not ws4b Is Nothing And blnFound = False Then
    Set srch = rng4b.Offset(0, 1).Find(What:=ws1b.Range("T" & lrw), LookIn:=xlValues, LookAt:=xlWhole)
    If Not srch Is Nothing Then
        srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & lrw)
        ws1b.Range("U" & lrw) = "ws4b"  'Testing Output
        blnFound = True
    End If
End If
If Not ws4c Is Nothing And blnFound = False Then
    Set srch = rng4c.Offset(0, 1).Find(What:=ws1b.Range("T" & lrw), LookIn:=xlValues, LookAt:=xlWhole)
    If Not srch Is Nothing Then
        srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & lrw)
        ws1b.Range("U" & lrw) = "ws4c"  'Testing Output
        blnFound = True
    End If
End If
If blnFound = False Then
    Set srch = rng6a.Offset(0, 1).Find(What:=ws1b.Range("T" & lrw), LookIn:=xlValues, LookAt:=xlWhole)
    If Not srch Is Nothing Then
        srch.Offset(0, -14).Resize(, 6).Copy Destination:=ws1b.Range("G" & lrw)
        ws1b.Range("U" & lrw) = "ws6a"  'Testing Output
        blnFound = True
    End If
End If
' If blnFound is still False here, none of the searches succeeded.

[VegasNath]

Thanks Hans, it works a treat.