Run-Time Error '13' could someone look at this code for a error please

[Mr_Bill]

Please Login or Register  to view this content.

What I am trying to do is make it so when I update a grade in Column E that the date updates directly to its left and also changes the cell directly under the date to submitted both of these are only if the input is a 0 or higher. The grade is a - by default
the reason for the 1,-1 in because this runs the length of the page and these three cells are always in this proximity to each other.
but the red text is what is yellow in debugger and I cant seem to figure out why. The cell for the grade is as a percent, the cell for the date is formatted as date and the submitted and not submitted are formatted as text.

Thank you for any help that you can provide.

[TMS]

"0" is a text string. 0 is a number (zero)

Regards, TMS

[mike7952]

Give this a try

Please Login or Register  to view this content.

[TMS]

@Mike: couldn't spot the difference first time round ... but I think you've highlighted the wrong bit.

Regards, TMS

[mike7952]

@TMS: If you use the OP code and are in column A it will error out because of the offset.

[Mr_Bill]

Thank you both. Mike that fixed it spot on thank you

[Mr_Bill]

one more question about this if I wanted to keep going following the pattern If .Column = 4,10,16,22,28 etc would I need to do an array (guessing at the term) for that part?

[TMS]

@Mike: why would it error out on the offset.

The change event is only executed for columns 4 and 10:

Please Login or Register  to view this content.

The error is fixed by this change:

Please Login or Register  to view this content.

Which is what I pointed out in post #2 in relation to the line of code that the OP highlighted in red.

What am I missing?

Regards, TMS

[Mr_Bill]

TMS not sure the technical reasoning of the "0" and 0 but when I tried to do the 0 it stopped the error but also when the cell was updated it didnt change the stamp or submitted part.




Figured out how to add more columns seems to be working with no errors. Thank you again

Please Login or Register  to view this content.

[TMS]

OK, hands up, to be fair all the code options ("0" and 0) work. My mistake ... response from looking at the code, not testing the code.

Please Login or Register  to view this content.

	C	D	E	F	G	H	I	J
1
2		-1						-1
3	x Not Submitted						x Not Submitted
4	01/09/2014 23:45	0					01/09/2014 23:47	0
5	x Submitted						x Submitted
6	01/09/2014 23:45	1					01/09/2014 23:47	1
7	x Submitted						x Submitted
8	01/09/2014 23:45	2					01/09/2014 23:47	2
9	x Submitted						x Submitted
10	01/09/2014 23:45	a					01/09/2014 23:47	a
11	x Submitted						x Submitted
12	01/09/2014 23:45	b					01/09/2014 23:47	b
13	x Submitted						x Submitted
14
15		-1						-1
16	y Not Submitted						y Not Submitted
17	01/09/2014 23:46	0					01/09/2014 23:48	0
18	y Submitted						y Submitted
19	01/09/2014 23:46	1					01/09/2014 23:48	1
20	y Submitted						y Submitted
21	01/09/2014 23:46	2					01/09/2014 23:48	2
22	y Submitted						y Submitted
23	01/09/2014 23:46	a					01/09/2014 23:48	a
24	y Submitted						y Submitted
25
26		-1						-1
27	z Not Submitted						z Not Submitted
28	01/09/2014 23:46	0					01/09/2014 23:48	0
29	z Submitted						z Submitted
30	01/09/2014 23:46	1					01/09/2014 23:48	1
31	z Submitted						z Submitted
32	01/09/2014 23:46	2					01/09/2014 23:48	2
33	z Submitted						z Submitted
34	01/09/2014 23:47	a					01/09/2014 23:48	a
35	z Submitted						z Submitted
36

However, you can break each and every one of them if you select more than one cell and, for example, delete the contents.

Regards, TMS

[TMS]

@Mr_Bill: thanks for the rep.

[Mr_Bill]

However, you can break each and every one of them if you select more than one cell and, for example, delete the contents.

How do I prevent that?

here is the code now

Please Login or Register  to view this content.

[TMS]

Well, there are two scenarios that might cause you to select multiple cells and change them. The first is where you select multiple cells, put a value in the first cell and press Ctrl-Enter to populate all the selected cells. If the selected range intersects your "ranges of interest" you will have a problem. In the circumstances, I think this option is unlikely. If it is something that could happen, you would need to cycle through all the cells in the target range and process them as you would have if there was only one cell in the target range.

More likely is that you want to select a row, column or block of cells and delete the contents. In that case, you just want it to happen without executing the body of the event handler code.

To avoid that, put this as the first line of code:

Please Login or Register  to view this content.

Regards, TMS