Lookup? Find? How?

[Steve]

I am importing data from tables into Excel from the
internet using the Web Query function. That part is no
problem. I'm testing this using football scores...my data
is actually very similar in format . Below is an
example:

SE MISSOURI 3
OHIO 17
Box Score

FINAL
EAST TENN. ST. 21
E. MICHIGAN 28


What I am looking for is a function that can find the
number that goes along with the text and put it into a
specific cell. i.e. Say I want SE Missouri's score to be
in cell A1. When you do a web query, you never know which
cell SE Missouri's score will show up in (web sites change
constantly). So I want the sheet to look for the text 'SE
Missouri' and pick off the number in the next cell. Can
anyone help?

Thanks,

Steve
(e-mail address removed)

 

[Don Guillett]

Look in VBA HELP index for FIND for a macro to do this.

 

[Don Guillett]

OK. If you can't find vba HELP here is a sub to do what you want
Sub findSEmissouri()
[a1] = Cells.find("SE Missouri").Offset(, 1)
End Sub

 

[Don Guillett]

To find each in a list and put the score to the right cell use this assigned
to a button.

Sub findemall()
For Each c In Selection ' your typed list
c.Offset(, 1) = Cells.find(c).Offset(, 1)
Next
End Sub

 

[Steve F]

Thank you! That script worked. Now I just have to make
the same thing for about 50 different teams. Is there a
way to make these macros so that they run automatically
when you open the spreadsheet, or do I have to
individually run each macro every time I want to have it
update?

-----Original Message-----
OK. If you can't find vba HELP here is a sub to do what you want
Sub findSEmissouri()
[a1] = Cells.find("SE Missouri").Offset(, 1)
End Sub

I am importing data from tables into Excel from the
internet using the Web Query function. That part is no
problem. I'm testing this using football scores...my data
is actually very similar in format . Below is an
example:

SE MISSOURI 3
OHIO 17
Box Score

FINAL
EAST TENN. ST. 21
E. MICHIGAN 28


What I am looking for is a function that can find the
number that goes along with the text and put it into a
specific cell. i.e. Say I want SE Missouri's score to be
in cell A1. When you do a web query, you never know which
cell SE Missouri's score will show up in (web sites change
constantly). So I want the sheet to look for the text 'SE
Missouri' and pick off the number in the next cell. Can
anyone help?

Thanks,

Steve
(e-mail address removed)

.

 

[Don Guillett]

Well, if you are using that method to get the data a worksheet_change event
might do it.
See my comphrensive answer in my last post. This could be put in the event
macro.

Thank you! That script worked. Now I just have to make
the same thing for about 50 different teams. Is there a
way to make these macros so that they run automatically
when you open the spreadsheet, or do I have to
individually run each macro every time I want to have it
update?

-----Original Message-----
OK. If you can't find vba HELP here is a sub to do what you want
Sub findSEmissouri()
[a1] = Cells.find("SE Missouri").Offset(, 1)
End Sub

I am importing data from tables into Excel from the
internet using the Web Query function. That part is no
problem. I'm testing this using football scores...my data
is actually very similar in format . Below is an
example:

SE MISSOURI 3
OHIO 17
Box Score

FINAL
EAST TENN. ST. 21
E. MICHIGAN 28


What I am looking for is a function that can find the
number that goes along with the text and put it into a
specific cell. i.e. Say I want SE Missouri's score to be
in cell A1. When you do a web query, you never know which
cell SE Missouri's score will show up in (web sites change
constantly). So I want the sheet to look for the text 'SE
Missouri' and pick off the number in the next cell. Can
anyone help?

Thanks,

Steve
(e-mail address removed)

.