Offset.Value

[John]

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]

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.

 

[Tom Ogilvy]

Or you could skip counting the dots and let them represent themselves.

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 r As Integer
Dim c As Integer
Dim Answer 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
With rgListItem.Offset(0, 1)
.Value = Replace(rgListItem.Text,"."," ") & .Value
' or for xl97
' .Value = Application _
' .Substitute(rgListItem.Text,"."," ") & .Value
End With
r = r + 1
Loop

Cells(r, c).Select

MsgBox "Finished"

End Sub

 

[John]

Dave,

Thanks very much for this. I can see now that I was using the r and c
values rather than a nominal offset value as you have done. Also you
suggestion to place the getting and setting string convStr outside the With
Y seems much more sensible.

Thanks very much for your time.

Best regards

John

 

[John]

Hi Tom,

Thanks for your second reply today!

Yes, I see what you mean about the dots, however as I read it, however, I
only want to indent the items in column B. The other values in the "dotted"
string are not required.

Anyway, thanks again and I will certainly use the Replace function in
future.

Best regards

John

 

[Tom Ogilvy]

Guess I missunderstood and thought there were only dots in the source
range - that answers why Dave spent time counting them.