do loop to match cell value spacing issue with data

[jacinthn]

i have 2 workbooks a tracker and a Data workbook ("inventory")with list of comprehensive values i have a loop that looks for the value in cell a1 of the tracker and returns all the corresponding values from the Data workbook, well i have come across a case where my A1 tracker value is listed on the Inventory sheet twice eg Brown,Dave and then with an extra space Brown, Dave
my work around has been to add the 2nd name with the extra space to cell B1 of the tracker and modify the first line of the macro with an IF OR statement, It works but seems messy. is there any way to get the code to recognize the name regardless of if there are 1 or no spaces and return the values

thanks

Dim y As Integer
Dim x As Integer
Application.ScreenUpdating = False
y = 3
x = 2
Do
x = x + 1
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
If Range("A1") = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("AC" & x) Or Range("B1") = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("AC" & x) Then
Range("C" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("AC" & x).Value
Range("A" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("B" & x).Value
Range("B" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("AB" & x).Value
Range("D" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("AD" & x).Value
Range("E" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("F" & x).Value
Range("F" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("G" & x).Value
Range("G" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range(" I" & x).Value
Range("H" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("H" & x).Value
Range("I" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("R" & x).Value
Range("I" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("R" & x).Value
Range("J" & y) = Workbooks("Inventory.xlsx").Sheets("Sheet1").Range("S" & x).Value
<o:p></o:p>
<o:p></o:p>
y = y + 1
End If
Loop Until x = 4000

<!-- / message --><!-- edit note -->

 

[Ralajer]

The easiest way is to normalize your strings by replacing all the spaces with the empty string. Here is your code to include the replace function and some minor other tweeks.

Dim y As Integer
Dim x As Integer
[COLOR="red"]Dim testStr As String[/COLOR]

Application.ScreenUpdating = False


[COLOR="Red"]With Workbooks("Inventory.xlsx").Sheets("Sheet1")[/COLOR]


'\\ Pulled out Range("A1") of Do loop as it is constant over the loop and
'\\    doesn't need to be looked up each time through
'\\ Replace all spaces with empty string normalizing input
[COLOR="red"]testStr = Replace(Range("A1"), " ", "")[/COLOR]


y = 3
x = 2


Do
x = x + 1

[COLOR="red"]    If testStr = Replace(.Range("AC" & x), " ", "") Then[/COLOR]        

        Range("C" & y) = .Range("AC" & x).Value
        Range("A" & y) = .Range("B" & x).Value
        Range("B" & y) = .Range("AB" & x).Value
        Range("D" & y) = .Range("AD" & x).Value
        Range("E" & y) = .Range("F" & x).Value
        Range("F" & y) = .Range("G" & x).Value
        Range("G" & y) = .Range(" I" & x).Value
        Range("H" & y) = .Range("H" & x).Value
        Range("I" & y) = .Range("R" & x).Value
        Range("I" & y) = .Range("R" & x).Value
        Range("J" & y) = .Range("S" & x).Value
        
        y = y + 1
    End If

Loop Until x = 4000

[COLOR="red"]End With[/COLOR]

Let me know if you have questions.

 

[jacinthn]

<P>thanks so much Rob will test it out at work on tuesday!!</P>