Type Mismatch error on string creation VBA

Question

I'm having an issue that I can't quite figure out through google searching. I'm constructing a string based on cell content within a workbook and values from a variant array and getting a type mismatch error when the code reaches the argument where I construct the string. Code below. Any help is appreciated, thanks!

    Dim taskAssignments As String
        taskAssignments = ws.[getTaskAssignment] & var(i, 1) & ws.[getTaskAssignment2]

There's not nearly enough information here to give you a definitive answer. Exactly what is getTaskAssignment/getTaskAssignment2 (seriously? how about meaningful names?) and what's its value? — Mathieu Guindon
– Mathieu Guindon, Commented Mar 28, 2017 at 15:57
Try putting each of the 3 into the taskAssignments string one at a time to see which one is causing the issue? — CLR
– CLR, Commented Mar 28, 2017 at 15:57
Try it as ws.Range("getTaskAssignment") in both places. — user4039065
– user4039065, Commented Mar 28, 2017 at 15:58
if getTaskAssignment and getTaskAssignment2 are the names of two named ranges local to the same worksheet to which ws is a valid object reference, then your code is likely to fail in the var(i, 1) part -> wrap it in a Cstr() call — user3598756
– user3598756, Commented Mar 28, 2017 at 16:05

Mathieu Guindon · Accepted Answer · 2017-03-28 15:59:56Z

3

Try wrapping each value in CStr() to explicitly convert the values to a String:

taskAssignments = CStr(ws.[getTaskAssignment]) & cstr(var(i, 1)) & cstr(ws.[getTaskAssignment2])

edited Mar 28, 2017 at 15:59

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Mar 28, 2017 at 15:59

Zachariah Beasley

462 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mathieu Guindon Over a year ago

If the cell contains an error, this will blow up all the same.

Yoav24 Over a year ago

Also try using .Value at the end of every range. such as ws.[getTaskAssignment].Value ... you can combine this with Cstr() if needed.

ye-olde-dev Over a year ago

this worked - thanks. the cell didn't contain an error, I've already vetted that. I'll accept the answer in a few.

Collectives™ on Stack Overflow

Type Mismatch error on string creation VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related