Home » Forums » Open Source Learning Center » Question and Answer

IF OR FORMULA in VBA

Submitted by Benjamin Anthony on 5 October, 2012 - 12:00

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.

‹ I need to Sheets.Add a single sheet of a template containing two sheets Numbers convert into words ›
»
Nick's picture

you need to define what "not

Submitted by Nick on 5 October, 2012 - 12:29.
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
»