For, IF, Offset, move to next if not blank

[Lovelylou79]

Hi Folks,

I have a basic Loop and If statement fr populatnga Tasks/Roster type sheet.

Currently, the code will populate fields based on the value in the range.

What I would like to do, is if the offset cell is not blank, for the next blank cell to be populated instead.
I have tried a number of ways to do this, but they are all bulky.

I would greatly appreciate some assistance. Thanks!



Dim i As Integer


For ii = 2 To 11
For i = 4 To 6
If InStr(1, (Cells(i, ii).Value), "BANK") > 0 Then
Cells(i, ii).Offset(3, 0).Value = "1330 - Payment Release"
Cells(i, ii).Offset(6, 0).Value = "1330 - Payment Release"
Cells(i, ii).Offset(9, 0).Value = "1330 - Payment Release"
Cells(i, ii).Offset(12, 0).Value = "1330 - Payment Release"
Cells(i, ii).Offset(15, 0).Value = "1330 - Payment Release"
'New Week
Cells(i, ii).Offset(26, 0).Value = "1330 - Payment Release"
Cells(i, ii).Offset(29, 0).Value = "1330 - Payment Release"
Cells(i, ii).Offset(32, 0).Value = "1330 - Payment Release"
Cells(i, ii).Offset(35, 0).Value = "1330 - Payment Release"
'2nd Friday
Cells(i, ii).Offset(38, 0).Value = "1100 - Payment Release"
Cells(i, ii).Offset(39, 0).Value = "1300 - Payment Release"
End If
Next i
Next ii

 

[James006]

Hi,

Could you design a sample file (with no confidential data) and share it in the cloud ... :wink:

 

[Lovelylou79]

Hi James006,

I would love to, however i have no idea how to do that.

If it helps, the sheet is for a roster, Cells in Rows 4,5,6 are the allocated tasks. Each day belowcontains 3 rows, ie, Monday is rows 7,8,9, Tuesday is 10,11,12 etc…
When a task is allocated in rows 4, 5 or 6 the cells in thedays may need to be populated with a specific duty for that task, for example,if Bank Details is selected, then 1330 – Payment Release needs to be addedevery day (as per the code above) What I need is for the code to check thefirst cell of the day, ie Monday row 7, and if it is not blank, then move tothe next row/cell in Monday, row 8, or 9 if 8 is also not blank. As is ispossible that several daily duties may need to be completed on any day.
This will allow auto population of duties through out theweek when a particular task is selected in rows 4,5,6.
I really hope this makes sense.