Checking for spaces at beginning of string 1

[nwallac1]

I am having a problem using the left function to check for spaces at the beginning of a string. using this code:

If Left(ActiveCell, 5) = " " Then activecell.offset(1,0).activate

I am trying to skip any string that begins with 5 spaces. The code above does not seem to work to accomplish this goal. Does anyone have any suggestions about how to better go about this?

 

[Bong]

How about using LTrim:

if len(activecell.txt)-len(LTrim(activecell.text))=5 then

_________________
Bob Rashkin

 

[MakeItSo]

Use "Select" rather than "activate".



[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[yogia]

Hi nwallac1:

It worked for me with 5 spaces within the quotes ... so make sure there are 5 spaces within the quote marks.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Bob Rashkin:

Just curious ... Don't we simply need ...

if len(LTRIM(activecell))= 5 then

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[MakeItSo]

Hi Yogi!

Because the length of an ltrimmed string with 5 consecutive spaces will be zero 0 than 5.
In any case: it is the "activate" that won't work. His if-statement functions.
;-)

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[nwallac1]

While using ltrim would be nice, it is not a possibility in this case. I am trying to eliminate those cells that begin with 5 or more spaces from my search. I chose 5 as a safe number that would distinguish the cells I want from the cells I do not want. thus, I am still having many problems.

 

[SkipVought]

I would not use Select either. Using the Select or Activate methods is not a good programming practice for range referencing.

Skip,

_[red][/red]

 

[MakeItSo]

I am trying to eliminate those cells that begin with 5 or more spaces from my search.

Ah yeah! Finally some more info.
Keep it rolling, and you might not need five spaces in the first place!


==> What are you trying to do and can't these unwanted cells not be distinguished in any other way?

==> With a search, you don't want to actually activate any cell anyway, do you?

Hence, a simple

dim ran as range, c as cell
set ran=Selection.Find(What:=.......).Activate
for each c in ran
 if not left(c,5)="     " then
   'do your thing
 end if
next c

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[SkipVought]

set ran = [b]ActiveSheet.Cells[/b].Find(What:=.......)   [red][S][b].Activate[/b][/s][/red]
...

Skip,

_[red][/red]

 

[fumei]

Skip, you sound like me regarding using Selection in Word.




Um, that may not be a compliment.

Gerry
My paintings and sculpture

 

[MakeItSo]

Ooopsie!. Thanks Skip. No activate there of course...

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[SkipVought]

Yup, Gerry. Hate to sound like a broken record, but...

Do kids today know what a broken record sounds like?

Skip,

_[red][/red]