VBA Integer from Cell

Question

In my code I keep having the error "Runtime error 13 - type mismatch" When I put the lines in comment that get the value from the Cell to put in the integer (qtyCode = Cells(x, "L").Value) , this disappears. But I can't seem to find out why it's type mismatch.

Column L is set as number in my excel file.

Sub counting()
Dim code As String
Dim lookup As String
Dim qtyCode As Integer
Dim qtyLookup As Integer

Dim numRows As Integer

numRows = Range("AM2", Range("AM2").End(xlDown)).Rows.Count
For x = 1 To numRows
    code = Cells(x, "AM").Text
    qtyCode = Cells(x, "L").Value   'error here
    For y = 1 To numRows
        lookup = Cells(y, "AM").Text
        If (code = lookup) Then
        qtyLookup = CInt(Cells(y, "L").Text)  'error here
        qtyCode = qtyCode + qtyLookup
        End If
        ActiveCell.Offset(1, 0).Select
        Next
    Cells(x, "AN").Value = qtyCode
    ActiveCell.Offset(1, 0).Select
    Next
    End Sub

I assume the solution will be easy and I'm overlooking something most likely..

Thanks in advance,

David

This is the code, there is still something wrong with the value output, but no more errors so this question is solved :)

Sub counting()
Dim code As String
Dim lookup As String
Dim a As Long
Dim b As Long
Dim c As Long


Dim numRows As Integer

numRows = Range("AM2", Range("AM2").End(xlDown)).Rows.Count
For x = 2 To numRows
    code = Cells(x, "AM").Text
    a = CLng(Cells(x, "L").Value)
    For y = 2 To numRows

        lookup = Cells(y, "AM").Text
        If (code = lookup) Then
        b = CLng(Cells(y, "L").Value)
        c = a + b
        End If

        Next
    Cells(x, "AN").Value = c
    Next
    End Sub

I removed the comment ' from the code but it's line: qtyCode = Cells(x, "L").Value and line: qtyLookup = CInt(Cells(y, "L").Text) note the CInt was to try if it might work to convert string -> int — David Vandenbroucke
– David Vandenbroucke, Commented Apr 26, 2016 at 8:20
Yes they are. No blanks only integers, I set column format as number — David Vandenbroucke
– David Vandenbroucke, Commented Apr 26, 2016 at 8:25
Have you checked the value of x or y where the error occurs? You can check the value of the current cell being checked when the error is generated. There might be something fishy there. — Rowen Chumacera
– Rowen Chumacera, Commented Apr 26, 2016 at 8:37
Well, you used the 2nd and 3rd rows in that sample you just gave but in your code, processing starts from the first row which you said contains headers. That's why I asked if ALL values are integers in the columns where the errors are generated. :) — Rowen Chumacera
– Rowen Chumacera, Commented Apr 26, 2016 at 8:44

J. Chomel · Accepted Answer · 2016-04-26 08:42:48Z

Add debugging info when error occurs:

Sub counting()
On Error GoTo ErrorTrap
  Dim code As String
  Dim lookup As String
  Dim qtyCode As Integer
  Dim qtyLookup As Integer

  Dim numRows As Integer

  numRows = Range("AM2", Range("AM2").End(xlDown)).Rows.Count
  For x = 1 To numRows
    code = Cells(x, "AM").Text
    mydbgval=Cells(x, "L").Value
    qtyCode = Cells(x, "L").Value   'error here
    For y = 1 To numRows
      lookup = Cells(y, "AM").Text
      If (code = lookup) Then
        mydbgval=CInt(Cells(y, "L").Text)
        qtyLookup = CInt(Cells(y, "L").Text)  'error here
        qtyCode = qtyCode + qtyLookup
      End If
      ActiveCell.Offset(1, 0).Select
    Next
  Cells(x, "AN").Value = qtyCode
  ActiveCell.Offset(1, 0).Select
Next
Exit Sub
ErrorTrap:
   Beep
   MsgBox "FAILED" & Chr(13) & "Error number: " & Err & Chr(13) & Error(Err) & Chr(13) & "dbgval:<" & mydbgval & ">"
End Sub

user3598756 · Accepted Answer · 2016-04-26 08:45:57Z

1

the main issue is you have to declare all variables used to reference columns/rows indexes as of Long type, since sheet columns/rows number can exceed the capacity of an Integer variable.

Also

get in the habit of placing Option Explicit statement at the very top of your module, thus forcing yourself to declare ALL variables and get much more control of your code.
use fully qualified references up to the worksheet ones to be sure what range you'r dealing with.
change the way you count rows (see code below)

these are only a few suggestions as long as coding habits are concerned, which may result as follows:

Option Explicit '<== always use this

Sub counting()
Dim code As String
Dim lookup As String
Dim qtyCode As Integer
Dim qtyLookup As Integer
Dim x As Long, y As Long

Dim numRows As Long

With ThisWorkbook.Worksheets("MySheet") '< change it as per your needs
    numRows = .Range("AM2", .Cells(.Rows.Count, "AM").End(xlUp)).Rows.Count 'get the last non blank row of column "AM"
    For x = 1 To numRows
        code = Cells(x, "AM").Text
        qtyCode = Cells(x, "L").Value
        For y = 1 To numRows
            lookup = Cells(y, "AM").Text
            If (code = lookup) Then
                qtyLookup = CInt(Cells(y, "L").Text)
                qtyCode = qtyCode + qtyLookup
            End If
            ActiveCell.Offset(1, 0).Select
        Next y
        Cells(x, "AN").Value = qtyCode
        ActiveCell.Offset(1, 0).Select
    Next x

End With
End Sub

Finally I din't grasp the logic of your code: you start counting rows form row 2 of column "AM"but then iterate from row 1.

may be you can picture a thorough scenario

answered Apr 26, 2016 at 8:45

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

4 Comments

Rory Over a year ago

The number of columns doesn't (yet) exceed an Integer's capacity.

user3598756 Over a year ago

@Rory: yes you're right. the "integer" warning is currently for rows number from Excel 2010 worksheets on, which must be the OP's scenario. Consider my currently improper scope widening to columns as a "safe habit" for (hopefully?) future excel versions.

Rory Over a year ago

I sincerely hope they never increase the columns that far!

user3598756 Over a year ago

And I sincerely agree!

Patrick Honorez · Accepted Answer · 2016-04-26 09:00:13Z

0

Not sure if this the source of your error, but remember that Cells() is 0 based. That means that to refer to cell F7, you have to use Cells(6,"F").
So you should perhaps review you FOR...NEXT statement accordingly, using something like:

For x = 0 To numRow - 1

For a more readable code avoiding this difference, you can also use Range("F" & x). Perhaps slightly less efficient but I feel it more comfortable to debug.

EDIT: Cells() is NOT 0 based, but rather 1 based. Sorry for the misinformation, but glad it helped to fix the issue.

edited Apr 26, 2016 at 9:00

answered Apr 26, 2016 at 8:31

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

4 Comments

David Vandenbroucke Over a year ago

Actually this might be the issue. I forgot the header and since x = 1, it'll take a string. I'll adjust that first.

Rory Over a year ago

Cells is not 0 based. Cells(6, "F") is F6, not F7.

David Vandenbroucke Over a year ago

Okay, so yes I found the error thanks to your reasoning :) So All I had to do is exclude the column headers, since he was putting a string in an integer (or trying atleast) this caused the error. Thanks!

Patrick Honorez Over a year ago

@RowenChumacera: you are right :-/ I will try to update my answer to avoid zillion downvotes

Collectives™ on Stack Overflow

VBA Integer from Cell

3 Answers 3

Comments

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related