how to use If function in excel using vba on entire column

[m_vishal_c]

hi Everyone
i have one excel workbook. The column structure is as below
AY AZ BB
date date -------

there are more then 4000 records(rows) in AY and AZ column. I need if function like if(AZ>=AY,"",AY) through vba. this formula will be placed on BB column

is anybody help me out

heaps thanks in advance

thanks

 

[FDibbins]

Why not just copy the formula down as needed? dragging down 4000 rows should take seconds

 

[m_vishal_c]

Why not just copy the formula down as needed? dragging down 4000 rows should take seconds

hi Thanks for reply. this is not only one formula i have to place this formula in vba long code. there are such more formulas. i can do it manually but i want to update by vba not manually

 

[Marcelo Branco]

Maybe

Range("BB2:BB" & Cells(Rows.Count, "AZ").End(xlUp).Row).Formula = "=IF(AZ2>AY2,"""",AY2)"

Assumes data begin in row 2

M.

 

[m_vishal_c]

Maybe

Range("BB2:BB" & Cells(Rows.Count, "AZ").End(xlUp).Row).Formula = "=IF(AZ2>AY2,"""",AY2)"

Assumes data begin in row 2

M.

hi Marcelo Branco. thanks for replying. i think this will work only row no 2 . i need to for entire column. i have 4000 rows

 

[Marcelo Branco]

hi Marcelo Branco. thanks for replying. i think this will work only row no 2 . i need to for entire column. i have 4000 rows

No, no, it works for all rows in the range BB2:BBx where x is the last row with data. Have you tried?

M.

 

[m_vishal_c]

No, no, it works for all rows in the range BB2:BBx where x is the last row with data. Have you tried?

M.

hi, i tried but it gives me this error " Run time error 1004; Application - defined or object - defined error

Please guide me
thanks

 

[m_vishal_c]

hi Marcelo Branco, sorry my bad, it works. so sorry for that. heaps thanks

 

[Marcelo Branco]

Great it worked!

M.

 

[m_vishal_c]

Great it worked!

M.

hi Marcelo Branco. Thanks for resolving my query. Actually I wanted to count only " not blank " cells in column that's why I needed that formula . it works but when I place below formula then It counts all. I need formula which count only " Not blank " cells in same column

=COUNTIFS(ABC!$B:$B,A5,ABC!$BB:$BB,"<>")

we placed your formula on BB column but when I place above formula to get "Not Blank" cell on BB column, it gives me wrong result . can you help me to find out only "Not Blank " cells in same column

heaps thanks