VBA Macro Error

Question

I had this macro running perfectly on one worksheet but now it is displaying an error on line 13. The macro basically finds all values under any heading starting with "Unique Pulls" then adds them all together.

I'm thinking this is something to do with the ActiveSheet but I can't seem to figure it out.

       If UCase$(ActiveSheet.Cells(1, i).Value) Like "UNIQUE PULLS*" Then
            iTotal = iTotal + ActiveSheet.Cells(n, i).Value
               ' For each of these columns, take value and add to total sum
        End If

Run-time error '13': Type mismatch

"Displaying an Error" - what error? What is the source data causing the error? What does the source data look like when it doesn't cause an error? If you know it's on line 13, then why show us the rest of your code? Clean up your code to show us only the relevant portion which runs up to the point of the error, document the data causing the error, and the details of the error itself. — Grade 'Eh' Bacon, Feb 19 '16 at 14:53
Also, I suggest removing all instances of [`.Select` and `.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) which can cause headaches. — BruceWayne, Feb 19 '16 at 15:01
I've updated the question to just show a small chunk of code and also displayed the error message — Rob Dunn, Feb 19 '16 at 15:05
Try this: `If Trim(UCase$(ActiveSheet.Cells(1, i).Text)) Like "UNIQUE PULLS*" Then` Also check what is it that you're actually comparing — Stupid_Intern, Feb 19 '16 at 15:09
Santosh, I just added the .Text and it's working perfectly again, thank you — Rob Dunn, Feb 19 '16 at 15:30
@RobDunn Don't just use `.Text` use `Trim` as well to remove leading or trailing spaces before comparing — Stupid_Intern, Feb 19 '16 at 16:25
The error is displaying on this line iTotal = iTotal + ActiveSheet.Cells(n, i).Value I've added the Trim aswell and it's still not running — Rob Dunn, Feb 19 '16 at 16:30

score 1 · Answer 1 · answered Feb 19 '16 at 15:18

1

You would get this error when the value of the cell isn't a number. You could add an IsNumeric check to skip any none numeric cells:

If UCase$(ActiveSheet.Cells(1, i).Value) Like "UNIQUE PULLS*" And IsNumeric(ActiveSheet.Cells(n, i).Value) Then
    iTotal = iTotal + ActiveSheet.Cells(n, i).Value
End If

answered Feb 19 '16 at 15:18

Chris Barber

513
4
10

Or he can use .Text Property of cell to get the text and if it's a number it would obviously will not be matched – Stupid_Intern Feb 19 '16 at 15:20
I just tried the IsNumeric method and it no longer adds up the values in each column – Rob Dunn Feb 19 '16 at 15:26
1

@RobDunn Which suggests that your `VBA` isn't pointed at cells that contain numbers. – ARich Feb 19 '16 at 15:29

score 0 · Accepted Answer · answered Feb 19 '16 at 16:42

Try this:

If Trim(UCase$(ActiveSheet.Cells(1, i).Text)) Like "UNIQUE PULLS*" Then
       iTotal = iTotal + ActiveSheet.Cells(n, i).Value2
       ' For each of these columns, take value and add to total sum
End If

if you're getting error on this line -> iTotal = iTotal + ActiveSheet.Cells(n, i).Value2

That maybe because ActiveSheet.Cells(n, i).Value2 is not a number that can be added but some other data type check it using Debug.Print ActiveSheet.Cells(n, i).Value2 you will get the output of what you're trying to add to total in the Immediate window

VBA Macro Error

2 Answers2