Click here to Skip to main content
15,314,965 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
hello everyone.
i have below code with 2 for next loop:

For i = Row1 To Row2
        On Error GoTo ErrorHandler
        Temporary = Left(Range("N" & i).Value, (InStr(1, Range("N" & i).Value, "_", vbTextCompare) - 1))
        On Error GoTo -1
            For z = LBound(MatrNumeri) To UBound(MatrNumeri)
                If Temporary = MatrNumeri(z) Then
                    Dim ImportoSomma As Integer
                        ImportoSomma = CInt(Cells(i, 15).Value)
                        MatrSomme(z) = MatrSomme(z) + ImportoSomma
                End If
            Next z
qui:
        Next i
    Stop
    Erase MatrNumeri()
    Erase MatrSomme()
Cells(Row2, 5).Offset(1, 0).Select

Loop
'*********************
ErrorHandler:
GoTo qui
'*********************


the line where variable "temporary" is determined, sometimes retrun error because parameter are not respected.
the error managing always return run time error n. 5 (invalid pricedure call)

What I have tried:

i tried
on error goto qui (label)
on error goto handler (off loops label9
on error goto 0 (error check ending)
on error goto -1 (error check ending)
Posted
Updated 15-May-22 19:48pm
Comments
CHill60 13-May-22 4:17am
   
What are the values of Row1 and Row2 and what is in the range N(Row1) to N(Row2) - that is likely to be the cause of your problem
Mat 257 13-May-22 8:03am
   
hi, and thanks for yuor reply, please find below full subroutine code:


Sub TrasfDaTI()
Dim FindRange As Range, AddressDataFin As Integer, riga As Integer
Set FindRange = ActiveSheet.Range("E2:O445")
AddressDataFin = 445
riga = 3

Dim MatrNumeri() As Variant, Somma As Integer, SearchRange As Range, NewRiga As Integer
Dim z As Integer, i As Integer
Dim Row1 As Integer, Row2 As Integer, Trovato As Range, CosaCerco As String

'ReDim MatrNumeri(2)
'MatrNumeri(0) = "4"
'MatrNumeri(1) = "16"
'MatrNumeri(2) = "11"

NewRiga = riga
'REIMPOSTO IL RANGE DI DATI UNA CELLA PIù SOPRA
Set SearchRange = ActiveSheet.Range(Cells(NewRiga - 1, 5), Cells(AddressDataFin, 5))


Cells(NewRiga, 5).Select
Dim MatrSomme() As Variant, Temporary As String

Do Until ActiveCell.Value = ""

CosaCerco = ArrangeStringDate(ActiveCell.Value)
Row1 = SearchRange.Find(what:=CosaCerco, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
Row2 = SearchRange.Find(what:=CosaCerco, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

MatrNumeri() = MatrixNumeriProdot(FindRange, Row2, Row1) '<----
ReDim MatrSomme(UBound(MatrNumeri))
For i = Row1 To Row2
On Error GoTo ErrorHandler
Temporary = Left(Range("N" & i).Value, (InStr(1, Range("N" & i).Value, "_", vbTextCompare) - 1))
On Error GoTo -1
For z = LBound(MatrNumeri) To UBound(MatrNumeri)
If Temporary = MatrNumeri(z) Then
Dim ImportoSomma As Integer
ImportoSomma = CInt(Cells(i, 15).Value)
MatrSomme(z) = MatrSomme(z) + ImportoSomma
End If
Next z
qui:
Next i
Stop
Erase MatrNumeri()
Erase MatrSomme()
Cells(Row2, 5).Offset(1, 0).Select

Loop
'*********************
ErrorHandler:
GoTo qui
'*********************

End Sub

My comment was
Quote:
What are the values of Row1 and Row2 and what is in the range N(Row1) to N(Row2) - that is likely to be the cause of your problem
Dumping your entire sub-routine here does not provide the content of that range nor those variables

Look at the line that is causing the problem and break it down into it's component parts
VB
Temporary = Left(Range("N" & i).Value, (InStr(1, Range("N" & i).Value, "_", vbTextCompare) - 1))
The Left function expects a string as it's first parameter and then a long value representing the length of the string you want to return. See Left function (Visual Basic for Applications) | Microsoft Docs[^]. In particular note the comment
Quote:
length Required; Variant (Long). Numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If greater than or equal to the number of characters in string, the entire string is returned.
So that second parameter must be a whole number and it must be greater than or equal to 0 - zero.

You are passing in the value
VB
(InStr(1, Range("N" & i).Value, "_", vbTextCompare) - 1)
If the range N1 contains the value "here_is_a_string" then that will equate to passing in the value 5 - 1 = 4 and you will get "here" as the result.

If that range contains the value "_here is a string" then it will equate to passing in the value 1 - 1 = 0 and your will get "" as the result.

But if one of your values in column N does not contain any underscore characters then you will pass in the value 0 - 1 = (-1) - which is illegal which is why you get the error.

You can fix the error either by fixing your data, or by programming defensively and including something like
VB
If Instr(Range("N" & CStr(i)).Value, "_") > 0 Then
See InStr function (Visual Basic for Applications) | Microsoft Docs[^]. I can't help you beyond that because you have not shared data that caused the problem.

Quote:
on error goto qui (label)
on error goto handler (off loops label9
on error goto 0 (error check ending)
on error goto -1 (error check ending)
Playing with the error handling does not solve any problems, it looks like you were frantically trying to just ignore the error instead of understanding what was going on. Also On Error GoTo -1 does not mean "error check ending" - it clears the current error. Do some research on error handling in VBA e.g. VBA Error Handling - A Complete Guide - Excel Macro Mastery[^] and also how to debug your code e.g. VBA: How to Debug Code - Overview, Tools, Shortcut Keys[^] (warning - the link is for a useful article but you will get pop-ups trying to sell you courses - ignore them)

Finally (I could go on, but I won't) look at
VB
Range("N" & i).Value
i is a number and "N" is a string, so you shouldn't really be able to concatenate them "as is" using &. VBA is generous and essentially does that conversion from number to string on your behalf. However, that is not good practice and one day they may remove that auto-conversion. So get into the habit of making sure you convert numbers to a string before you attempt to concatenate them e.g. use
VB
Range("N" & CStr(i)).Value
   
Comments
Mat 257 13-May-22 13:42pm
   
very thank you for this detailed help. just let me try to explain the code meaning.

i hope to clarify:
the main function of this code is to
1) purpose: determine a check ranges of data between row 1 and row 2.
these are two row numbers.
the row numbers are determined in column (column n. 5) by 'cosacerco' look up (.find).
cosacerco it is a string data.
this new range is a fixed sub-range in column 5 between newriga and addressdatafin rows.
therefore the new range to be analyzed is inner 'searchrange' and is restricted to row1 and row2

it starts reading data by selecting cells(newriga,5) .

this cell value is look up along the entire column (5);
row1 find the first occurance in top down direction
while row2 looks the same value but bottom up direction.

2) purpose: to scroll down each cell in new range between row1 and 2 and populate 2 arrays upon conditions met.
MatrNumeri() hold all values that may be found during scroll.

each cells value may have fisrt part with 1/2 numeric value digit separated by the rest of string content with "_" delimiter (something like "12_aabbcccdd4g&&$$....")

of course not all cell have such character ("abbgg12///"), and when instr function return nothing, the error occur.

second array MatrSomme() it's an array beside MatrNumeri(); it has same size.

so if one element of MatrNumeri(i) is found in range string value , the same position of MatrSomme( ) is populated by a progressive sum integer.

MatrNumeri hold a series of numeric values that match first numeric part of range value;
eg. MatrNumeri(2) where MatrNumeri(0)="5", MatrNumeri(1)="16", MatrNumeri(2)="8"

loop (i) srcoll over all ranges between row1 and row2,
loop (z) scroll over all MatrNumeri elements, i.e. fro 0 to 2

let's say range value = "5_aabbcccdd4g&&$$...."

the numeric string compared with MatrNumeri(0) element is variable 'Temporary '; in this case it is = "5", first numeric value of range content.

since MatrNumeri(0) is = 'Temporary'= "5" then MatrSomme(0) is populated by an integer.
for my question, it doesnt matter where it comes from.

the final result consists , in this case, of two arrays;
MatrNumeri(0)="5", MatrNumeri(1)="16", MatrNumeri(2)="8"
MatrSomme(0)=5000, MatrSomme(1)=300, MatrSomme(2)=26657.

as i told, if "_" is missing, 'Temporary' is nothing and the error pops out.

so i simply would step over next loop if this mismatching occur.
thats why i thought to use on error goto ... next loop

precisely , error handling work fine at first error occurance but in second , it stucks
CHill60 13-May-22 13:45pm
   
Don't use error handling to handle potential data anomalies that you can very easily handle properly with code.
Error handling is for errors, not validation
Maciej Los 13-May-22 16:33pm
   
Sorry, Caroline, but i can't agree with this statement: "So get into the habit of making sure you convert numbers to a string before you attempt to concatenate them(...)". That's the reason of 4.
BTW: This code is perfectly correct: Range("N" & i).Value. MSDN documentation does not mention the need of convert. I'd aggree with your statement if there would be [+] operator and there will be concatenation like this: "2" + 2. The result might be different than expected, especially, when on the left side would be variable type of variant.
Please, read this: Concatenation Operators in Visual Basic | Microsoft Docs[^]
hi everyone and thank you again.
as suggested, i replaced error check with a simple if statement.
if instr("_") return >0 then run code below, else step over next loop
it works
thank you
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900