Excel Formula in VBA

[dandy]

How would I put the following Formulas into VBA and have them autofill all adjacent cells?

=IF(A2<>A1,B2,C1 &" " &B2)

=IF(A2<>A3,CONCATENATE(C2,""),"")

 

[bellman101]

Dandy,

You can enter a formula by just saying range.formula = "DesiredFormula"

The only adjustment to make the formula VBA friendly, is to double up any speech marks, so if the target cells are D2 & E2, your code would look like this

Range("D2").formula = "=IF(A2<>A1,B2,C1&"" ""&B2)"
Range("E2").formula = "=[COLOR=#333333]IF(A2<>A3,CONCATENATE(C2,""""),"""")"[/COLOR]

As for autofilling, you may need some dollars in your formulae, to keep to a certain row/column/cell, but you can autofill formulae like this:

Range("D2:E50").formula = Range("D2:E2").formula
OR
Range("D2:E2").resize([I]DesiredRows[/I]).formula = Range("D2:E2").formula

Hope that helps.

JB