Using FormulaR1C1 to find fields begining with spaces

[poppy]

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 = ""

 

[NickHK]

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

NickHK

 

[poppy]

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

 

[poppy]

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