runtime error 13 type mismatch

[jimcuk]

Hi Folks
I'm using the code below and it seems to be working ok, However in the event I right click and delete a row I get a runtime error 13 type mismatch, can anyone suggest a solution.
Thanks

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[jimcuk]

Sorry Richard
Done

[Richard Buttrey]

Hi,

Any chance you can upload the workbook, anonymised if necessary so that we can see the problem in context?

[jimcuk]

Here you go Richard, I am noting the error has occurred at other times s well so I have broken something

[Richard Buttrey]

Can you clarify what actions you take which results in the runtime error.

I'm assuming it's the Trailer_costs sheet that's active when this happens. I find that right clicking a row and selecting delete puts Excel into what seems a loop of some sort with much screen flashing. I needed to use task manager to close Excel.

[jimcuk]

Yes it's the trailer cost sheet

[Richard Buttrey]

...and what actions do you take? Please give clear details since as I say I encounter a runaway loop problem but not an error message.

[jimcuk]

Hi Richard
I am also getting the runaway loop now, Initially I was selecting the drop down in the off hire column the vba msg box would appear I select yes, it would successfully move the row to the off hire sheet then the error would appear indicating this line

Please Login or Register  to view this content.

at fault

[Richard Buttrey]

Hi Richard
I am also getting the runaway loop now, Initially I was selecting the drop down in the off hire column the vba msg box would appear I select yes, it would successfully move the row to the off hire sheet then the error would appear indicating this line

Please Login or Register  to view this content.

at fault

Hi,

This is slightly different to your original post in which you mentioned right clicking and deleting a row.

Right clicking a row and deleting a row of course causes the target address to be the whole row, i.e. all 16000+ columns. There is no intersection with column M in this case, hence the loop that process the upper case statement is invoked. So it's not actually a runaway ad infinitum but still takes a long time.

In getting the variable LR you're using the empty column A which always returns the value 1 which doesn't sound right.

I still can't get the error message though.

[jimcuk]

Hi Richard, Thanks for your patience.
I have deleted the zoom code and the code to change to upper case, to simplify it, and I am no longer getting the error or loop, I suspect when I added the code I messed it up.
The issue I have now is it seems to be copying to the off hire page and overwriting the previous row, instead of appending it to the next empty row can youu help with this
amended sheet attached.