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