Using FormulaR1C1 to find fields begining with spaces

Good Day

I'm writing a Macro that must find all fields that start with a space and move them to another column depending on the number of spaces.

Eg.from this

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

I'm thinking of something like this in terms of code:

Please Login or Register  to view this content.

Thanks for your help:

Poppy,
Why are there 2 x Veggies, but 3 x fruits and 5 x Fridge ?

NickHK

"poppy" <[email protected]> wrote in
message news:[email protected]...
>
> Good Day
>
> I'm writing a Macro that must find all fields that start with a space
> and move them to another column depending on the number of spaces.
>
> Eg.from this
>
> Code:
> --------------------
>
> Carrots 6
> Onions 7
> Veggies 13
> Bananas 5
> Oranges 9
> Apples 2
> Fruits 16
> Fridge 29
>
> --------------------
>
>
> To this:
>
> Code:
> --------------------
>
> Carrots 6 Veggies 13 Fridge 29
> Onions 7 Veggies 13 Fridge 29
> Bananas 5 Fruits 16 Fridge 29
> Oranges 9 Fruits 16 Fridge 29
> Apples 2 Fruits 16 Fridge 29
>
> --------------------
>
>
> I'm thinking of something like this in terms of code:
>
>
> Code:
> --------------------
>
> Sheets("Sheet1").Select
>
> ' Start moving fields
> ActiveWorkbook.Names.Add Name:="Start", RefersTo:=Range("A6")
> Range("Start").Select
>
> Do
> If ActiveCell.FormulaR1C1 = " " & (Whatever is contained in the
field) Then
> Range(ActiveCell, ActiveCell.End(xlToRight)).Select
> Selection.Cut
> End If
>
>
> ActiveCell.Offset(1, 0).Activate
>
> Loop Until ActiveCell.Value = ""
>
> --------------------
>
>
> Thanks for your help:
>
>
> --
> poppy
> ------------------------------------------------------------------------
> poppy's Profile:
http://www.excelforum.com/member.php...o&userid=11453
> View this thread: http://www.excelforum.com/showthread...hreadid=558331
>

Hi Nick

Those are levels. Carrots and Onions roll into Veggies. Bananas, Oranges and Apples roll into Fruits and finally they all roll into Fridge which is the highest level.

I hope that makes sense?

Kind Regards

Hi

Maybe I haven't explained myself properly. I'm basically trying to find the number of spaces contained in a word:

eg. (" Jean") I want to be able to say that if the value of the activecell has 3 spaces at the begining of it then....else....

I think it would be along the lines of this piece of code (ActiveCell.FormulaR1C1 = "=LEFT(R[2]C, FIND(""2"",R[2]C)-1)") except that I dont want the formula to be dumped in the activecell.

if ActiveCell.value has 3 spaces in front of it then
...
else

if ActiveCell.value = 5 then
....

else

if ActiveCell.value = 7 then
....
end if
etc...


Hope this explains it a bit more. Thanks for all your help in advance

Kind Regards