Need Help-Error When Deleting or Inserting Rows

**krisels** · 08-29-2012, 10:47 AM

I have been working on creating a spreadsheet for our shop foreman. The spreadsheet is designed to help him assign jobs and manage due dates more effectively. After receiving some help from this forum the spreadsheet is now running a few different macros in order to sort and assign jobs. I have run into a couple of bugs though that I need help fixing. First, I had created a macro to insert a new row and insert the formulas needed into the row. That macro was functional until I added a macro to automatically copy data from one tab to the corresponding machine tab once a machine was selected in column A. Now when I try to run the macro to insert a new line I get a Run-time error '13': Type Mismatch error. This error also results any time I click on a row header (like to manually insert a new line). Also, I had inserted a macro to automatically sort the data by the Start Date (oldest to newest) and then Job Number when a value in column M was changed. That macro is working fine except that the first line of data (row 2) is not sorting. I am attaching a Sample Worksheet that is a copy of my actual worksheet with data removed. I would greatly appreciate any help on fixing these issues! I am just learning how to work with macros and I can't seem to figure out how to work though these bugs.

**Richard Buttrey** · 08-29-2012, 11:04 AM

Hi,

If you are selecting a whole row then the problem is with the Sheet Selection Change event

If Not Intersect(Target, Range("A:A")) Is Nothing Then ValChn = Target.Value

In this case the target is a whole row, e.g. 2:2 and the syntax Target.Value can't evaluate. Select a single cell before adding a new row.

**krisels** · 08-29-2012, 11:53 AM

Ah, I see! That makes sense. Thank you! I tried just selecting a cell and was able to insert a new row with no problem. But I still have not been able to figure out a way to automatically paste my formulas into the new cells (either with a macro or without) which was the reason I had originally created a macro to insert a new line. Any suggestions on how to do this?

**krisels** · 08-29-2012, 12:14 PM

I'm sorry, I must have been mistaken. I just tried again and I do still get the error whenever I insert a new row by just selecting a single cell. I believe this is what is causing me to be unable to run a macro to insert a new line and paste the formatting and formulas. If I hit End when the error screen pops up I can continue working but I cannot find a way to tell a macro to hit End when the error message is displayed.

**Richard Buttrey** · 08-29-2012, 12:24 PM

The problem is still the same. Even though you may only be choosing a single cell the instruction

Rows("2:2").Select

in your 'AddNewLine' macro is over-riding the single cell and selecting the whole row.

**krisels** · 08-29-2012, 12:32 PM

I'm sorry, I should have specified that I am inserting the new line manually now (not using the macro) by right clicking in a single cell and selecting Insert, then Entire Row. I am still receiving the error when I insert a new row that way.

---------- Post added at 11:32 AM ---------- Previous post was at 11:29 AM ----------

If I remove the DelPrev macro on the Master List I am able to insert a new line without error so that leads me to believe that there is a problem within this macro. I am just not sure what the problem is or how to fix it.

**Richard Buttrey** · 08-29-2012, 04:12 PM

Yes that's fine, but whilst you think you're only selecting a single cell Excel thinks differently when you ask it to insert a new row, whether manually or with a macro.

And again, you may think you're not using a macro but since you've got both a Sheet Change and a Sheet Selection Change macro coded, Excel thinks otherwise.

One solution is wherever you use a 'target' reference in the macros is to change them to something like
target = target.cells(1,1)
so that you force the target to be a single cell rather than a whole row. Whether that has implications elsewhere for you only you can say.

**krisels** · 08-29-2012, 04:28 PM

I understand what you are saying about using a macro now. I thought you were referring to the macro that I created specifically to add a new row. But I'm sorry, I am very new to this and not completely sure that I understand your suggestion. Should I go in and change every time it says "target" to the "target=target.cells(1,1)" even where there is a target.value?

**Richard Buttrey** · 08-29-2012, 04:44 PM

Hi

I find it difficult to answer that since I don't know what you're wanting to do.

But since you are presumably wanting to pick up a single value, (else why would you specify the .Value instruction?) then I guess the answer is yes.
As ever though, change it and test thoroughly to ensure it delivers what you want.

**krisels** · 09-06-2012, 02:30 PM

I apologize for the delay in my response. I had posted a reply last week but it apparently did not transmit. Anyway, all I am really wanting to do is to insert a row without receiving an error message. I have attempted to do as you suggested and change all of the "target" to "target=target.cells(1,1)" but I am now receiving error messages in the code. Would it be possible for you to supply a small portion of the code so I could see exactly what you are referring to? As I said before, I am very new to using VBA and received a lot of assistance to write the code that I have so I do not understand enough about it to know exactly what you are suggesting. I really appreciate your help!

**krisels** · 09-11-2012, 04:52 PM

I am still needing help from someone on this question with the run-time error that displays whenever I insert or delete a row. I have made a few changes to the original sample spreadsheet and no longer have the problem with the auto sort. I also created a new macro to insert my formulas once I create a new row so I do not need assistance with that either. I just need to know how to either ignore the error message that occurs whenever I insert or delete a row or how to fix it so that there is no longer an error. The error appears to be linked to the

Please Login or Register  to view this content.

part of the code, but I am not sure what this needs to be changed to. I would really appreciate any help on this matter.

Need Help-Error When Deleting or Inserting Rows

LinkBack

Thread Tools

Rate This Thread

Display

Need Help-Error When Deleting or Inserting Rows

Re: Please Help with Bugs in Spreadsheet Macros

Re: Please Help with Bugs in Spreadsheet Macros

Re: Please Help with Bugs in Spreadsheet Macros

Re: Please Help with Bugs in Spreadsheet Macros

Re: Please Help with Bugs in Spreadsheet Macros

Re: Please Help with Bugs in Spreadsheet Macros

Re: Please Help with Bugs in Spreadsheet Macros

Re: Please Help with Bugs in Spreadsheet Macros

Re: Please Help with Bugs in Spreadsheet Macros

Re: Need Help-Error When Deleting or Inserting Rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions