Looping through workbooks - weird error. 1

[ItIsHardToProgram]

Hello all,

I currently am having a weird problem, wich I can't really define, I have passed almost an hour on this and I am totaly clueless.

I have a macro wich I am working on right now, that is suppose to collect data from a file to another, I am trying to optimize it a bit and working on it every day, so that it can be used for almost all data we hold in the cmopany, wich would save me alot of time.

What it does, is go look at opened workbooks name, splits the name, takes a company name, then matches it with a company name on another spreadsheet.

Once that is done, it goes look for the G/L account, in the spreadsheet.

Unfortunatly G/L accounts are never entered properly, but I can split the cell in order to get that G/L account.

So far so good.

I get the data I need and paste it.

Everything was working fine until I decided to place it in another sub, that I call from my primary sub.

I have a loop, for j = 3 to iworkbookcount
where iworkbookcount = 5

and it errors on j = 6 --> ??????????????

I do not add anything anywhere to the j, I thought it might have to do with the Resume Next in my error handler, but it does not...

I am really clueless.

Here is the code for the primary and the 2nd cell, + the global variables:

global variables

Option Explicit
'
' WorkBookNavigation Macro
' Macro enregistrée le 08/07/2008 par Julien Roy
Dim iWorkBookCount, iSheetCount, RowCount  As Integer
Dim iSheet, iRow, iBook As Integer
Dim i, y, row As Integer
Dim SheetName() As String
Dim location, name, company As String
Dim ActColumn As Integer
Dim FoundValue As Double
Dim testerror As Boolean
Dim currentworkbook, currentworksheet, currentrow, currentcolum As Integer

Dim SplitCompany() As String
Dim SplitGL, SplitGL2

Dim TempVal

Now the primary macro

Sub RedistributionGL()

'Merger entre feuille pour GL et compagnie
'Julien-Bono Roy, 25/11/2008

On Error Resume Next

iWorkBookCount = Workbooks.Count

row = 1

For i = 1 To 9
    'Variable source pour le merger, compte de GL ou de compagnie
    row = row + 1
    SplitGL = Split(Cells(row, 2).Value, "-")
    SplitGL = Split(SplitGL(0), " ")
    row = row + 21
    For y = 3 To 199
         'Dépend des variables pour le merger... il est possible de storer les variables dans un tableau
         'Pour que celles-ci soient utilisé dans un select case avec une boucle tableau(i)
         Select Case i
        
                Case 1
                    If y < 24 Then
                        Call Merging
                    End If
                Case 2
                    'répétition du case 1
                    If y < 46 And y > 24 Then
                        Call Merging
                    End If
                Case 3
                    If y < 68 And y > 46 Then
                        Call Merging
                    End If
                Case 4
                    If y < 90 And y > 68 Then
                        Call Merging
                    End If
                Case 5
                   If y < 112 And y > 90 Then
                        Call Merging
                   End If
                Case 6
                    If y < 134 And y > 112 Then
                        Call Merging
                    End If
                Case 7
                    If y < 156 And y > 134 Then
                        Call Merging
                    End If
                Case 8
                    If y < 178 And y > 156 Then
                        Call Merging
                    End If
                Case 9
                    If y < 200 And y > 178 Then
                        Call Merging
                    End If
            End Select
       

    Next y

Next i

End Sub

And the Merging sub

Sub Merging()

On Error GoTo ErrorHandler
Dim j, x As Integer


                        
                        company = Cells(y, 1).Value
                        
                        For j = 3 To iWorkBookCount
                            currentworkbook = j
                            
                            SplitCompany = Split(Workbooks(j).name, "-")
                            If SplitCompany(0) = company Then
                                'MsgBox (SplitCompany(0))
                                'MsgBox (Company)
                                For x = 1 To 1000
          If Workbooks(j).Worksheets(1).Cells(x, 2).FormulaR1C1 <> "" Then
                                   
                   name = Workbooks(j).Worksheets(1).Cells(x, 2).FormulaR1C1
                                   SplitGL2 = Split(name, " ")
                                   
                                   TempVal = SplitGL2(0)
                             'Ligne ou l'erreur survenait
                                   
                      If SplitGL2(0) = SplitGL(1) And Len(SplitGL2(0)) = 5 Then
                                            'MsgBox (SplitGL2(0))
                                            'MsgBox (SplitGL(1))
                                            'MsgBox (x)
                                        
                                        
                    Range("E" & y & ":" & "P" & y).Select
                                            Selection.Copy
                                            Workbooks(j).Worksheets(1).Activate
                                            Range("C" & x).Select
                                            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                    :=False, Transpose:=False
                                            Workbooks("Copie de Classeur2.xls").Activate
                                        End If
                                    End If
                                Next x
                            End If
                        Next j
                        
ErrorHandler:
    name = Workbooks(currentworkbook).Worksheets(1).Cells(x, 2).FormulaR1C1 & " "
    SplitGL2 = Split(name, " ")
    Resume Next

End Sub

Sorry for the disorganized code but it wouldnt fit in my original settings.

Thank you for your help, as usual.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[PHV]

and it errors on j = 6
Which error ?
On which line ?

Anyway, iWorkBookCount and j are defined as Variant, not Integer, in your code.

Furthermore, add an Exit Sub before the ErrorHandler label ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

Basicaly, j returns 6 so workbooks(j) returns an error, then goes to errorhandler and errors on:

name = Workbooks(currentworkbook).Worksheets(1).Cells(x, 2).FormulaR1C1 & " "

Thank you for your help, and your right, I forgot exit sub.

=/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Sorry I did not read correctly, defined as variants????

Dim j, x As Integer
Dim iWorkBookCount, iSheetCount, RowCount  As Integer

Why are they defined as variants, and I don't think that would cause an error, would it?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[PHV]

j, iWorkBookCount and iSheetCount are Variant.
x and RowCount are Integer.

Put the cursor inside the Dim word in your code and press the F1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

Orly, and I always thought otherwize, well thank you for that pointer.

=/ what an embarassment.

Ok, so, besides that, do you have any clue what so ever what could be causing my current problem?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Oh, I guess that was implicitely fixed by the Exit sub...

On another note, my macro does not fire as it should.

I will get back to you with more information.

Thanks for your help PHV

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Well nevermind, simply a formating problem in the other spreadsheet.

Now that I grabbed your attention though, I might have a question for you, that I would like you to answer, if you can help me.

I would like to be able to "find", if you want, a G/L (general ledger) number in any type of string imaginable.

The G/L numbers are always formed of 5 numbers, always graduate by 5
*(80905, 80910, etc...)

Theres a few, problems
First of all, the "property" numbers, are also formed of 5 numbers. They usualy graduate by 50, are almost never found in the same row than a GL, and if they are its always:
PPPPPGGGGG (P being property number and G gl number)

What I want to do, is to extract GGGGG from ANY TYPE OF STRING.

weither it is formed with ascii, numbers, letters.
"asda80905asdas"
" 80905 "
"1105080905"

So either, 10 character all numbers with a G/L (PPPPPGGGGG)
Or a GL with any type of splitter.


I hope I made myself clear enough???? IF not I will of course rewrite this for you.

Also if you do not want to chew my food, you could get me started on a clue, so that I can not waste 2 hours of my time . Or if its simply too easy, don't bother, I guess

Thanks for your help

Always a pleasure,

Julien


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Andrzejek]

Like PHV said, all your variables in RED are Variants, only BLUE are defined as something:

Dim [red]iWorkBookCount, iSheetCount[/red], [blue]RowCount[/blue]  As Integer
Dim [red]iSheet, iRow[/red], [blue]iBook[/blue] As Integer
Dim [red]i, y[/red], [blue]row[/blue] As Integer
Dim [blue]SheetName()[/blue] As String
Dim [red]location, name[/red], [blue]company[/blue] As String
Dim [blue]ActColumn[/blue] As Integer
Dim [blue]FoundValue[/blue] As Double
Dim [blue]testerror[/blue] As Boolean
Dim [red]currentworkbook, currentworksheet, currentrow[/red], [blue]currentcolum[/blue] As Integer

Dim [blue]SplitCompany()[/blue] As String
Dim [red]SplitGL, SplitGL2[/red]

Dim [red]TempVal[/red]

If you want to declare more than one variable in one line, you have to do this:

Dim iSheet As Integer, iRow As Integer, iBook As Integer

Also, it is a good idea to use prefixes. You do use some, but your are not consistent.

Consider this addition, whoever will have to maintain your code will be grateful for it:

Dim iWorkBookCount As Integer, iSheetCount As Integer, [red]i[/red]RowCount  As Integer
Dim iSheet As Integer, iRow As Integer, iBook As Integer
Dim i As Integer, y As Integer, [red]i[/red]row As Integer
Dim [red]s[/red]SheetName() As String
Dim [red]s[/red]location As String, [red]s[/red]name As String, [red]s[/red]company As String
Dim [red]i[/red]ActColumn As Integer
Dim [red]dbl[/red]FoundValue As Double
Dim [red]b[/red]testerror As Boolean
Dim [red]i[/red]currentworkbook As Integer, [red]i[/red]currentworksheet As Integer, [red]i[/red]currentrow As Integer, [red]i[/red]currentcolum As Integer

Dim [red]s[/red]SplitCompany() As String
Dim SplitGL, SplitGL2

Dim TempVal

Have fun.

---- Andy

 

[ItIsHardToProgram]

Yep, I have modified the variables already, but I will indeed add i for integer, s for string etc... infront of every variable, They would indeed be grateful of this.

Thanks for the suggestion.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.