VBA Cells to move down

[Jyters]

Hi there,

I am building an interactive dashboard for our Asphalt Department and when they do their program or planning one of the queries is that when they have planned work on a specific date, and, it gets rained off. They have the option to select rained off. but they want the planned dates below that to automatically shift down a day. Ultimately pushing the program out when ever there is a rained off day.

In the attached example. When Rain off in Column H is selected all cells below except Column B will move down by the number of days in Column D in the row of Rain Off.


Thanks,

 

[Ezguy4u]

jyters Let's get the ball a rollin. First is this close to what you had in mind. I cut your dashboard down but it should still work. Now one of the questions I have is notice that the 5th day is on a Saturday. Also note that sometimes the number of days could go thru a Sunday. Do you want the schedule showing work on the weekend? Once you approve or change the below we can move on to the next step.

24-07-11 Nothern Program rev A.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Region	Date Start	Day	Duration Working	Day Finish	Supervisior	Manhours	Client	Work Order	Activity Codes	Location
3
4	Northern	1-Jul-24	Mon	1	1-Jul-24	Johan		Dairy	5569		Black Water
5		2-Jul-24	Tue
6	Northern	3-Jul-24	Wed	1	3-Jul-24	Johan		Rain Off	5569		River Run
7		4-Jul-24	Thu
8		5-Jul-24	Fri
9		6-Jul-24	Sat
10		7-Jul-24	Sun
11	Northern	8-Jul-24	Mon	5	12-Jul-24	Johan	8	Dairy	5569		Black Water
12		9-Jul-24	Tue
13		10-Jul-24	Wed
14		11-Jul-24	Thu
15		12-Jul-24	Fri
16		13-Jul-24	Sat
17		14-Jul-24	Sun
18		15-Jul-24	Mon
Input

24-07-11 Nothern Program rev A.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Region	Date Start	Day	Duration Working	Day Finish	Supervisior	Manhours	Client	Work Order	Activity Codes	Location
3
4	Northern	1-Jul-24	Mon	1	1-Jul-24	Johan		Dairy			Black Water
5		2-Jul-24	Tue
6		3-Jul-24	Wed					Rain Off
7	Northern	4-Jul-24	Thu	1	4-Jul-24	Johan			5569		River Run
8		5-Jul-24	Fri
9		6-Jul-24	Sat
10		7-Jul-24	Sun
11		8-Jul-24	Mon
12	Northern	9-Jul-24	Tue	5	13-Jul-24	Johan	8	Dairy	5569		Black Water
13		10-Jul-24	Wed
14		11-Jul-24	Thu
15		12-Jul-24	Fri
16		13-Jul-24	Sat
17		14-Jul-24	Sun
18		15-Jul-24	Mon
Output

 

[Jyters]

Hi there, thank you so much for your response. What is shown is what I was asking. Just want the dates below the rain off to shift down, as the power query would return a blank date for the rain off as shown. The dates are set as Workday.Intl as we mostly work at nights, Sonday evenings will be our 1st working day. Saturdays will be a non working day. I do however have another question on the same worksheet, do i have to create a different threat? Regards,

 

[Ezguy4u]

jyters Any other questions about this worksheet use this thread. So, it sounds like the workers don't want to work in the heat of the day so let's start the workday at night. So it sounds like the work week starts Sunday night to Monday morning and the work week ends Friday night to Saturday morning. This shouldn't be a problem with the schedule. Now I believe the best solution to this problem would be to use a program or macro. Do you know anything about visual basic programing that is connected to excel? If you don't, it just means we have to go a little slower. If you do know then we can cut to the chase.

 

[Jyters]

jyters Any other questions about this worksheet use this thread. So, it sounds like the workers don't want to work in the heat of the day so let's start the workday at night. So it sounds like the work week starts Sunday night to Monday morning and the work week ends Friday night to Saturday morning. This shouldn't be a problem with the schedule. Now I believe the best solution to this problem would be to use a program or macro. Do you know anything about visual basic programing that is connected to excel? If you don't, it just means we have to go a little slower. If you do know then we can cut to the chase.

Thanks, i have very little knowledge of VBA, i only have researched some VBA's I am using, Currently also on this worksheet there is a Worksheet protection VBA. I have recorded and used macros before, but slow is better. As i would like to understand what is being coded. Another question on this query, we have about 8 Regions/Crews running. Can the VBA code have a section where there are more worksheets added, the worksheet name can be added in the code for the VBA to work on those sheets?

 

