dellehurley
Board Regular
- Joined
- Sep 26, 2009
- Messages
- 171
- Office Version
- 365
- Platform
- Windows
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.
This code is ImpWs is updating NmWs
ImpWs
NmWs
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.
VBA Code:
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 |