copy formual

[Trevor3007]

Hi,

I wanted to create a 'macro' to copy the formula =if(a6>0,a6,"") and =if(b6>0,b6,"").

using the record macro feature, produced the following:-

ActiveCell.FormulaR1C1 = "=IF(R[-1]C>0,R[-1]C,"""""
    Range("A7").Select
    Selection.Copy
    Range("B7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A7:B7").Select
    Selection.Copy
    Range("A8:A200").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A6").Select
End Sub[CODE=vba]

[/CODE]

after running the macro, & inserting data into a6 & b6, returned the following ( see fig 1)

as you can se, it should of copied the data from a6 & b6 to a200:b200


can anyone solve this for me?

 

[johnnyL]

Please explain exactly what you are trying to accomplish.

It appears:
you want cell A7 to have the formula of =if(a6>0,a6,"")
you want cell B7 to have the formula of =if(b6>0,b6,"")
Not sure what you want after that. Are you trying to copy those formulas down to row 200?

Can you explain further what exactly you are trying to do?

 

[JoeMo]

Does this do what you want?

Sub try()
Range("A7:B7").Formula = "=IF(A6>0,A6,"""")"
Range("A7:B200").FillDown
End Sub

 

[johnnyL]

Dang it @JoeMo You always stealing my thunder

Sub TestCopyDown()
'
    Range("A7").Formula = "=IF(A6>0,A6,"""")"
    Range("B7").Formula = "=IF(B6>0,B6,"""")"
'
    Range("A7:B7").AutoFill Destination:=Range("A7:B200"), Type:=xlFillDefault
End Sub

 

[Trevor3007]

Does this do what you want?

Sub try()
Range("A7:B7").Formula = "=IF(A6>0,A6,"""")"
Range("A7:B200").FillDown
End Sub

thnak you...works a treat.

have a great rest of your day.

PS
Sorry for the late reply, just gotten chance to test/try ?

 

[JoeMo]

thnak you...works a treat.

have a great rest of your day.

PS
Sorry for the late reply, just gotten chance to test/try ?

You are welcome - thanks for the reply.