Excel vba application-defined or object-defined error

[dgrimm]

I am getting an error EXCEL VBA APPLICATION-DEFINED OR OBJECT-DEFINED ERROR on the last line of the loop. I have looked it up but with nothing applying that I have read.

For i = 1 To PatCnt - 1

Range("E" & i + 1).Formula = _
"=IF(ISNA(VLOOKUP(" & """Patient:""" & "," & "PatientL" & i & ",3,FALSE))," & """""" & ",VLOOKUP(" & """Patient:""" & "," & "PatientL" & i & ",3,FALSE))"
Range("F" & i + 1).Formula = _
"=IF(ISNA(VLOOKUP(" & """Primary:""" & "," & "PatientL" & i & ",3,FALSE))," & """""" & ",VLOOKUP(" & """Primary:""" & "," & "PatientL" & i & ",3,FALSE))"
Range("G" & i + 1).Formula = _
"=IF(ISNA(VLOOKUP(" & """Secondary:""" & "," & "PatientL" & i & ",3,FALSE))," & """""" & ",VLOOKUP(" & """Secondary:""" & "," & "PatientL" & i & ",3,FALSE))"
Range("H" & i + 1).Formula = _
"=IF(ISNA(VLOOKUP(" & """Patient Total:""" & "," & "PatientL" & i & ",43,FALSE))," & """""" & ",VLOOKUP(" & """Patient Total:""" & "," & "PatientL" & i & ",4,FALSE))"
Range("I" & i + 1).Formula = _
"=IF(OR(AND(CELLS(" & """E""" & i + 1 & ") = """""" & "",CELLS(" & """F""" & i + 1 & ")=" & """""" & ",CELLS(" & """G""" & i + 1 & ")=" & """""" & "),MAX(CELLS(" & """E""" & i + 1 & "):CELLS(" & """G""" & i + 1 & "))>NOW()-365)," & "OK" & "," & "Dated" & ")"""

Next i

the vlookups work fine it is the if(or(and line that is messing up.

Any help would be greatly appreciated.

Thanks

David

 

[Jonmo1]

Try

Range("I" & i + 1).Formula = _
"=IF(OR(COUNT(E" & i + 1 & ":G" & i + 1 & ")=0,MAX(E" & i + 1 & ":G" & i + 1 & ")>NOW()-365),""OK"",""Dated"")"

I took the lliberty of changing
AND(Ei+1="",Fi+1="",Gi+1="")
to
COUNT(Ei+1:Gi+1)=0

MUCH easier to write in code, and should give same results.

 

[Jonmo1]

Also, you are way overcomplicating the process of concatenating variables into a formula...

When the end result of the formula you want contains quotes
you just have to double up those quotes.
You don't have to concatenate in a new string of quotes..

The only part(s) that requires concatenation is the variable (i)


This

Range("E" & i + 1).Formula = _
"=IF(ISNA(VLOOKUP(" & """Patient:""" & "," & "PatientL" & i & ",3,FALSE))," & """""" & ",VLOOKUP(" & """Patient:""" & "," & "PatientL" & i & ",3,FALSE))"

can be changed to

Range("E" & i + 1).Formula = _
"=IF(ISNA(VLOOKUP(""Patient:"",PatientL" & i & ",3,FALSE)),"""",VLOOKUP(""Patient:"",PatientL" & i & ",3,FALSE))"

Hope that helps.

 

[dgrimm]

Thank you both, I greatly appreciate the help.

Dave