On 8/7/2010 12:09 PM, Snuffwinkler wrote:
Sub ReplaceDotDelimiter()
'sub to add back in dot delimiters previously removed

Dim ary(8) As Variant
Dim sval As Variant
Dim i, lr As Integer
ary(0) = 4
ary(1) = 8
ary(2) = 12
ary(3) = 16
ary(4) = 20
ary(5) = 24
ary(6) = 28
ary(7) = 32
ary(8) = 37

Sheets("Data_for_RIB_Import_Convertor").Select
lr = Cells(Rows.Count, 1).End(xlUp).Row
For Each Cell In Range("A3:a"& lr)
sval = Cell.Value
'ary = Array(4, 8, 12, 16, 20, 24, 28, 32, 37)
For i = LBound(ary) To UBound(ary)
sval = Left(sval, ary(i) - 1)& "."& Right(sval,
Len(sval) - ary(i) + 1)
Next i
Cell.FormulaR1C1 = sval
Next

Range("a3").Select

End Sub

What happens when you run this?

Public Sub ReplaceDotDelimiter()
'sub to add back in dot delimiters previously removed
Dim ary(9) As Variant
'0 counts as 1 element, 1-8 = 8 elements, so you need 9
Dim sval As Variant, i as long, lr As long
for lr=1 to 9
ary(lr-0)=lr*4
next
'if you really meant 37 instead of 36 add one more
ary(9)=37
'does that fix things?

Sheets("Data_for_RIB_Import_Convertor").Select
lr = Cells(Rows.Count, 1).End(xlUp).Row
For Each Cell In Range("A3:a" & lr)
sval = Cell.Value
'ary = Array(4, 8, 12, 16, 20, 24, 28, 32, 37)
For i = LBound(ary) To UBound(ary)
sval = Left(sval, ary(i) - 1) & "." & Right(sval,
Len(sval) - ary(i) + 1)
Next i
Cell.FormulaR1C1 = sval
Next
Range("a3").Select

End Sub