AaliyahN82
New Member
- Joined
- Apr 24, 2020
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
So I am working on a project that is connected to an Access database. On button click it will retrieve everything that's in my Access table to my Excel file then it will create a table which shows which weeks each person has data in the database. If they have data for that week (week beginning is always a Monday), it will show 1 and if there's none, it will show 0. Then all 1s will be converted to Y and all 0s will be converted to N.
However, I needed to add two columns on my data sheet (which contains a list of names of the people I am checking the above against) saying Week Start Date and Week End Date. The Week Start Date is the week date (week beginning Monday) when the person initially sent data, and the Week End Date is the week date (week beginning Monday) the person is set to send data.
I need to revamp the below code to also check the two columns. Like the Y/N will only start on the date indicated in the Week Start Date column and will end on the date indicated in the Week End Date column. The rest of the dates will show as NA. However if the two columns are empty then it will do Y/N as usual.
here's my code:
I also posted here: Put NA on week dates not in range
and in vbaexpress however the site is currently down and could not put a link to the thread that's been unanswered for maybe 4-5 days already.
However, I needed to add two columns on my data sheet (which contains a list of names of the people I am checking the above against) saying Week Start Date and Week End Date. The Week Start Date is the week date (week beginning Monday) when the person initially sent data, and the Week End Date is the week date (week beginning Monday) the person is set to send data.
I need to revamp the below code to also check the two columns. Like the Y/N will only start on the date indicated in the Week Start Date column and will end on the date indicated in the Week End Date column. The rest of the dates will show as NA. However if the two columns are empty then it will do Y/N as usual.
here's my code:
VBA Code:
For Each cell In rng
If cell.Value = "0" Then
'write to adjacent cell
cell.Value = "N"
cell.Interior.Color = 13551615
Else
cell.Value = "Y"
cell.Interior.Color = 13434828
End If
Next
I also posted here: Put NA on week dates not in range
and in vbaexpress however the site is currently down and could not put a link to the thread that's been unanswered for maybe 4-5 days already.