I've got two spreadsheets that have a column with the same data, more or less.
i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester "

so i've got this bit of macro
where sitename comes from spreadsheet 1 and is a string and
osheet.Range("A" & counter) comes from spreadsheet 2.

For counter = 1 To 50 Step 1

If sitename = osheet.Range("A" & counter) Then

do stuff

Next counter

The problem is that with the example above, i.e. spreadsheet 1 has
"Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true

Is there any way of getting around this.

Maybe removing the empty spaces at the end of a cell?

TIA

trim(sitename) does the trick

google is my friend I guess

"A1pro" wrote:

> I've got two spreadsheets that have a column with the same data, more or less.
> i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester "
>
> so i've got this bit of macro
> where sitename comes from spreadsheet 1 and is a string and
> osheet.Range("A" & counter) comes from spreadsheet 2.
>
> For counter = 1 To 50 Step 1
>
> If sitename = osheet.Range("A" & counter) Then
>
> do stuff
>
> Next counter
>
> The problem is that with the example above, i.e. spreadsheet 1 has
> "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true
>
> Is there any way of getting around this.
>
> Maybe removing the empty spaces at the end of a cell?
>
> TIA

Hi,

Maybe the following would give you some ideas

1. To ignore leading and trailing spaces:

If trim(sitename) = trim(osheet.Range("A" & counter))

2. To apply #1 AND ignore the case

If ucase(trim(sitename)) = ucase(trim(osheet.Range("A" & counter)))

3. If you want to ignore only the spaces at the end of the string, use Rtrim
and if you want to ignore only the spaces at the beginning, use Ltrim.




--
Hope that helps.

Vergel Adriano


"A1pro" wrote:

> I've got two spreadsheets that have a column with the same data, more or less.
> i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester "
>
> so i've got this bit of macro
> where sitename comes from spreadsheet 1 and is a string and
> osheet.Range("A" & counter) comes from spreadsheet 2.
>
> For counter = 1 To 50 Step 1
>
> If sitename = osheet.Range("A" & counter) Then
>
> do stuff
>
> Next counter
>
> The problem is that with the example above, i.e. spreadsheet 1 has
> "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true
>
> Is there any way of getting around this.
>
> Maybe removing the empty spaces at the end of a cell?
>
> TIA

Try this
If sitename = osheet.clean(Range("A" & counter)) Then

"A1pro" wrote:

> I've got two spreadsheets that have a column with the same data, more or less.
> i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester "
>
> so i've got this bit of macro
> where sitename comes from spreadsheet 1 and is a string and
> osheet.Range("A" & counter) comes from spreadsheet 2.
>
> For counter = 1 To 50 Step 1
>
> If sitename = osheet.Range("A" & counter) Then
>
> do stuff
>
> Next counter
>
> The problem is that with the example above, i.e. spreadsheet 1 has
> "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true
>
> Is there any way of getting around this.
>
> Maybe removing the empty spaces at the end of a cell?
>
> TIA

Joel,

Just a heads up -

Clean only does non-printing characters (and i suspect only those below
128).

a = " a "
? len(a)
3
? len(Application.Clean(a))
3
? len(trim(a))
1

(it doesn't clean chr(160), "nbsp" often copied in from the internet. as an
example)

--
Regards,
Tom Ogilvy


"Joel" wrote:

> Try this
> If sitename = osheet.clean(Range("A" & counter)) Then
>
> "A1pro" wrote:
>
> > I've got two spreadsheets that have a column with the same data, more or less.
> > i.e. spreadsheet 1 has "Gloucester" and the spreadsheet 2 "Gloucester "
> >
> > so i've got this bit of macro
> > where sitename comes from spreadsheet 1 and is a string and
> > osheet.Range("A" & counter) comes from spreadsheet 2.
> >
> > For counter = 1 To 50 Step 1
> >
> > If sitename = osheet.Range("A" & counter) Then
> >
> > do stuff
> >
> > Next counter
> >
> > The problem is that with the example above, i.e. spreadsheet 1 has
> > "Gloucester" and the spreadsheet 2 "Gloucester ", the if will never be true
> >
> > Is there any way of getting around this.
> >
> > Maybe removing the empty spaces at the end of a cell?
> >
> > TIA