Maybe something like:

dim wks as worksheet
dim LastRow as long
dim myCell as range
dim myRng as range

set wks = activesheet
with wks
lastRow = .cells(.rows.count,"B").end(xlup).row
set myrng = .range("b2:B" & lastrow)
for each mycell in myrng.cells
select case lcase(mycell.value)
case is = lcase("x")
mycell.offset(0,4).formular1c1 = "=rc[-1]/3"
case is = lcase("Y")
mycell.offset(0,4).formular1c1 = "=(rc[-1]+rc[2])/3"
case else
'do nothing
end select
next mycell
end with

===========
I used .formular1c1. Then I could use this kind of formula:
=(rc[-1]+rc[2])/3

r means the same row as the cell with the formula.
c[-1] means the column to the left of the cell with the formula
c[+1] is the cell to the right of the cell with the formula

You may want to consider using a formula that includes the test:

=if(b2="y",someformula,if(b2="x",someotherformula, evenanotherformula))

Then the formulas will react to any changes in column B.






Fester wrote:

I want to run a loop that looks at a value in one cell, and then
enters one formula for one value, and another for a different value.

So if Cell B2="X" then
range("E2").value = "formula"
elseif Cell B2 = "Y" then
range("E2").value = "other formula

this would loop through all cells until it found an empty cell.

Any help is appreciated.

Brendon

--

Dave Peterson