[Ezguy4u]

jyters VBA is very powerful and yes you can add worksheets to a workbook using VBA. We just have to take it one step at a time so let's work on the problem above.

 

[Ezguy4u]

jyters here is my solution to the first problem. Now I usually get something wrong so let me know what it is. I am going to assume you know how to copy and paste this program to your excel workbook and save it as an Excel Macro. This usually generates more questions than answers so let the discussion begin.

Sub RainDelay()
Dim CellCnt As Integer
Dim strRng As String
Dim Row1 As Integer
'this line will count how many rows in column H
CellCnt = Cells(Rows.Count, "H").End(xlUp).Row
'we loop from the bottom of H up
For i = CellCnt To 3 Step -1
'we go from the bottom up because you could have another rain day after the first rain day
If Cells(i, 8) = "Rain Off" Then
Row1 = i
For n = CellCnt To i Step -1
'this if statement fills down the number of days. Now you will have 2 dates showing
If Cells(Row1, 4) <> "" Then
    Range((Cells(Row1, 4)), Cells(Row1 + 1, 4)).Select
    Selection.FillDown
    Row1 = Row1 + 1
End If
 
Row1 = Row1 + 1
Next n

'this line just makes a string that looks like this Range("Ai,Fi:Gi,Ii:Ki,Hi).Now the i is a variable for the row number. The , and : gives us the spacing.
strRng = "A" & i & "," & "F" & i & ":" & "G" & i & "," & "I" & i & ":" & "K" & i & "," & "H" & i + 2
Range(strRng).Select
'this line just shifts the selected cells down one cell.
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'we have to exit the loop after this operation.
Exit For
End If

Next i

Range("A1").Select

End Sub

24-07-11 Nothern Program rev G.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Region	Date Start	Day	Duration Working	Day Finish	Supervisior	Manhours	Client	Work Order	Activity Codes	Location
3
4	Northern	01-Jul-24	Mon	1	Tuesday, July 2, 2024	Johan		Dairy	5569		Black Water
5		02-Jul-24	Tue
6	Northern	03-Jul-24	Wed	2	Friday, July 5, 2024	Johan		Dairy	5569		River Run
7		04-Jul-24	Thu
8		05-Jul-24	Fri
9		06-Jul-24	Sat
10		07-Jul-24	Sun
11	Northern	08-Jul-24	Mon	5	Sunday, July 14, 2024	Johan	8	Dairy	5569		Black Water
12		09-Jul-24	Tue
13		10-Jul-24	Wed
14		11-Jul-24	Thu
15		12-Jul-24	Fri
16		13-Jul-24	Sat
17		14-Jul-24	Sun
18		15-Jul-24	Mon
19		16-Jul-24	Tue
20		17-Jul-24	Wed
21		18-Jul-24	Thu
Work Schedule

Cell Formulas
Range	Formula
C4:C21	C4	=WEEKDAY(B4)
E4:E21	E4	=IF(D4="","",WORKDAY.INTL(B4,D4,17))

 

[Jyters]

Hi, thanks, but i did copy and paste firstly created a new module, changed the "H" to "G" as since i have made the query changed some columns. After I created a new module and saved the document as xlsm re-open it and selected the Rain Off on Column "G" nothing happened. Also, copied the VBA below an existing VBA for Protect Password, Changed the column back to "H" and added a selection for Rain Off. It still did not work. hehe as you predicted there might have been something missing

 

[Ezguy4u]

jyters if I am reading you correctly, it sounds like you made changes to the Work Schedule worksheet. A problem there is the program really only works with the worksheet above. Let's try this. You can open a new workbook, go to the above worksheet, Work Schedule, go to just below the upper left corner, copy and paste to the new workbook. Then copy and paste the program to a module in the new workbook. It sounds like you have a handle on that part. I need to have you see the program work and say yes that is what I want. Or no that isn't what I want and here is what I want. You know jeyters, I too had problems with the Rain Off if statement. I finally just went to the program and copied the Rain Off from the program and pasted it in a cell. So if you have any questions let us know.

 

[Jyters]

Hi, sorry for my delayed response. On your message when you send me the VBA, there is no attachment to test the scenario. And I cant edit the cells on the message either. So there is no way for me to confirm if it indeed works. Furthermore, you mention that I should copy and paste the VBA on my worksheet, which I did and had to make the changes as I had changed the columns in the original example. I did not save the original example send. if that is where you meant for me to copy and paste the VBA? Look forward to your reply..