Enter formula in Macro/VBA

[vtj]

I have a sheet that I want to put formulas into. The formula that I want to
paste in is similar to +A2+B2-C2+D2 with the result going into E2. If I put
that into the E2 location, the following will paste that formula all the way
down the sheet after I insert a Do Loop:

Sub Macro6()
'
' Macro6 Macro
'

'
Range("N187").Select
Selection.Copy
Debug.Print (xlFormulas)
Range("N188").Select
Debug.Print (xlFormulas)
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

The Debug.Print always shows -4123 as the value even if I use a different
formula or have it in different columns or rows. I am using Excel 2007
running on XP.

What I want to do is be able to define a formula in the Macro/VBA that I
could then paste so that I could change the formula whenever I needed to
based on the sheet that I'm working with.

Any help will be greatly appreciated. Thanks.

 

[Bob Phillips]

Sub Macro6()
With Range("E2")

.Formula = "=A2+B2-C2+D2"
.Copy .Offset(1, 0)
End With
End Sub

 

[Dave Peterson]

Excel's VBA has a bunch of constants that make coding easier to understand.

For instance, vbRed is a constant that is equivalent to 255.

That makes this kind of code:
activesheet.range("a1").font.color = vbred
easier to understand than
activesheet.range("a1").font.color = 255

The code will do the same, but one is easier to understand.

Same thing with xlformulas. It's a constant that makes the code easier to use.

If you wanted to retrieve the formula of a single cell, you could use:

dim myFormula as string
myformula = activecell.formula

I'd recommend that you record a macro while you enter a formula in the cell and
you'll see how it works:

with activesheet
.range("e2").formula = "=a2+b2-c2+d2"
'then fill down
end with

Manually you can select the range (E2:E99, say) type the formula for E2 and hit
ctrl-enter to fill the selection with that formula. Excel will adjust the
formula (if you write it nicely) to what you want.

You can do the same thing in code.

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("E2:E" & lastrow).formula = "=a2+b2-c2+d2"
end with

LastRow is based on the last used cell in column A. That may not be what you
want. Change the "A" to what you need.

 

[vtj]

Thank You very much both Bob and Dave. You have saved me a lot of work!!