Write a macro to populate a column with a formula

[martinjw]

I suspect this is an elementary concept for VBA, hopefully this will b
an easy answer for y'all:
I would like to write a macro that populates a cell with a formul
given a certain condition is met in another cell. I setup a pivot tabl
that imports pertinent data from a master table. One column contain
the trigger info - an "M" means that we do not want the formula to b
imported otherwise we do want it. the column containing the trigge
will not move, likewise the destination formula will not move (no fanc
offsets required). I simply want the macro to recognize a non-"M" i
cell D4 and put my formula in F4.

The pivot table will constantly be updating, the "M"s and othe
characters (always integers if not M) in the target column (D) will b
changing. When an M shows up, the spreadsheet user will have t
manually enter data into the F column, which would overwrite a stati
entry if we were to simply write a formula.

I am not quite sure how to make the leap to scanning all the rows of m
document. I changed each of the ranges to (D410), (F4:F10
respectively but when I run the macro I get the error "type mismatch"


Here's my code:
--------------------------------------------------------------------------------
Sub y_1()
If Range("D410").Value <> "M" Then
Range("F4:F10").Formula = "=D4+1"
Else
Range("F4:F10").Value = ""
End If
End Sub

 

[Frank Kabel]

Hi
try the following
sub insert_formula
dim rng as range
dim cell as range
set rng=activesheet.range("D410)
for each cell in rng
if rng.value="M" then
rng.offset(0,2).value=""
else
rng.offset(0,2).formulaR1C1="=R[0]C[-2]+1"
end if
next
end sub

 

[gr8white]

Here are two solutions that work somewhat differently.
The first is more like your original attempt - it sets
the values at the time the macro is run:

Range("F4:F10").Formula = "=D4+1"
For Each Cell In Range("D410").Cells
If Cell.Value = "M" Then
Cells(Cell.Row, "F").Value = ""
End If
Next

The second puts the condition in the formula for each
cell - so it's more dynamic. (Actually - you probably
wouldn't need to use VB at all for this - just put the
formula below - strip out the extra quotes - in the first
row and copy it to the rest.)
Range("F4:F10").Formula = "=IF(D4=""M"","""",D4+1)"

HTH,
Ken