Loop to test range of cells

[Jason Campbell]

I am in the need of some assistance!

I need to evaluate all the cells that contain dates in column R, to the end of the range….however, I can expect that there will be dates missing, where the end users hasn’t input an ‘Agreed CR Due Date’. I have accounted for the empty cells in the if statements however, I need to construct a loop that will locate the last entry in column R, to make note of that cell reference and then proceed in checking cell by cell from column R4 down to the last column R entry cell reference already established.

Basically, I need to figure out the loop that I can encase my IF statements within… Based on the results of the IF statements, column T will be populated accordingly.

Below is an example spreadsheet layout and below it the code that have constructed so far, minus the loop part, which I can’t figure out… bit of a newbie!

Assistance will be kindly received!

	P	Q	R	S	T
3	Response to CIR	Agreed CR Due Date	CR Due Date	CR Sent Date	CRTime
4	CIR Bypassed!	N/A	10-Mar-17		X
5	Withdrawn		Withdrawn at CIR		Wdrwn@CIR
6	CCN - Await Confirmation Notice		CCN		CCN
7	Proceed CR Development - Due date agreed	Input Agreed Date!
8	Proceed CR Development - Due date NOT agreed	Date Not Agreed!	02-Jun-17

<tbody>
</tbody>

Range("R4").Activate
 
For
   
        If ActiveCell.Value = "Withdrawn at CIR" Or ActiveCell.Value = "CCN" Or ActiveCell.Value = "" Then
            ActiveCell.Offset(1, 0).Activate
        ElseIf ActiveCell.Offset(0, 1).Value = "" And Date <= ActiveCell.Offset(0, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "CR to be sent?"
            ActiveCell.Offset(1, 0).Activate
        ElseIf ActiveCell.Offset(0, 1).Value = "" And Date > ActiveCell.Offset(0, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "X"
            ActiveCell.Offset(1, 0).Activate
        ElseIf ActiveCell.Offset(0, 1).Value <= ActiveCell.Offset(0, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "OK"
            ActiveCell.Offset(1, 0).Activate
        ElseIf ActiveCell.Offset(0, 1).Value > ActiveCell.Offset(0, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "X"
            ActiveCell.Offset(1, 0).Activate
        End If
 
Next

 

[Jason Campbell]

I've figured it out! Whoop, Whoop!

I have had to refer to Column P, which would be consistent with data input from the top of the range to the last entry...referring to the count of rows derived from that column, I've then used that with the FOR loop to test each cell in the R Column until at the last entry on the sheet...the if statements have then done their job and populated column T appropriately.

Without Google and the help on this forum...I would be stuffed! Hopefully it will all start to become second nature!

Dim SH as Worksheet 
Dim CC as Long ' CC refers to 'Cell Check'
Set SH = Thisworkbook.Activesheet 

Range("R4").Activate

CC = SH.Range("P4", SH.Range("P4").End(xlDown)).Rows.Count

For i = 1 To CC
    
        If ActiveCell.Value = "Withdrawn at CIR" Or ActiveCell.Value = "CCN" Or ActiveCell.Value = "" Then
            ActiveCell.Offset(1, 0).Activate
        ElseIf ActiveCell.Offset(0, 1).Value = "" And Date <= ActiveCell.Offset(0, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "CR to be sent?"
            ActiveCell.Offset(1, 0).Activate
        ElseIf ActiveCell.Offset(0, 1).Value = "" And Date > ActiveCell.Offset(0, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "X"
            ActiveCell.Offset(1, 0).Activate
        ElseIf ActiveCell.Offset(0, 1).Value <= ActiveCell.Offset(0, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "OK"
            ActiveCell.Offset(1, 0).Activate
        ElseIf ActiveCell.Offset(0, 1).Value > ActiveCell.Offset(0, 0).Value Then
            ActiveCell.Offset(0, 2).Value = "X"
            ActiveCell.Offset(1, 0).Activate
        End If
Next