Hi
try the following
sub insert_formula
dim rng as range
dim cell as range
set rng=activesheet.range("D4:D10)
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

--
Regards
Frank Kabel
Frankfurt, Germany


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

The pivot table will constantly be updating, the "M"s and other
characters (always integers if not M) in the target column (D) will
be
changing. When an M shows up, the spreadsheet user will have to
manually enter data into the F column, which would overwrite a static
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
my document. I changed each of the ranges to (D4:D10), (F4:F10)
respectively but when I run the macro I get the error "type
mismatch".


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



Thank you in advance for any help you might be able to give!


---
Message posted from http://www.ExcelForum.com/