The sub below gives the result I wanted but when the workbook is
opened it comes up with an error 9 message.
I've tried to track it down but to success so far; any help woul dbe
appreciated.

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

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

Mike S wrote:
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?

ary(lr-0)=lr*4

Apologies, I made a mistake in the line above, -0 should be -1,

Public Sub ReplaceDotDelimiter()
'sub to add back in dot delimiters previously removed
Dim ary(9) As Variant ' or Dim ary(0 to 8) 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-1)=lr*4 'assigns values to elements 0 to 8
next
'if you really meant 37 instead of 36 fix that here
ary(9)=37
'does that fix the error?
'the rest is unchanged
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

On Aug 8, 6:44*am, mscir wrote:
Mike S wrote:
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?
* * * ary(lr-0)=lr*4

Apologies, I made a mistake in the line above, -0 should be -1,

Public Sub ReplaceDotDelimiter()
* * *'sub to add back in dot delimiters previously removed
* * *Dim ary(9) As Variant ' or Dim ary(0 to 8) 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-1)=lr*4 'assigns values to elements 0 to 8
* * *next
* * *'if you really meant 37 instead of 36 fix that here
* * *ary(9)=37
* * *'does that fix the error?
* * *'the rest is unchanged
* * * * *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- Hide quoted text -

- Show quoted text -

Thanks Mike
It still comes up with the error 9 message.
yes I do mean 37, the code you've scripted looks tidier but adds two
dots at position 37.
i tried changing ary(9)=37 to ary(8) =37 this would be element 9 if
the Base is 0, but it didn't run.
My orignal code did the trick just left me with the annoying message.
The code below also works in terms of putting the dots back in the
right place, but also leaves me with error 9 when the workbook is
loaded.
Spmething is going wrong on loading the worklbook, before even trying
to run the procedure?

Public Sub ReplaceDotDelimiter2()
'sub to add back in dot delimiters previously removed
Dim ary As Variant
Dim sval As Variant
Dim i, lr As Integer

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

Write Option Base 0 at the start of module

or

declare array

ary( 0 to 8 ) as Variant

Hope this will solve.