Urgent Please Help - Basic VB Looping

[jameslec]

Please excuse my newness to VB. I have a urgent task for work that I need to complete today, however I am stuck. I didn't think initially that I would need to use VBA to complete the task, but ultimately I do, and I am too new at it to complete it on my own. Below is the code with explanations. Thanks in advance for the help.

Sub Fill_Schedule()
Dim rngCell As Range
Dim rngAddress As String
For Each rngCell In Range("C10:C116") ‘This has the time slots I’m filling
If WorksheetFunction.CountIf(Range("DW11"), rngCell) = 1 Then ‘DW11 is in row 1 of the table and this cell contains the time corresponding to the first available slot in C10:C116
End If
Next
rngAddress = Range("DX11").Value ‘DX11 contains the cell address of the value of DW11
Range(rngAddress) = "Schedule" ‘Writes the word schedule in the cell specified by the value in DX11
End Sub

This code is working fine (although probably not nearly as efficiently as possible), but it only does the first row of the table (Row 11). I need it repeat for however many rows are in the table.
***Important note, it must do 1 row at a time. The results of row 1 affect the slot available for row 2***

 

[DSCfromCFA]

G'day James,

I am not a vba person in any way shape or form, but here is a link to a possible solution that sounds similar to yours

https://www.mrexcel.com/forum/excel-questions/908967-macro-trim-all-cell-contents-within-column.html

Good luck

shane

 

[Michael M]

you haven't finished the IF statement...

If WorksheetFunction.CountIf(Range("DW11"), rngCell) = 1 Then

 

[jameslec]

Yeah, I guess I din't which means that part isn't doing anything. But it is still doing what I want for that 1 row. Just to test I copied this part and manually added a few rows. See below. It works, but I need it to look at like 116 rows, and I don't want to have to copy that 115 times. Again, I really only know enough about VBA to get myself in trouble. Could you please advise how you would write this?

For Each rngCell In Range("C10:C116")
If WorksheetFunction.CountIf(Range("DW11"), rngCell) = 1 Then
End If
Next
rngAddress = Range("DX11").Value
Range(rngAddress) = Range("K11").Value
For Each rngCell In Range("C10:C116")
If WorksheetFunction.CountIf(Range("DW12"), rngCell) = 1 Then
End If
Next
rngAddress = Range("DX12").Value
Range(rngAddress) = Range("K12").Value

For Each rngCell In Range("C10:C116")
If WorksheetFunction.CountIf(Range("DW13"), rngCell) = 1 Then
End If
Next
rngAddress = Range("DX13").Value
Range(rngAddress) = Range("K13").Value

For Each rngCell In Range("C10:C116")
If WorksheetFunction.CountIf(Range("DW14"), rngCell) = 1 Then
End If
Next
rngAddress = Range("DX14").Value
Range(rngAddress) = Range("K14").Value

 

[Michael M]

Maybe this

Dim n As long
n = 11
For Each rngCell In Range("C10:C116")
    If WorksheetFunction.CountIf(Range("DW" & n), rngCell) = 1 Then
        rngAddress = Range("DX" & n).Value
        Range(rngAddress) = Range("K" & n).Value
        n = n + 1
    End If
Next rngCell

 

[jameslec]

Worked perfectly! Thank you so much.

Maybe this

Dim n As long
n = 11
For Each rngCell In Range("C10:C116")
    If WorksheetFunction.CountIf(Range("DW" & n), rngCell) = 1 Then
        rngAddress = Range("DX" & n).Value
        Range(rngAddress) = Range("K" & n).Value
        n = n + 1
    End If
Next rngCell

 

[Michael M]

glad to help, thx for the feedback...