COuntifs Then

[Godders199]

Hi, i have been trying to get the following to run, but keeps erroring, effectively if value in range (x,25) is contained in Range "i;I") i want "NA Protection" in (X,18), othereise blank

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To FinalRow
cells(X,18)= If (Application.WorksheetFunction.CountIfs(Worksheets("Non Advised").Range("I:I"), Worksheets("summary").Range(x, 25) > 0) Then Cells(x, 18).Value = "NA Protection" Else Cells(x, 18).Value = "")
Next x

Have put if at the start ,but it always highlighs u to "then" whatever i try.

Any ideas?

 

[Fluff]

Try

For x = 2 To finalrow
If Application.CountIfs(Worksheets("Non Advised").Range("I:I"), Worksheets("summary").Range(x, 25) > 0) Then Cells(x, 18).Value = "NA Protection" Else Cells(x, 18).Value = ""
Next x

 

[Godders199]

thanks fluff, it still does not like this part.
If Application.CountIfs(Worksheets("Non Advised").Range("I:I"), Worksheets("Summary").Range(x, 25) > 0) Then

error is Application-defined or object defined error

Have checked the worksheet names, all correct.

I can just record it, its just lot cleaner to do it this way keeps the workbook free of formula's

 

[Fluff]

This

Range(x, 25)

should be Cells not Range

 

[Godders199]

This

Range(x, 25)

should be Cells not Range

Hi, so changed to cells and now code runs, but does not return any results,

For x = 2 To FinalRow
If Application.CountIfs(Worksheets("Non Advised").Range("I:I"), Worksheets("summary").Cells(x, 25) > 0) Then Cells(x, 18).Value = "NA Protection" Else Cells(x, 18).Value = ""
Next x

If I type in the formula =IF(COUNTIFS('Non Advised'!I:I,Summary!Y2)>0,"NA Protection","") for example, it functions correctly.

Any ideas?

 

[Fluff]

How about

For x = 2 To FinalRow
If Application.CountIfs(Worksheets("Non Advised").Range("I:I"),  Worksheets("summary").Cells(x, 25)) > 0 Then Cells(x, 18).Value = "NA  Protection" Else Cells(x, 18).Value = ""
Next x