If statements based around Find results

[Fellhouse]

Hello,

I am trying to create some if statements based around find method results. In essence, I am trying to find an exact term in sheet of data and then if it is found copy the data directly to the left of it as a variable and then post it on another sheet. If it doesn't find the exact term, I want it to go to the next term search.

Here is an example of what I have so far:

AttName = "Part Number:"
FindRoutine = Cells.Find(What:=AttName, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If FindRoutine = True Then

ActiveCell.Offset(0, 1).Range("a1").Select
AttValue = ActiveCell.Value
Sheets("Reference Data").Select
Cells.Find(What:=AttName, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(RowCount - 1, 0).Range("a1").Select
ActiveCell.Value = AttValue
Sheets("Web Query Data").Select
Application.Goto reference:="R2C1"

Else: End If


AttName = "Model Year:"
FindRoutine = Cells.Find(What:=AttName, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If FindRoutine = True Then

ActiveCell.Offset(0, 1).Range("a1").Select
AttValue = ActiveCell.Value
Sheets("Reference Data").Select
Cells.Find(What:=AttName, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(RowCount - 1, 0).Range("a1").Select
ActiveCell.Value = AttValue
Sheets("Web Query Data").Select
Application.Goto reference:="R2C1"

Else: End If

Ultimately I would want it to move from the "Part Number:" search to the "Model Year:" search if there were no results on the "Part Number:" search.

Anyone have an idea?

Thanks in advance!

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copy_AttName()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> wsWQData <SPAN style="color:#00007F">As</SPAN> Worksheet, wsRef <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> FoundWQD <SPAN style="color:#00007F">As</SPAN> Range, FoundRef <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> AttName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsWQData = Sheets("Web Query Data") <SPAN style="color:#007F00">'Source worksheet</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsRef = Sheets("Reference Data")    <SPAN style="color:#007F00">'Destination worksheet</SPAN><br>    <br>    <SPAN style="color:#007F00">' Loop for each AttName in array</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> AttName <SPAN style="color:#00007F">In</SPAN> Array("Part Number:", "Model Year:")<br>    <br>        <SPAN style="color:#007F00">'Reset .Find results</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> FoundWQD = Nothing: <SPAN style="color:#00007F">Set</SPAN> FoundRef = <SPAN style="color:#00007F">Nothing</SPAN><br>        <br>        <SPAN style="color:#007F00">' Fine Attname on Source worksheet</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> FoundWQD = wsWQData.Cells.Find(What:=AttName, _<br>                                           LookIn:=xlFormulas, _<br>                                           LookAt:=xlWhole, _<br>                                           SearchOrder:=xlByRows, _<br>                                           SearchDirection:=xlNext, _<br>                                           MatchCase:=False)<br>                               <br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> FoundWQD <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">' If AttName found on Source</SPAN><br>            <SPAN style="color:#007F00">' then find it on Destination worksheet</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> FoundRef = wsRef.Cells.Find(What:=AttName, _<br>                                            LookIn:=xlFormulas, _<br>                                            LookAt:=xlWhole, _<br>                                            SearchOrder:=xlByRows, _<br>                                            SearchDirection:=xlNext, _<br>                                            MatchCase:=False)<br>                                            <br>             <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> FoundRef <SPAN style="color:#00007F">Is</SPAN> Nothing <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">' If AttName on both worksheets then copy data</SPAN><br>                <SPAN style="color:#007F00">' Not sure where you wanted to copy to (rowcount?)</SPAN><br>                FoundRef.Offset(1, 0).Value = FoundWQD.Offset(0, 1).Value<br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>    <SPAN style="color:#007F00">'Exit loop</SPAN><br>             <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> AttName<br>    <br>    MsgBox "Done"<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Fellhouse]

Thanks a ton! This is killer!