error 9 - related to an array - adding delimiters back to a cellvalue

[Snuffwinkler]

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

 

[Mike S]

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

 

[mscir]

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

 

[Snuffwinkler]

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

 

[Javed]

Write Option Base 0 at the start of module

or

declare array

ary( 0 to 8 ) as Variant

Hope this will solve.