Type Mismatch Error

[USNA91]

BACKGROUND

I have a workbook with 31 worksheets.

Thirty of those sheets (let's call them the Data Sheets) are identical to each other. The 31st sheet (let's call it the Reference Sheet) contains a table with information from each of the 30 Data Sheets, with each Data Sheet taking a single row.

In each of the 30 Data Sheets is a function that uses the Sheet Name to determine which row in the Reference Sheet table that Data Sheet appears. This function sits in Cell A5 of each Data Sheet and results in an integer from 4 to 33. These functions are working perfectly.

Each Data Sheet contains the code below:

CODE IN QUESTION

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Row As Integer

If Not Intersect(Target, Range("C7:L161")) Is Nothing Then

Row = ActiveSheet.Range("A5").Value
Worksheets("Lookups and Calculations").Cells(Row, 9) = Now
End If

End Sub

INTENDED FUNCTION

The code is triggered by a Change Event on the sheet. If a change is detected in any of the cells in Range (C7:L161), then the date and time of the change is logged in the table on the Reference Sheet ("Lookups and Calculations"). The row where that sheet's data is to be entered is taken from Cell A5 of the sheet, and the target cell is then identified using the CELL (Row,9) function. All results for this fall into column 9 (Column I).

Each of the 30 Data Sheets contains the code above.

PROBLEMS ENCOUNTERED

1. The code works some of the time, but other times it throws an "Application-Defined or Object-Defined Error" message at the underlined line above.
2. Sometimes the code is triggered even when no change in Range (C7:L161) has been made. This happens when the sheet is being hidden or unhidden via VBA.

QUESTIONS

• First, am I doing anything that's obviously wrong?
• Am I running afoul of the code confusing which sheet is active at the time?
• Any ideas why it would work some of the time but throw errors otherwise?

Thanks!

 

[Oaktree]

When the code gives you the error, click debug, then type ?activesheet.name in the immediate window. I suspect you're triggering the hide/unhide while Lookups & Calculations is your active sheet and cell A5 on that sheet doesn't contain an integer/row number.

As a potentially workaround, you might also consider explicitly referring to the Sheet Codenames (you'll see e.g. Sheet5(Name of Sheet) in the VBA project explorer next to each sheet object... Sheet5 is that tab's codename and the benefit is that it doesn't change if you rename the sheet) in your code such as:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngRow As Long

If Not Intersect(Target, Sheet5.Range("C7:L161")) Is Nothing Then
    
lngRow = Sheet5.Range("A5").Value
Worksheets("Lookups and Calculations").Cells(lngRow, 9) = Now
    End If
    
End Sub

Note that, as an aside, it wouldn't cause your code to break, but it's still not a good idea to declare a variable with the same name as a property. Consider the Hungarian notation "intRow" for your variable name instead of "Row"... or, better, "Dim lngRow as long" instead of "Dim Row as Integer" as above, as it's a good practice to declare variables that refer to row numbers as longs instead of integers. If you're in that habit, you won't need to debug the issue when it does matter/break!

 

[USNA91]

It would seem that I was trying to make a selection on a sheet after it was hidden, and that cascaded into other issues. Since I fixed that, the error above has vanished.

I have no other explanation, but I appreciate your help. Thanks!

 

[USNA91]

Dear Oaktree,

I'm afraid the problem has returned, only now it's flagging a different line of code.

I followed your advice on the variable names and types, so the code now looks like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TableRow As Long

If Not Intersect(Target, Range("C7:L161")) Is Nothing Then
   
    TableRow = ActiveSheet.Range("A5").Value
    Worksheets("Lookups and Calculations").Cells(TableRow, 9) = Now
    End If
   
End Sub

I now get the Type Mismatch error at the TableRow = ActiveSheet.Range("A5").Value line.

I did your trick in the immediate window, and the correct sheet is activated. I also determined that this has nothing to do with hiding or unhiding, as this is being triggering before any such command is given.

I have been working extensively with another sub in the same workbook (I have a thread or two on that headache as well), but that one seems to be performing fine up to where the code above trips the error.

The way I see it, there are two problems I need to solve:

1) Why is this Type Mismatch error happening? Am I running afoul of subroutine scopes (i.e. - Workbook vs. worksheet)? the TableRow variable is used in both subs, and I may not have separated them properly. Come to think of it, I may change the way I call for that value in the other sub. Hmmm...

