Runtime Error 13 Type Mismatch

[vtsoldier2010]

Dear Forum,

I'm having a bit of an issue with a script I'm writing, and I'm hoping that someone here can help. My issue is i'm getting a runtime error 13 Type Mismatch when I try to run my code. I think the issue lies in the fact that i want my script to be ran on every cell in the column because if i run my script on just one cell say "s2" it works just fine. However, when I expand the cell range reference to capture the entire oolumn I get the error. I read somewhere blank cells in the columns where my script is interacting with the data validation lists could create a problem, and it also could be that the compiler expects one data type, but is seeing another data type. Here's my code, hopefully someone can help me make sense of what i'm doing wrong. I've been working on this project for a week now, and this is the last hurdle to clear before it's completed. Any help you guys can give is greatly appreciated.

Please Login or Register  to view this content.

[vtsoldier2010]

http://www.ozgrid.com/forum/showthread.php?t=163511, Cross Posted Here:

[Firefly2012]

Hi

It is because a multicell range returns an array when you access its Value property and you therefore get the type mismatch when your If tries to compare this array to a single string value (such as "PLX").

Would you mind explaining in words what you want your code to do? Something along the lines of "If a V col value is changed then if the V cell = 'PLX' then modify some other cell by..."

[vtsoldier2010]

[QUOTE=Firefly2012;2737151]Hi

It is because a multicell range returns an array when you access its Value property and you therefore get the type mismatch when your If tries to compare this array to a single string value (such as "PLX").

Would you mind explaining in words what you want your code to do? Something along the lines of "If a V col value is changed then if the V cell = 'PLX' then modify some other cell by..."[/QUOTE

To explain my situation a little better. I have three dependent data validation lists set up in columns Q, R, and S. What I want the code to do is take the choices made in the three data validation lists and compare them to an exact criteria and return a value based on a match to specific criteria. For example if the user chooses "PLX" in column Q and "Imaging_TopCall" in column R and finally "Missing From File" in column S then column U auto populates with "Level 1". A record will be evaluated on every row of the spreadsheet, thus the necessity to have my code run over the entire range of all four columns involved with my code. Is there a way to rewrite my code so that it functions correctly? Any example you can give would be a great help. Thanks so much man>

[tigeravatar]

vtsoldier2010,

Give this a try. The code assumes that multiple cells in Range("Q2:S" & Rows.Count) can change simultaneously. If it's guaranteed that only 1 cell at a time will change, the code can be greatly simplified:

Please Login or Register  to view this content.

[vtsoldier2010]

Bump: No Response