Hi, Carlo, if you want to keep working on it that would be great. :) I would
be very interested in finding out what the solution is. Would it be helpful
if I sent you one of the files I've been experimenting with? That way you
would have a real example of what these letter/number combinations are like.
Just let me know. Thank you so much for your continued perseverance in this.
:)



"carlo" wrote:

On Dec 13, 7:46 pm, FJ wrote:
Hi, Carlo, I think you are right about the other macro. It does seem as
though the problem happens on entries with leading zeros. Good catch. :)

Anyway, I made the changes to the code that you indicated and got some
interesting results. Sometimes it gives a "Run-time error 13 type mismatch"
error and sometimes it gives a "Code execution has been interrupted" error
message. When I get the "Code execution has been interrupted" message if I
click "Continue" then I get the "Run-time error 13 type mismatch".

I know it's hard for you to reproduce these errors because you don't have
the same exact data as I do. I tried to find a pattern as to what entries
were correct and the ones that were incorrect, but I'm just not sure. It
seems as though it mostly did the ones with a few leading zeros correctly,
but not in all cases. For instance, it seemed to consolidate a group with
two leading zeros correctly but another group had three leading zeros and the
entries were not consolidated. And then it seemed to stop completely when it
got to an entry that started with seven leading zeros.

But then on a worksheet with different data, it did seem to consolidate the
data with three leading zeros correctly. But there were other errors and the
same error messages as above.

I guess it's also possible that I pasted something from the new code into
the wrong place in the existing code, although I think I put the pieces in
the right place.

Something else interesting that I noticed is that after I run the macro the
calculation option setting changes from "automatic" to "manual".

Anyway, as usual, I want to thank you for all your help. :) If you're tired
of modifying this macro I totally understand. The project we've been working
on is winding down (I hope) and we can finish the rest of the files manually
if we have to. I think the bulk of the work has already been done. I just
hate to take up any more of your time with this. You've saved me so much
time already that doing the last bit by hand won't be bad at all.



"carlo" wrote:
Hi FJ

I forgot one tiny little thing. Could you check this for me:
everytime the problem happens, are leading zeros involved, right?
for example: ABCDE00012345
i didn't see that coming, replace this function:
'----------------------------------------------------
Function returnConnection(strValue As String, Plus As Boolean) As
String

Dim a As Double
Dim c As Double
Dim b As String

For i = 1 To Len(strValue)
If IsNumeric(Mid(strValue, i, 1)) And Mid(strValue, i, 1) 0 Then
a = CDbl(Right(strValue, Len(strValue) - i))
b = Left(strValue, i)
If Plus Then
c = a + 1
Else
c = a - 1
End If

If Len(c) < Len(a) Then
b = Left(b, Len(b) - (Len(c) - Len(a)))
End If
returnConnection = b & c
Exit For
End If
Next i

End Function
'----------------------------------------------------

end for the speedproblem...i forgot to put in following pieces of code
at the beginning and end of sub main, directly after the sub main()
and before the sub end.
beginning:
'----------------------------------------------------
Dim Var_Calc As Variant
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Var_Calc = Application.Calculation
Application.Calculation = xlCalculationManual
'----------------------------------------------------

end:
'----------------------------------------------------
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = Var_Calc
'----------------------------------------------------
End Sub

tell me if it stills uses that much time.

Cheers Carlo- Hide quoted text -

- Show quoted text -

Hi FJ

hmm....that would be a lot of guess working.

As I am rather busy right now I don't think, that i can up with a
clean solution for you.

If you could do it manually that would be great.
(I'm not tired of this, i'm actually still interested in why it
doesn't work!)

I don't think I could work on this problem until tuesday or even
wednesday.
I will definitely have another look at it then.

Sorry for that.

Cheers Carlo