For Each Cell in Range Enter Value

[Storm08]

Hi all,

I've been having trouble figuring out how to do this.
What I want to do is something like this:

Please Login or Register  to view this content.

I've tried to run and change this code, but Excel will crash every time.
I'm not familiar enough with VBA to figure out why. Any help?

Thanks!

[BadlySpelledBuoy]

Could you attach a sample workbook so we can see the issue in context? It would make it a lot easier to fathom what the issue is.

BSB

[JohnTopley]

Works fine for me in that data is placed in the range: what error do you get?

[Storm08]

Thanks for the quick responses!

Workbook attached. I've removed the rest of the data in the workbook, but the error "Out of stack space" still remains. I assume that means it gets stuck in a loop?

[BadlySpelledBuoy]

Every time the worksheet change event runs, it changes a cell value which in turn runs the change event again, and again..... Eventually you run out of memory and get that error.
Try this:

Please Login or Register  to view this content.

It will turn the event trigger off whilst the code runs, then back on again ready for the next worksheet change.

BSB

[Storm08]

Every time the worksheet change event runs, it changes a cell value which in turn runs the change event again, and again..... Eventually you run out of memory and get that error.
Try this:

Please Login or Register  to view this content.

It will turn the event trigger off whilst the code runs, then back on again ready for the next worksheet change.

BSB

BSB, Thank you very much. That makes a lot of sense.

I've tested it and it no longer crashes excel, but the value I need in each cell isn't correct.
What would be the best way to get the range to count up to the value in U11? For instance, the value in U11 is 8, so the cells in the range need display:

	1/1
	2/1
	3/1
	4/1
	5/1
	6/1
	7/1
	8/1

[BadlySpelledBuoy]

You need to increment your rowNum variable each turn of the loop.

Please Login or Register  to view this content.

BSB

[Storm08]

Ah, cool.

Would it also be okay to do this?:

Please Login or Register  to view this content.

[BadlySpelledBuoy]

Absolutely. Although perhaps it should be -31 rather than -32?

When I tested your code the first value was 0/1

BSB

[Storm08]

Absolutely. Although perhaps it should be -31 rather than -32?

BSB

...that is why I have excel to do maths for me! Haha, thank you.

Lastly, what would I add to make the For loop stop when rowNum gets to a certain value? Would that just be an If statement at the first line of the for loop to create the condition?

[BadlySpelledBuoy]

Certainly one way of doing it.

Please Login or Register  to view this content.

Change 10 to whichever number you want to stop at.
Alternatively, change the "Set cirNum = Range...." line to only include the number of rows you want to run it for.

Which is the best option would very much depend on your preference. I have little idea of what you're actually trying to do with this workbook so not best placed to answer that for you.

BSB

[Storm08]

Ah, I didn't know you could do an Exit For rather than Exit Sub.

That's more than enough education for me to do what I need to with this.
Thank you very much BSB!

[BadlySpelledBuoy]

Very happy I could be of assistance and pass on a little knowledge

Good luck with the project and you know where we are if you need more help.

BSB