Type Mismatch, Out of Context

markinboone

New Member
Joined
Dec 30, 2010
Messages
5
This is so frustrating. I am trying to put the value of a cell into a variable and I get the useless error Type Mismatch. I understand a variable of one type can't hold a value of a different type (the explanation from every search result I got). But my code has a cell reference that finds the value I'm looking for when I debug the code and hold my cursor over it (see link for pic) but will not pass it to the variable and throws the TM error.

https://drive.google.com/file/d/1nfMsHbsjfEEO2mUVaUjJxtwnCEAhSDBn/view?usp=sharing


view

The block of code I'm working on should loop through the list of worksheet names saved in a named Range (using variable c) and get the value of a cell (identified with another variable myRow + 6 as the column property) from each worksheet to sum them (add each to the current value of variable catsum). I can't get the initial value found into variable catsum.


I have tried:

  • not declaring the variable
  • declaring the variable as a Variant (isn't that the type that assumes the type of the data assigned to it???)
  • declaring a public variable
  • using different variable names
  • adding .Value to the reference
  • removing variables in my cell reference
  • taking the Cells property in my reference out of the Range property
  • changing the target cell from a formula to a fixed value
  • setting a watch on the variable catsum, which returns Value: <out of="" context="">, Type: Variant/Empty, Context: ThisWorkbook.test</out>
  • setting a cell on the active sheet to the found cell's value instead of setting a variable (How can that be a type mismatch???)


Everything I've tried returns the TM error. This goes against everything I've learned about variable coding. Why is this so ridiculously difficult???


Code:
Sub test()
myRow = 5
Dim c As Range
   For Each c In Worksheets("BankDrafts").Range("BudgetAccts")
      catsum = ActiveWorkbook.Sheets(c).Range(Cells(2, myRow + 6))
   Next
End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
This is so frustrating. I am trying to put the value of a cell into a variable and I get the useless error Type Mismatch.

Hi,
nothing useless about the TM error being reported - it is simply telling you that there is something wrong that needs resolving.
looking at how you have constructed your code I would suggest that you probably need to include the the required properties of the Range object.

Rich (BB code):
   For Each c In Worksheets("BankDrafts").Range("BudgetAccts")
      catsum = ActiveWorkbook.Sheets(c.Value).Range(Cells(2, myRow + 6).Address)
   Next



see if this solves the issue.

Dave
 
Upvote 0
Thank you. That appears to have solved it. I have never seen property constructions like that and did not find anything but your reply to suggest it.

The Type Mismatch error is meaningless (to me anyway) in the sense that it doesn't point to the specific problem nor does it have any straightforward solution. Solutions, as you've shown, go beyond "the data types have to be the same".

Thanks again for your help.
 
Upvote 0
Thank you. That appears to have solved it. I have never seen property constructions like that and did not find anything but your reply to suggest it.

Methods & Properties can be found in the VBA Helpfile

https://msdn.microsoft.com/en-us/VB...rkMoniker-Office.Version=v15)&rd=true#methods

The Type Mismatch error is meaningless (to me anyway) in the sense that it doesn't point to the specific problem nor does it have any straightforward solution. Solutions, as you've shown, go beyond "the data types have to be the same".

Thanks again for your help.

When you get Debug error message press the help button - this will take you to the appropriate place in VBA Helpfile.


Glad suggestion resolved


Dave
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top