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

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

Using FormulaR1C1 to find fields begining with spaces
 

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

NickHK

"poppy" wrote in
message ...

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

Using FormulaR1C1 to find fields begining with spaces
 

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
------------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=558331

Using FormulaR1C1 to find fields begining with spaces
 

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


--
poppy
------------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=558331