Hi ExcelMonkey,

Try replacing:

If Not Suspectedrng = " " And Not
Application.WorksheetFunction.IsNumber(Suspectedrn g) Then

with:

If Not Suspectedrng = " " _
And Not Suspectedrng = vbNullString _
And Not Application.IsNumber(Suspectedrng) Then


---
Regards,
Norman


"ExcelMonkey" wrote in message
...
So I am confused. I effectively have this string:

$A7 $B$11 $I$2 $I$6 I6 1 3 3 $I$6 K$3

It clearly has spaces in it at variable lenghts. I want to split it up
into
segments. Then I want to step throught the array, if the substring is a
cell
address, I want to offset by 1 column if its not a cell address (a number
or
a space) I want to make it a space. The problem I am having is that when
I
step throught the array, I am getting space values for spaces that are ""
and
" ". I am not sure why this is. My goal was to split the string by each
into its consitutent substrings including spaces (i.e 0 = " ", 1 = $A7, 2
=
$B$11, 3 = " " etc). The code fails on the line Set r =
Range(Suspectedrng)
because some of the array elements return values of "". Why is this?

Sub Thing ()
Dim CurrentFormula As String
Dim SuspectedrngArray As Variant
Dim X As Integer
Dim r As Range
Dim RevisedRngRight As String
Dim PassedRange As String

CurrentFormula = $A7 $B$11 $I$2 $I$6 I6 1 3 3 $I$6 K$3
SuspectedrngArray = Split(Replace(CurrentFormula, " ", "# #"), "#")

For X = LBound(SuspectedrngArray) To UBound(SuspectedrngArray)
Suspectedrng = SuspectedrngArray(X)
'If cell address then offset otherwise, make " "
If Not Suspectedrng = " " And Not
Application.WorksheetFunction.IsNumber(Suspectedrn g) Then
Set r = Range(Suspectedrng)
RevisedRngRight = r.Offset(0, 1).Address
ElseIf RevisedRngRight = " " Then
RevisedRngRight = " "
Else
RevisedRngRight = Suspectedrng
End If
PassedRange = PassedRange & RevisedRngRight
Next

End Sub