Do Loop and data formatting question
 

First I am getting a file and all the data is text. Is there a way to
convert the Text to Numbers?

What I am trying to do is generate a indented Bill of Material. The
report I received has levels in Column "B". I would like to indent the
cell in column "B" based on the level that the item report to. I am
trying to do a conditional do loop but I am get error with the
"Selection.InsertIndent 1". I have also added a X=Abs(Range("B" &
i).Select) or something to this affect and did a
Selection.InsertIndent X to try and keep the coding simple to no
luck.. Can someone help?



i = 4
Do
Range("B" & i).Select
If Range("B" & i).Select = -1 Then
Selection.InsertIndent 1
Else
Selection.InsertIndent 4
i = i + 1
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Do Loop and data formatting question
 

You shouldn't have to select the cells. Try this
Sub loop()
i = 4
Do While Len(Range("B" & i).Formula) 0
If Range("B" & i).Value = -1 Then
Range("B" & i).InsertIndent 1
Else
Range("B" & i).InsertIndent 4
End If
i = i + 1
Loop
End Sub

"B~O~B" wrote:

First I am getting a file and all the data is text. Is there a way to
convert the Text to Numbers?

What I am trying to do is generate a indented Bill of Material. The
report I received has levels in Column "B". I would like to indent the
cell in column "B" based on the level that the item report to. I am
trying to do a conditional do loop but I am get error with the
"Selection.InsertIndent 1". I have also added a X=Abs(Range("B" &
i).Select) or something to this affect and did a
Selection.InsertIndent X to try and keep the coding simple to no
luck.. Can someone help?



i = 4
Do
Range("B" & i).Select
If Range("B" & i).Select = -1 Then
Selection.InsertIndent 1
Else
Selection.InsertIndent 4
i = i + 1
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Do Loop and data formatting question
 

On Jul 11, 4:24*pm, Mike wrote:
You shouldn't have to select the cells. Try this
Sub loop()
* * i = 4
* * Do While Len(Range("B" & i).Formula) 0
* * If Range("B" & i).Value = -1 Then
* * *Range("B" & i).InsertIndent 1
* * Else
* * *Range("B" & i).InsertIndent 4
* * End If
* * i = i + 1
* * Loop
End Sub



"B~O~B" wrote:
First I am getting a file and all the data is text. *Is there a way to
convert the Text to Numbers?

What I am trying to do is generate a indented Bill of Material. *The
report I received has levels in Column "B". I would like to indent the
cell in column "B" based on the level that the item report to. *I am
trying to do a conditional do loop but I am get error with the
"Selection.InsertIndent 1". * I have also added a X=Abs(Range("B" &
i).Select) or something to this affect and did a
Selection.InsertIndent X to try and keep the coding simple to no
luck.. *Can someone help?

i = 4
* * Do
* * Range("B" & i).Select
* * If Range("B" & i).Select = -1 Then
* * Selection.InsertIndent 1
* * Else
* * Selection.InsertIndent 4
* * i = i + 1
End If
* * Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub- Hide quoted text -

- Show quoted text -

No luck... I get a syntax error with the "Sub Loop()" if i remove it
then the macro does noting...

Do Loop and data formatting question
 

sorry bob try this
Sub test()
i = 4
Do While Len(Range("B" & i).Formula) 0
If Range("B" & i).Value = -1 Then
Range("B" & i).InsertIndent 1
Else
Range("B" & i).InsertIndent 4
End If
i = i + 1
Loop
End Sub

"B~O~B" wrote:

On Jul 11, 4:24 pm, Mike wrote:
You shouldn't have to select the cells. Try this
Sub loop()
i = 4
Do While Len(Range("B" & i).Formula) 0
If Range("B" & i).Value = -1 Then
Range("B" & i).InsertIndent 1
Else
Range("B" & i).InsertIndent 4
End If
i = i + 1
Loop
End Sub



"B~O~B" wrote:
First I am getting a file and all the data is text. Is there a way to
convert the Text to Numbers?

What I am trying to do is generate a indented Bill of Material. The
report I received has levels in Column "B". I would like to indent the
cell in column "B" based on the level that the item report to. I am
trying to do a conditional do loop but I am get error with the
"Selection.InsertIndent 1". I have also added a X=Abs(Range("B" &
i).Select) or something to this affect and did a
Selection.InsertIndent X to try and keep the coding simple to no
luck.. Can someone help?

i = 4
Do
Range("B" & i).Select
If Range("B" & i).Select = -1 Then
Selection.InsertIndent 1
Else
Selection.InsertIndent 4
i = i + 1
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub- Hide quoted text -

- Show quoted text -

No luck... I get a syntax error with the "Sub Loop()" if i remove it
then the macro does noting...

Do Loop and data formatting question
 

On Jul 12, 1:41*pm, Mike wrote:
sorry bob try this
Sub test()
* * i = 4
* * Do While Len(Range("B" & i).Formula) 0
* * If Range("B" & i).Value = -1 Then
* * *Range("B" & i).InsertIndent 1
* * Else
* * *Range("B" & i).InsertIndent 4
* * End If
* * i = i + 1
* * Loop
End Sub



"B~O~B" wrote:
On Jul 11, 4:24 pm, Mike wrote:
You shouldn't have to select the cells. Try this
Sub loop()
* * i = 4
* * Do While Len(Range("B" & i).Formula) 0
* * If Range("B" & i).Value = -1 Then
* * *Range("B" & i).InsertIndent 1
* * Else
* * *Range("B" & i).InsertIndent 4
* * End If
* * i = i + 1
* * Loop
End Sub

"B~O~B" wrote:
First I am getting a file and all the data is text. *Is there a way to
convert the Text to Numbers?

What I am trying to do is generate a indented Bill of Material. *The
report I received has levels in Column "B". I would like to indent the
cell in column "B" based on the level that the item report to. *I am
trying to do a conditional do loop but I am get error with the
"Selection.InsertIndent 1". * I have also added a X=Abs(Range("B" &
i).Select) or something to this affect and did a
Selection.InsertIndent X to try and keep the coding simple to no
luck.. *Can someone help?

i = 4
* * Do
* * Range("B" & i).Select
* * If Range("B" & i).Select = -1 Then
* * Selection.InsertIndent 1
* * Else
* * Selection.InsertIndent 4
* * i = i + 1
End If
* * Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub- Hide quoted text -

- Show quoted text -

No luck... *I get a syntax error with the "Sub Loop()" if i remove it
then the macro does noting...- Hide quoted text -

- Show quoted text -

Mike,

If worked. Thanks all you help.