Another frustrating Excel problem

[daveallston]

Hi.
I'm having an incredibly annoying problem with Excel again.

I'm trying to do some basic lookup spreadsheets, for hockey statistics
purposes. I have a multi-tab book, and my goal is to have a couple of
tabs to dump raw data in from a database on the net, and then the main
tabs will automatically pull and work with the data i dump in to the 2
raw tabs.

I have it all set and working in every way except for one bloody tab,
and its driving me nuts. I have some really basic information dropped
in to the sheet. Essentially column C contains the players names, and
then columns G H and I contain injury information. I have a similar tab
where column C contains the players names, and columns M N O P, etc.
contain stats.

The data comes in to the "injuries" tab the same way it does for the
"stats" tab. It drops perfectly in to the spreadsheet, and the formulas
are perfect, such that if a player matches info in the "main" tab with
a cell in the "injuries" tab it brings over the contents of columns G H
and I in to cells in the "main" tab. Pretty standard stuff.

I'm bringing in data from the same website, and for the stats part, it
worked fine. All the stats copy over to the "main" tab, and its fine.
But for the injuries, it won't do it! But yet, if I type the players
name directly over where the data was dropped from the net, spelled the
same way, no extra spaces at start or end, etc. it then works! (in
columns AE, AF and AG). So I know its not a formula problem or a
linkage problem. Its something in the way the data is set when its
sitting in the "injuries" tab in its raw form. If I simply write over
it EXACTLY what it already contains, it works fine. So what can I do to
automate this, or to fix this problem? I don't want to have to write
over all the names every day when I do a fresh data dump from the
net....

Please help! Thanks so much for your help.
If it helps, you can see the document I'm working with (its in its very
basic stages of being put together, but for the purpose of you being
able to see what i'm doing, it might help). The link to the doc is
http://www.westborogamblers.com/testinga.xls

Also note that the "injuries" data I'm putting into it comes directly
from the page http://www.sportsline.com/nhl/injuries

Thanks a lot!
Dave
(e-mail address removed)

 

[Gord Dibben]

Perhaps a non-breaking space that you can't see.

Try David McRitchie's TRIMALL macro on your names.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP