Excel VBA IF Then Else Loop Output Not Returning Different Statements Based on Criteria

[SSap]

Please help! I'm fairly new to working with macros and I am having trouble figuring out how to get my macro to loop within an If Then Else so that it will run a column and check if 'Hours' are either:

1. Greater than 'AvailHours' Then the output for 'Reason' would be "Not enough hours earned"
2. 'Hours' < 4 Then the output for 'Reason' would be "Pay for 4 hours"
3. 'Hours' >= 4 Then the output for 'Reason' would be "Pay for 8 hours"
4. If the output for 'Reason' is "Not enough hours earned" then I need that row to move to the bottom

I have tried different macros and I either just get only one of the output statements all the way down the Reason column or errors in my macro not allowing it to complete. I am working in sheet EXCEPTIONS in a workbook with other sheets.

Is there a better way to write my macro to get the desired results that I need....

This is what I have:

'Check if enough Avail Sick Time Hours and Round Hours Worked
Range("EXCEPTIONS[Hours]").Select

For Each cell In Range("EXCEPTIONS[Hours]")
If Value < "EXCEPTIONS[AvailHours]" Then
Range("EXCEPTIONS[Reason]").Value = "Not enough hours earned"
ElseIf Value < 4 Then
Range("EXCEPTIONS[Reason]").Value = "Pay for 4 hours"
ElseIf Value >= 4 Then
Range("EXCEPTIONS[Reason]").Value = "Pay for 8 hours"
End If

Next cell



Output Reason I Get:

This is what I am trying to get it to do for the Reason:

 

[CSmith]

Seems you're logic is not setting it to the cell value and only your first condition is being seen and your reference is off too . . .
Maybe something like:

Sub mre_1235604()
'Check if enough Avail Sick Time Hours and Round Hours Worked

    ' See below Range("EXCEPTIONS[Hours]").Select ' If you are using a Table style reference you should use Sheet("SheetName").ListObjects("EXCEPTIONS").ListColumns("Hours").Select
    
    For Each tblCell In Sheets("MrExcel-1235604").ListObjects("EXCEPTIONS").ListColumns("Hours").DataBodyRange
        With tblCell  'Not sure your logic is clear here; guessing at intent . . .  Also, tblCell.Row -1 to skip header
            If .Value < Sheets("MrExcel-1235604").ListObjects("EXCEPTIONS").ListColumns("AvailHours").DataBodyRange(tblCell.Row - 1).Value Then ' This should only initial check to see if there are enough hours before determining amount to pay
                If .Value >= 4 Then Sheets("MrExcel-1235604").ListObjects("EXCEPTIONS").ListColumns("Reason").DataBodyRange(tblCell.Row - 1) = "Pay for 8 hours"
                If .Value < 4 Then Sheets("MrExcel-1235604").ListObjects("EXCEPTIONS").ListColumns("Reason").DataBodyRange(tblCell.Row - 1) = "Pay for 4 hours"
            Else
                Sheets("MrExcel-1235604").ListObjects("EXCEPTIONS").ListColumns("Reason").DataBodyRange(tblCell.Row - 1) = "Not enough hours earned"
            End If
        End With
    Next tblCell
End Sub

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	EmpID	Name	Job	Hours	Rate	Pay	Reason	Budget Code	Blank	AvailHours	Job Code
2			234234432	8			Pay for 8 hours			53.153
3			234324234	7.5			Pay for 8 hours			9.33
4			234123214	4			Not enough hours earned			3.466
5			234324234	8			Not enough hours earned			6.8
6			234123214	4			Pay for 8 hours			6.8
7			123456789	3.5			Pay for 4 hours			5
MrExcel-1235604

 

[SSap]

Thanks, I was able to fix my code after reviewing your suggestion. This is what I ended up with.

'Check if enough Avail Sick Time Hours and Round Hours Worked
 Dim Cell As Range

    For Each Cell In Range("EXCEPTIONS[Hours]")
        With Range("EXCEPTIONS[Reason]").Cells(Cell.Row - 1)
            If Cell > Range("EXCEPTIONS[AvailHours]").Cells(Cell.Row - 1) Then
                .Value = "Not enough hours earned"
            ElseIf Cell < 4 Then
                 .Value = "Pay for 4 hours"
            ElseIf Cell >= 4 Then
                 .Value = "Pay for 8 hours"
            End If
         End With
    Next Cell