Why is the item I'm searching for empty? VBA Nested Loop

[dellehurley]

Hi
When I watch this code LookFor is empty, it shouldn't be and I cannot figure out why. Please help?
I am searching for the number found on ImpWs column B in NmWs column B then swapping out the text. Replacing NmWs with the correct text currently found in ImpWs column A.

Dim NmWs As Worksheet
Dim ImpWs As Worksheet
Dim NmLastRow, ImpLastRow As Long
Dim NmRange As Range
Dim ImpRange As Range
Dim i, j As Long
Dim LookFor As Variant

Set NmWs = ThisWorkbook.Sheets("Nwsh")
Set ImpWs = ThisWorkbook.Sheets("ImportRINS")

'last rows
    NmLastRow = NmWs.Cells(Rows.Count, "A").End(xlUp).Row
    ImpLastRow = ImpWs.Cells(Rows.Count, "A").End(xlUp).Row

'set dynamic arrays
Set NmRange = Range(NmWs.Cells(2, 1), NmWs.Cells(NmLastRow, 2))
Set ImpRange = Range(ImpWs.Cells(2, 1), ImpWs.Cells(ImpLastRow, 3))
    
    For i = ImpLastRow To 2 Step -1
        For j = NmLastRow To 2 Step -1
            LookFor = ImpRange(i, 2)
                If NmRange(j, 2) = LookFor Then
                    NmWs.Cells(j, 1).Clear
                    NmWs.Cells(j, 1).Value = ImpWs.Cells(i, 1).Value
                    ImpWs.Rows(i).EntireRow.Delete
                Exit For
            End If
        Next j
    Next i

This code is ImpWs is updating NmWs

ImpWs

Data_Entry_Form_ver_25c.xlsm
	A	B	C
1	New Name	RIN	Existing Name
2	Braga, Jose Maria (b. 1897)	16898	Braga, Jose Maria (b. 1897)djdsj
3	da Luz, Augusta Isabel da Conceição Ozorio (b. 1898)	16899	da Luz, Augusta Isabel da Conceição Ozorio (b. 1898) NOJN{O
4	Donnelly, Matthew (b. 1884)	16888	Donnelly, Matthew (b. 1890)
5	Haar, Emily Margaret (b. 1914)	16904	Harr, Emily Margaret (b. 1914)
6	Wilson, Mary (b. 1886)	16889	Wilson, Mary (b. 1890)
ImportRINS

NmWs

Data_Entry_Form_ver_25c.xlsm
	A	B
16262	Donnelly, Matthew (b. 1890)	16888
16263	Wilson, Mary (b. 1890)	16889
16264	Wheatley, John C. (b. 1927)	16892
16265	Murhall-Griffith, Thelma Jean (b. 1909)	16894
16266	Donovan, Thomas (b. 1851)	16896
16267	Long, Mary (b. 1860)	16897
16268	Braga, Jose Maria (b. 1897)djdsj	16898
16269	da Luz, Augusta Isabel da Conceição Ozorio (b. 1898) NOJN{O	16899
16270	Rebbechi, Victor Richard (b. 1912)	16903
16271	Harr, Emily Margaret (b. 1914)	16904
16272	Singleton, William Alan (b. 1897)	16908
16273	Coates, Doris Maude (b. 1900)	16909
16274	Worle, Ellen Mercy Eliza (b. 1881)	16905
16275	Wiltshire, William Henry (b. 1879)	16906
16276	Tann, Margaret Mavis (b. 1920)	16901
16277	Kendall, Abel (b. 1908)	16893
16278	Hurley, Ella (b. 1990)	16907
16279	Herrick, Leslie William (b. 1912)	16900
16280	Elizabeth (b. 1965)	16902
16281	David (b. 1950)	16895
Nwsh

 

[Zot]

You define
Set NmWs = ThisWorkbook.Sheets("Nwsh")
Set ImpWs = ThisWorkbook.Sheets("ImportRINS")

as ranges, then try to use them as single cell ranges?

LookFor = ImpRange(i, 2)
                If NmRange(j, 2) = LookFor Then

Perhaps you meant

LookFor = ImpWs.Cells(i, 2)
                If NmWs.Cells(j, 2) = LookFor Then

 

[RoryA]

How are you determining that LookFor is empty?

 

[jasonb75]

ImpRange has been converted to an array so the bounds have been reset. The first pass of the loop is outside of the array. You would need to use something like

For i = ImpRange.Rows.Count To 1 Step -1

for both loops.

 

[dellehurley]

How are you determining that LookFor is empty?

When I watched it and stepped through the procedure

ImpRange has been converted to an array so the bounds have been reset. The first pass of the loop is outside of the array. You would need to use something like

For i = ImpRange.Rows.Count To 1 Step -1

for both loops.

This was definitely part of the problem. Thanks for the advice.

You define
Set NmWs = ThisWorkbook.Sheets("Nwsh")
Set ImpWs = ThisWorkbook.Sheets("ImportRINS")

as ranges, then try to use them as single cell ranges?

LookFor = ImpRange(i, 2)
                If NmRange(j, 2) = LookFor Then

Perhaps you meant

LookFor = ImpWs.Cells(i, 2)
                If NmWs.Cells(j, 2) = LookFor Then

This was definitely part of the problem. Thanks for the advice.

A combination of these worked.
The final working code was

Dim NmWs As Worksheet
Dim ImpWs As Worksheet
Dim NmLastRow, ImpLastRow As Long
Dim NmRange As Range
Dim ImpRange As Range
Dim i, j As Long
Dim LookFor As Variant

Set NmWs = ThisWorkbook.Sheets("Nwsh")
Set ImpWs = ThisWorkbook.Sheets("ImportRINS")

'last rows
    NmLastRow = NmWs.Cells(Rows.Count, "A").End(xlUp).Row
    ImpLastRow = ImpWs.Cells(Rows.Count, "A").End(xlUp).Row

'set dynamic arrays
Set NmRange = Range(NmWs.Cells(2, 1), NmWs.Cells(NmLastRow, 2))
Set ImpRange = Range(ImpWs.Cells(2, 1), ImpWs.Cells(ImpLastRow, 3))
    
    For i = ImpLastRow To 1 Step -1
        For j = NmLastRow To 2 Step -1
            LookFor = ImpWs.Cells(i, 2)
                If NmWs.Cells(j, 2) = LookFor Then
                    NmWs.Cells(j, 1).Clear
                    NmWs.Cells(j, 1).Value = ImpWs.Cells(i, 1).Value
                    ImpWs.Rows(i).EntireRow.Delete
                Exit For
            End If
        Next j
    Next i

End Sub

Thanks for your help, I really appreciate this message board and all of you who answer my questions.
Dannielle