IF OR FORMULA in VBA

Hi,

I have recorded a macro in excel
There are two sheets named "Unique DATA" and "DATA"

below formula was recorded in sheet Unique DATA C2 and draged till c10000
=(IF(OR(DATA!P7="SUB",DATA!P7="SUB1"),DATA!B7,""))

VBA script is as follow :

heets("Unique DATA").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "SUB"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(DATA!R[5]C[13]=""SUB"",DATA!R[5]C[13]=""SUB1""),DATA!R[5]C[-1],"""")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C10000"), Type:=xlFillDefault
Range("C2:C10421").Select

However this is not working on other data. Can any one Help.

Nick's picture

you need to define what "not

you need to define what "not working" and "other data" mean

A better way to do this autofill is below:

Sheets("Unique DATA").Select
Range("C1").FormulaR1C1 = "SUB"
EndRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("C2:C" & EndRow).FormulaR1C1 = _
 "=IF(OR(DATA!R[5]C[13]=""SUB"",DATA!R[5]C[13]=""SUB1""),DATA!R[5]C[-1],"""")"
- this way picks up the end cell from the previous column