I think that you're touching the .offset() twice.

How about:

Option Explicit

Sub IndentListWithSpaces()

'Run down list and indent cell values to right,
'dependent on number of dots in string

Dim rgListItem As Range
Dim convStr As String
Dim nDots As Integer
Dim r As Integer
Dim c As Integer
Dim totalStr As String
Dim Answer As Long
Dim X As Long
Dim Y As Long

'Check user selects cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of list selected?", _
Buttons:=vbYesNo + vbQuestion)
If Answer = vbNo Then Exit Sub

r = ActiveCell.Row
c = ActiveCell.Column

Do
Set rgListItem = Cells(r, c)
If IsEmpty(Cells(r, c)) Then Exit Do
totalStr = rgListItem.Value
nDots = 0
For X = 1 To Len(totalStr)
If Mid(totalStr, X, 1) = "." Then
nDots = nDots + 1
End If
Next X

convStr = rgListItem.Offset(0, 1).Value
For Y = 1 To nDots
convStr = " " & convStr
Next Y
rgListItem.Offset(0, 1).Value = convStr

r = r + 1

Loop

Cells(r, c).Select

MsgBox "Finished"

End Sub

Another way:

In a cell, you can use a formula like:

=len(a1)-substitute(a1,".","")
to find the number of dots.

In VBA (xl2k or higher), you can use
nDots = Len(rgListItem.Value) _
- Len(replace(rgListItem.Value, ".", ""))
or in xl97:
nDots = Len(rgListItem.Value) _
- Len(Application.Substitute(rgListItem.Value, ".", ""))

So the code becomes:

Option Explicit

Sub IndentListWithSpaces()

'Run down list and indent cell values to right,
'dependent on number of dots in string

Dim rgListItem As Range
Dim convStr As String
Dim nDots As Integer
Dim r As Integer
Dim c As Integer
Dim totalStr As String
Dim Answer As Long
Dim X As Long
Dim Y As Long

'Check user selects cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of list selected?", _
Buttons:=vbYesNo + vbQuestion)
If Answer = vbNo Then Exit Sub

r = ActiveCell.Row
c = ActiveCell.Column

Do
Set rgListItem = Cells(r, c)
If IsEmpty(Cells(r, c)) Then Exit Do
nDots = Len(rgListItem.Value) _
- Len(Application.Substitute(rgListItem.Value, ".", ""))
With rgListItem.Offset(0, 1)
.Value = Space(nDots) & .Value
End With
r = r + 1
Loop

Cells(r, c).Select

MsgBox "Finished"

End Sub

And space(#) repeats the space character # times.


John wrote:

OK, second string question this evening.

This procedure looks at the strings in column A (which have a series of dots
in them) and "should" add a space for every dot found to the front of the
string in the respective row in column B.

The problem is that I'm getting a blank value ("") for variable convStr even
though there is a string in the right place in column B.

Any clues?

Thanks again

John

Sub IndentListWithSpaces()

'Run down list and indent cell values to right, dependent on number of dots
in string
Dim rgListItem As Range
Dim convStr As String
Dim nDots As Integer
Dim r As Integer
Dim c As Integer
Dim totalStr As String

'Check user selects cell at top of list
Answer = MsgBox(Prompt:="Is cell at top of list selected?", Buttons:=vbYesNo
+ vbQuestion)
If Answer = vbNo Then Exit Sub

r = ActiveCell.Row
c = ActiveCell.Column

Do
Set rgListItem = Cells(r, c)
If IsEmpty(Cells(r, c)) Then Exit Do
totalStr = rgListItem.Value
nDots = 0
For x = 1 To Len(totalStr)
If Mid(totalStr, x, 1) = "." Then
nDots = nDots + 1
End If
Next x

For y = 1 To nDots
convStr = rgListItem.Offset(r, (c + 1)).Value
rgListItem.Offset(r, (c + 1)).Value = " " & convStr
Next y

r = r + 1

Loop

Cells(r, c).Select

MsgBox "Finished"

End Sub

--

Dave Peterson