2) When the other sub is running, the sub quoted above does NOT need to run at all. Any way to make that happen? Mind you, if I fix issue 1), then this other issue becomes transparent.

Thanks again...

 

[USNA91]

Here's another thought...

The "Integer" in Cell A5 is actually created by a VLOOKUP function that in turns pulls a ROW() result from another cell. Do I need to use an INT function or something? The cells themselves are formatted as numbers with zero decimals, but that may not mean anything to VBA...

 

[USNA91]

And another observation...

I currently have two subs referencing the same cell on a worksheet.

BOTH subs are throwing the same error for the same statement calling on that cell's value and assigning it to TableRow.

Doesn't matter if TableRow is defined as Integer or Long, or if I use different variable names in the two subs. There's something with the way that cell is being read that has changed, and I can't for the life of me figure it out.

 

[roelandwatteeuw]

I can't see a problem with the code.
And I tried it and the code ran without a problem.
Even with a Vlookup there was no problem

Before changing something, maybe you can put a 'Break' before the Worksheet Change sub.

Do this by clicking in the space at the left side of the sub. A red dot will appear.​

Then change something on your sheet so it triggers the Worksheet_Change Sub
VBA will wait to run the code. The first line will turn yellow.
Now you can run the code step-by-step by pressing the 'F8'-key on your keyboard.
Keep hitting the 'F8'-key and look all the steps VBA makes.
Now you can detect if an other sub is being triggered... and maybe causing the error.


You can avoid other subs from being triggered by adding this in your code:

Application.EnableEvents = False​

And it's highly recommended to reactivate it at the end of the sub --> and also put it in an errorhandler (otherwise it will stay disabled... even when you close en restart Excel):

Application.EnableEvents = True​

​

 

[Alex Blakenburg]

I now get the Type Mismatch error at the TableRow = ActiveSheet.Range("A5").Value line.

The "Integer" in Cell A5 is actually created by a VLOOKUP function that in turns pulls a ROW() result from another cell.

You didn't quite follow roeland's naming suggestion. He was suggesting prefixing it with the data type in this case lng for long.
That will return a number if it can convert it to a number and a 0 if it can't UNLESS the value is an error ie #N/A (or #REF! etc).

So check if the lookup in A5 is returning a #N/A.
If you put the line below before the TableRow = line when you are troubleshooting it, it will show you what is in A5 when it errors out

Debug.Print "A5 Value: " & ActiveSheet.Range("A5").Text

Perhaps add the cell that is used in the Vlookup instead of A5 to see what it is the vlookup isn't finding.

 

[USNA91]

I found the problem late last night...

As I pored over the spreadsheet, it occurred to me to look at the result in A5. It was #N/A. That would explain it, but why?

Turns out that when the code ran, it set calculation to MANUAL to help speed it along. When an error was encountered, the flag remained as MANUAL. I noticed this when the tables in "Lookups and Calculations" were not updating as expected, so the values being looked for were not there, and the #N/A resulted. This also explains why the problem would suddenly start out of nowhere (without any visible changes to justify the start of the error) and lingering.

I'll be testing everything again later today, but a key lesson learned.

Alex,

I see what you mean regarding naming variables. One thing I'm trying to find is good examples of good programming practices such as indenting, etc. It aligns with my OCD. I've read elsewhere about how variables should be named but it seemed kinda convoluted to me. Maybe I need to reassess that conclusion...

 

[Alex Blakenburg]

When an error was encountered, the flag remained as MANUAL.

Ideally code should run the lines resetting the Application settings such as calculations even when an error occurs.
Unfortunately there are no hard and fast rules on naming conventions. The name should be clear enough that you have a pretty good idea of what sort of data it should contain and how it is to be used.