Create if partial match then copy and paste data from one cell to another

[theaxemonkey]

Hello,



Hopefully, I can explain this well which is probably half the challenge. What I am trying to accomplish is a bit complicated.
For the purpose of simplicity I will
ask how I can achieve X without going into too much detail, then I will explain Y which is exactly what I am wanting to accomplish. Finally Z the complications of what I am trying to do



X / How can I create and if statement that says IF cell A2 (sheet2) finds a partial match in range A:A (sheet1) then copy data from cell B2(sheet2)and paste into cell B2 (sheet 1).




Y/ I have a WB with multiple sheets. I have a Feed sheet that automaticallypulls data from the web. I have a Scores sheet that currently has all headto head matchups each week for an entire season. So I want to check the a cell on the Feed sheet to find a partial match on the scores sheet and if partial match exists then copy a particular cell that contains the score (from the feed sheet) and paste it to the score sheet (in the cell next to thecorresponding team)
In Plain English (hopefully)...


I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D(rangeD2257) BUT I need to search by column not row then once it is found check the cell to the immediate right and only if the cell is blank copy data in H3 (!feed) to (!scores) to the right cell...

This is what happens if working right =

look at cell A3 (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row, finds "NY JET" as match in cell D8, if cell E8 is blank then copies cell H3 (!feed) sheet, and paste to cell E8 (!scores), if cell is not blank continues search until blank cell to the right of matching cell is found. Once this is done it moves on to the next row.
look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)




Z/ A complication is that team names are repeated over and over on the score sheet (the feed sheet is overwritten each week) so the search needs to check cells by row (not by column) to check cell B2 then cell D2 then move on to B3 then D3 then B4 and D4 and so on to find the first match then checkto see if the cell to the right is blank. Once the match is made and the score is copied and pasted I need to move to the next cell on the feed sheetto start the search process over again checking Cell A3 to A74, and if a match is not found move on to the next cell and search. For example Cell A3 + A4 have team names matches should be found but cell A5 is blank A6 say Game Final and A7 is blank

 

[Auric__]

Hopefully, I can explain this well which is probably half the
challenge. What I am trying to accomplish is a bit complicated.
For the purpose of simplicity

This is "simplicity"?

[snip]

This is what happens if working right
look at cell A3 (!feed) sheet = "New York Jets" then search (!scores)
sheet Column B and Column D by row, finds "NY JET" as match in cell D8,
if cell E8 is blank then copies cell H3 (!feed) sheet, and paste to cell
E8 (!scores), if cell is not blank continues search until blank cell to
the right of matching cell is found. Once this is done it moves on to
the next row.
look at cell A4 (!feed)= "New England Patriots" then search (!scores)
sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell
B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to
cell C8 (!scores)

The only suggestion I have at the moment is to build a Collection:

Dim Teams As Collection
Set Teams = New Collection
' scores sheet feed sheet
Teams.Add "NY JETS", "New York Jets"
Teams.Add "NEW ENGLAND", "New England Patriots"
'etc.

Then, when your code finds a match from the feed, it can get the info it
needs to find on the scores sheet like this:

infoToFind$ = Teams.Item("New York Jets")

This probably won't magically solve all your problems, but it *might* help.