PLEASE help

[Guest]

How do I write this as a macro for 200 different rows?

If F2 is 10 or more, and G2 is >= D2, THEN OK
If F2 is 10 or more, and G2 is < D2, THEN NOT OK
If F2 is <10, and E3 is >= D3, THEN OK
If F2 <10, and E3 is < D3, THEN NOT OK

Thanks so much if you can figure this out!!

 

[Tom Ogilvy]

You don't say what to do with the determination, so perhaps you can adjust
this. Although you don't say what to do if F is exactly 10

Sub AABBCC()
For i = 2 To 202
Set rngF = Cells(i, "F")
Set rngG = Cells(i, "G")
Set rngD = Cells(i, "D")
Set rngD1 = Cells(i + 1, "D")
Set rngE = Cells(i + 1, "E")

If rngF > 10 And rngG >= rngD Then
res = "OK"
ElseIf (rngF > 10 And rngG < rngD) Then
res = "Not OK"
ElseIf (rngF < 10 And rngE >= rngD1) Then
res = "OK"
ElseIf (rngF < 10 And rngE < rngD1) Then
res = "Not OK"
Else
res = "anomaly"
End If
MsgBox "row: " & i & " result: " & res
Next

End Sub

 

[Guest]

Hi Gale,

A VBA function:


Function bCheckMyCells() As Boolean
Dim r As Long, lNextRow As Long
Dim bOK As Boolean

For r = 2 To 201
Select Case ActiveSheet.Range("F" & r).Value
Case Is >= 10 And _
Range("G" & r) >= Range("D" & r): CheckMyCells = True
Case Is >= Range("D" & r) And _
Range("G" & r) <= Range("D" & r): CheckMyCells = False
Case Is < 10 And _
Range("E" & r + 1) >= Range("D" & r + 1): CheckMyCells = True
Case Is < 10 And _
Range("E" & r + 1) < Range("D" & r + 1): CheckMyCells = False
End Select
Next r

End Function

To test the result, use it like this...

'Test for OK
If bCheckMyCells Then... 'do something

OR
'Test for NOT OK
If Not bCheckMyCells Then... 'do something

---
If you'd rather use an in-cell formula:
This is a rather long formula so I put it in lines to match your request.
Make sure it's one continuous line when you enter it. It assumes you want to
leave the cell "" if no conditions are true.

=IF(AND(F2>=10,G2>=D2),"OK",
IF(AND(F2>=D2,G2<=D2),"NOT OK",
IF(AND(F2<10,E3>=D3),"OK",
IF(AND(F2<10,E3<D3),"NOT OK",
"")))) '<=this part leaves the cell blank (not to be confused with
"empty")

HTH
Regards,
Garry

 

[Guest]

A correction for the function:

Function bCheckMyCells() As Boolean
Dim r As Long
Dim bOK As Boolean

For r = 2 To 201
Select Case ActiveSheet.Range("F" & r).Value
Case Is >= 10 And Range("G" & r) >= Range("D" & r): bCheckMyCells = True
Case Is >= Range("D" & r) And Range("G" & r) <= Range("D" & r):
bCheckMyCells = False
Case Is < 10 And Range("E" & r + 1) >= Range("D" & r + 1):
bCheckMyCells = True
Case Is < 10 And Range("E" & r + 1) < Range("D" & r + 1):
bCheckMyCells = False
End Select
Next

End Function

Sorry about that!
Regards,
Garry

 

[Guest]

Tom raises a good point about what to do with the result, which I did not
focus on AND subsequently left out. Here's something that might work better
if you want to act on the results separately from the test:

'Use the following sub to process your task.
'It calls the bCheckMyCells() function (modified version included here).

Sub CheckMyCells()
Dim r As Long, lNumRows As Long, lStartRow As Long

lStartRow = 2
lNumRows = lStartRow + 199

For r = lStartRow To lNumRows
If Cells(r, "F") <> "" Then
If bCheckMyCells(r) Then
'do some OK thing
Else
'do some NOT OK thing
End If
End If
Next r
End Sub


'This is a revised version of my original concept using Cells()
' (borrowed from Tom's suggestion).
Function bCheckMyCells(ByVal Row As Long) As Boolean
Select Case ActiveSheet.Range("F" & Row).Value
Case Is >= 10 And _
Cells(Row, "G") >= Cells(Row, "D"): bCheckMyCells = True
Case Is >= Cells(Row, "D") And _
Cells(Row, "G") <= Cells(Row, "D"): bCheckMyCells = False
Case Is < 10 And _
Cells(Row + 1, "E") >= Cells(Row + 1, "D"): bCheckMyCells = True
Case Is < 10 And _
Cells(Row + 1, "E") < Cells(Row + 1, "D"): bCheckMyCells = False
End Select
End Function

Regards,
Garry

 

[Guest]

I'm new at this and please bear with me. I'll copy a few lines of my
spreadsheet to hopefully illuminate you both as to what I'm trying to do:

Name Month of Birth Date of Birth "No sooner than
30 days
Before BD" "
Completed" Result "TBQ
Completed" Comment
JONES,AMELIA Jan 1/4 12/5 02/01/79 12 4-Jan-06 Compliant
NEWS,TODD Jan 1/5 12/6 01/20/06 0 Compliant
SMITH,JOHN Feb 2/10 1/11 03/14/95 25 2-Feb-06 Compliant
FAKE,NAME Feb 2/15 1/16 02/08/06 0 Compliant
EXCEL,SAMMY Feb 2/16 1/17 11/03/97 19 27-Jan-06 Compliant
GUESS,MYNAME Mar 3/3 2/2 07/11/90 14 1-Mar-06 Compliant
SOMETHING,EROTHER Mar 3/4 2/2 03/13/06 0 Compliant
BABY,BROTHER Mar 3/8 2/6 02/01/06 0 Noncompliant

Highlight and copy into a spreadsheet. I would like column "H" to show
compliant or noncompliant.
Thanks,
gale

 

[Guest]

Hi Gale,

You could put the following formula into H2 and copy it down as many rows as
you need to. Note that this formula must be entered as one continuous line.

=IF(AND($F2>=10,$G2>=$D2),"Compliant",IF(AND($F2>=10,$G2<=$D2),"Noncompliant",IF(AND($F2<10,$E3>=$D3),"Compliant",IF(AND($F2<10,$E3<$D3),"Noncompliant,""))))

Note that this formula would be a lot easier for users to understand if you
used local defined names for your columns, that were column-absolute,
row-relative. That way the formula would read something like this:

=If(AND(YrsService>=10,TestDate>=NotBeforeDate)...

where YrsService might be the defined name for column F, TestDate for column
G, and NotBeforeDate for column D. The row would then be relative to the cell
containing the formula.

You enter the defined name as follows:

For YrsService:
Select any cell in column F, then
Insert>Name>Define...
in the "Names In Workbook:" box type
'Sheet1'!YrsService
click in the "Refers To:" box and press F2, then
remove the $ sign in front of the row number.
Click the "Add" button.

Repeat for each additional name, being sure to select a cell in the
respective column.
---

If you prefer to use VBA, you can edit CheckMyCells() sub as follows:

Replace: 'do some OK thing
with: Cells(Row, "H").Value = "Compliant"

Replace: 'do some NOT OK thing
with: Cells(Row, "H").Value = "Noncompliant"

 

[Guest]

Hello Garry,

Thanks so much for solving my problem! I used the one continuous line
and it worked like a charm. Wow - I can't begin to thank you enough!

Regards.
Gale

 

[Guest]

You're welcome! -I'm glad to help.

Thanks for the feedback!
Regards,
Garry