Type Mismatch, Out of Context

[markinboone]

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

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???

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

 

[dmt32]

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.

   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

 

[markinboone]

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.

 

[dmt32]

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