Insert textbox data into a specific empty cell in excel sheet

[Sam Summers]

Hi,
Been working on this for sometime but cant quite get it.
If this can be solved then the system is a goer if not its scrapped.

I am trying to make an efficient booking system that has to integrate with the users current well established Excel spreadsheet.
I have to be able to enter a string from the Access db form textbox "Name" which contains this format string "SMITH6748" into the empty cell of the Excel column for the date selected on the access form.

You can see the cell references in the attached screenshot .
How do i reference a cell that is a day or week from my current date based on this spreadsheet given that the month is briefly mentioned in row 2 and the day is in row 4?

Is this possible or may be i can make a slight change to the format of the Excel spreadsheet?

Any pointers would be massively appreciated!
Thank you in advance

 

[June7]

Suggest you provide workbook for analysis. Follow instructions at bottom of my post.

 

[Pat Hartman]

If the spreadsheet always contains 12 months, you know the absolute row and column you need by calculation. You just need to loop down the rows to find the first empty one.

 

[Sam Summers]

Thank you both very much. I have attached a cut down example with confidential data removed and less months just for this example.

 

[Sam Summers]

If the spreadsheet always contains 12 months, you know the absolute row and column you need by calculation. You just need to loop down the rows to find the first empty one.

Ill look further into this. I have been doing a lot of searching but very hard to find something to insert into particular empty cells.
Also in the top section (rows 5 - 90) they are for people requesting electrical hookups and then rows 105 - 180 are for non electrical pitches. but i could incorporate these options into the vba code.

 

[Gasman]

Perhaps insert placeholders and then search for those?

 

[theDBguy]

Ill look further into this. I have been doing a lot of searching but very hard to find something to insert into particular empty cells.
Also in the top section (rows 5 - 90) they are for people requesting electrical hookups and then rows 105 - 180 are for non electrical pitches. but i could incorporate these options into the vba code.

I'm not sure I understand the actual issue here. To insert data to a particular cell, you should be able to use a Range or Cell object. That is, if you knew which particular cell it is. So, perhaps the actual issue is not how to insert a data to a cell, how to find a particular cell?

 

[Pat Hartman]

Calculate the column number by adding the month to the number of fixed columns on the left. Then the first empty row starts on a specific row. I'm not looking at the workbook so I don't remember what these are. Then you have to create a loop that moves down the column one row at a time to find the first empty cell. You will need logic that ends the loop when no empty cell is found by a certain number of rows.

 

[June7]

Surely most anything is possible with enough code. It's just not clear to me what cell you want to find and populate with value of "SMITH6748". Outline the steps you would follow to make this determination.

 

[Pat Hartman]

Looks like the search would start with JD73. September 18 and then search for the first blank row.

Mind you, I think this is a really poor idea. But no one asked about that.

 

[Gasman]

Why not IY73?
I would have the month in all cells in row 2 (can be white on white, so appears hidden as per pictures), then you have a known range, then start from row 73 to where ever the rows are mean to end.

Unless you know these limits, I do not see how you can do this?

 

[Pat Hartman]

Why not IY73?

Didn't he say the next date after today?

 

[Gasman]

Didn't he say the next date after today?

Well the date apparently depends on whatever date is in the access form.

 

[Gasman]

I have been thinking about this.
Simple maths might be the answer.?

You know the date, so you can calculate the column using days in the months prior to that date and add any extra you might need for other data.
Then it is is just walking down that column until you find an empty cell or reach the end of where one should be?

 

[Pat Hartman]

Simple maths might be the answer.?

Isn't that what I said back in #8?

 

[Gasman]

Seems the same, except you were mentioning months and I was thinking days?

 

[June7]

Unfortunately, worksheet has multiple months and it is not simple to determine where a month starts. Might need to do some redesign to make coding easier.

 

[Gasman]

Unfortunately, worksheet has multiple months and it is not simple to determine where a month starts. Might need to do some redesign to make coding easier.

That is why I thought of days.
The pic shows Sept, so we have all the days for previous months as columns? plus those columns on the left.
Then as Pat states, walk the column until you find an empty cell or reach the end of where they should be?

 

[Pat Hartman]

Unfortunately, worksheet has multiple months and it is not simple to determine where a month starts. Might need to do some redesign to make coding easier.

The early assumption is that each workbook represents one year. That is pretty typical Excel design. Users don't seem to care that the book is hundreds of columns wide.

That is why you would always know where a month starts.
1. you know how many "prefix" columns exist.
2. you know the year so you know how long February is.

 

[June7]

Good points. Could hard code column reference of first day for each month in a SELECT CASE and calculate from there.