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
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
(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
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
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
Range("N" & CStr(i)).Value