VBA to assign car parking spaces

Hi,

I'm pretty new to VBA, and was hoping someone might be able to help me with something i am trying to write:

Sheet 1 (input tab): Row 1 contains Mon-Fri, with the rows below detailing what car parking spaces are available for those days, for example, if space 18 is available on monday cell A2 will say 18.

Sheet 2 (parking list tab): this tab has employees in column A, column B dictates whether the employee needs a space or not, so they can be in a "space not needed" state, and then column C-G is the week days.

What i wanted to do was update sheet1 with any available spaces for the week and then run a macro that would then assign the spaces to employees that require a space that week, so it would not assign to those in a "space not needed" state.

I was hoping once this macro was run, sheet1 would contain no spaces so that if anymore spaces become available, we could update sheet1, run the macro, and the next employees due to receive a space would be allocated the space.

I have no idea if this is possible, but i was hoping someone might be able to help?

I look forward to any response

Thanks,

Adam

Hi,

I have attached a file (hopefully!)

basically, on the input tab, you can see in each column what spaces are currently available that day, and what i want the code to do is allocate the available space on the temporary parking list tab to person 1 etc etc...
on the temporary parking list tab there is a space not needed criteria, so i was hoping to macro would not give a space to people who had this populated in column B.
So from the attachment, i would expect cell D9 to have 7, as this is the first space on the input tab on Monday and person 8 is the first person who requires a space.

Thanks,

p.s. do let me know if that still is insufficient, i can try and write it better.

I did miss out in my previous post where i would like the input tab to be clear once the macro has been ran, so new spaces that become available can then be entered in the input and the macro can be ran again to allocate these spaces to the next person on the list.

Thanks,

Apologies, i was unaware of the cross-posting rules, i have also created these pages:

i am unable to post the links due to the following error:

"The following errors occurred with your submission
You are not allowed to post any kinds of links, images or videos until you post a few times."

I am unclear from your sheet, how is a space that is not needed identified? it appears they are shaded gray from your list sheet, but your answer suggest they are the 2 paler colours

are you just wanting to go down the column and fill the non dark gray cells with the people who require parking?

If you have not been able to allocate spaces you want to plabk the input tab?


your sample is small, create an after sheet for how you wish the temporary parking sheet to look after the macro has run

Yeah that works
Thank you for putting up with me, i will improve the way i request help going forwards.

yes the other was on stack overflow.

And i understand that people are offering their time for free and i will not cross post again

For reference this is the one on stack
https://stackoverflow.com/questions/...parking-spaces

Hi,

I unfortunately forgot that a person is able to not require a space for just one day of the week, in this instance they will have an "x" placed in whatever day they do not require a space on sheet 2 (Temporary Parking List).

So looking in the attachment, person 11 has an x placed on Wednesday (F12), so due to this, space 100 on sheet1(Input) is now no longer being allocated.

is there any way to allow for this? So essentially i want something that would allocate all the available spaces on the input tab but be intelligent enough to see that there is an x so do not allocate a space on this day to this person.

Thank you for any help and apologies for missing this in my initial requirements.

Thanks,

Adam

Is there a maximum number of spaces and maximum number of persons.
torachan.

The spaces range from 1-200 and also T1-T100, as there are 2 car parks. So spaces can be any number between 1-200 and T1-T100.
The maximum number of persons will be the amount of employees at that site, so approximately 1000.

Ok from first checks that looks to be doing exactly what i want! Thank you so much