code noob trying to help a friend

[gg1988]

So a friend of mine has a small business has a very small ms access database and uses it to export to excel on occasion. recently some of the code the person who set up for her has screwed up, I said i would try to fix it so now here i am. please be patient. This is the code it gives a runtime 13 error when run. I am also wondering what the sub detection is doing and value for variable intcount is after the code executes? Any help is appreciated

Sub detection ()
Dim i As Integer
Dim intcount, intdrawing, intserial as integer
dim strbarcode as string

intcount = 0
i=1
while worksheets("sheet1").cells(i,1).Value<>""
intdrawing = clnt(worksheets("sheet1").cells(i,1).Value)
intserial = clnt(worksheets("sheet1").cells(i,2).Value)
strbarcode = worksheets("sheet1").cells(i,3).Value
If intdrawing = 48 and left(strbarcode,2) = "fe" then intcount + 1
i = i + 1
Wend
End Sub

 

[MARK858]

What do you get with the code below and if it still errors what line is highlighted?

Sub detection()
    Dim i As Long
    Dim intcount As Long, intdrawing As Long, intserial As Long
    Dim strbarcode As String

    intcount = 0
    i = 1
    While Worksheets("sheet1").Cells(i, 1).Value <> ""
        intdrawing = CLng(Worksheets("sheet1").Cells(i, 1).Value)
        intserial = CLng(Worksheets("sheet1").Cells(i, 2).Value)
        strbarcode = Worksheets("sheet1").Cells(i, 3).Value
        If intdrawing = 48 And Left(strbarcode, 2) = "fe" Then intcount 1
        i = i + 1
    Wend
End Sub

 

[Micron]

Here's a posting hint. There are thousands of runtime error numbers and although I've experienced many of them, when you post the number with no message, you force us to look up the number.
@Mark858 has hit on what would be my first guess as the problem. The suggestion is that the issue is the lack of declaration - only intserial is an integer, the rest on that line are variants because they have not been declared otherwise. My answer will be somewhat lengthy because I'm addressing several points here.

I see nothing wrong with the data type being a Variant IF it is expected that value being assigned to the variable could be NULL. In that case, the declaration was by design, but it is poor coding practice to not leave notes to that effect, or at least explicitly declare each variable type:
Dim incount as variant, intdrawing as variant, intserial as integer (not that I would declare a variant as int anything as opposed to varCount). So what I'm leading up to here is that the issue is likely the use of the CInt function. It makes no sense to use it on what you expect is an integer in the first place (intCount for example). I'm assuming therefore that the expectation was that it could be something else (Date, Null...) otherwise, what's the point of using the function?

I expect the issue is that the data type being passed to the function cannot be converted. Try inserting either a debug.print or msgbox line that outputs each expression being passed to the function:

msgbox clnt(worksheets("sheet1").cells(i,1).Value)

and see what you get.

Then there's this: intcount + 1. I think it should be written as intcount = intcount + 1, although I don't see anything being done with the value anyway. I also wonder about the fact that I see no declaration for 'worksheets'.

 

[MARK858]

I also wonder about the fact that I see no declaration for 'worksheets'.

Where is worksheets being used as a variable?

I see nothing wrong with the data type being a Variant.....
The suggestion is that the issue is the lack of declaration - only intserial is an integer

Actually I posted my response partly in case intserial is out of the range of an Integer (which is why I changed it to long).

Then there's this: intcount + 1. I think it should be written as intcount = intcount + 1

Good point that I missed in fact if you look at my post you will see the + has vanished because my VB editor doesn't accept it with that syntax and also a bit concerned that the code isn't correctly capitalized.
@gg88, is the code you posted copied from your editor or have you re-typed it?

 

[Micron]

Where is worksheets being used as a variable?

It's not - but then, objects have to be declared as well, don't they? There is no reference to an Excel or workbook object, which would be required somewhere in order for Access to know what a worksheet is. If that's because this code is in Excel, well no wonder I'm on about something that doesn't apply - this is the Access part of the forum and the OP says "a very small ms access database".

Actually I posted my response partly in case intserial is out of the range of an Integer (which is why I changed it to long).

My point was that the undeclared variables are Variants. The intent was to impart a lesson about multiple declarations on one line. The sample provided is a very common oversight in that coders seem to think all variables on one line are of the type that is declared at the end of the line when they are not. As far as the intent, I should not have presumed your intent. I still maintain the issue could be the failure of the conversion function. If the cell reference contains text, it may not be convertible to any type of number. Certainly it cannot be converted to a number if the reference returns Null.

...if you look at my post you will see the + has vanished...

I did, but ignored that as a typo on your part since I was advocating a rewrite of the line anyway and the fact that it was not that way in the OP. Since none of the usual keywords in the OP are capitalized, I presumed it was not from the VBE.

 

[MARK858]

If that's because this code is in Excel, well no wonder I'm on about something that doesn't apply

The way the code is written I think the code is residing in the Excel and not the Access part (and yes if that is the case then the question should have been in the Excel forum) especially as the OP states that the code only

recently... some of the code...has screwed up

but that needs clarifying from the OP.

I still maintain the issue could be the failure of the conversion function. If the cell reference contains text, it may not be convertible to any type of number.

again you could well be correct and the so the contents of the cells also need clarifying by the OP (by the way I was also puzzled by the need for CInt in the original code if it was a whole number in the cell).

Too many possibilities at the moment to take a firm guess until we get more info and so will now wait for some clarification from the OP.